Running subtotal

[benkingery]

I have a form to view inventory transactions that happen in sequence. It is sorted by the primary key which is just an autonumber field, so all inventory transactions show up in the order in which they entered the inventory transaction table.

What I would like to do is add an unbound calculated column that gives me the balance of the inventory much like a bank checking account. (i.e. I have a previous balance, a transaction, and a new balance)

The controls that will play a role are Transaction type (indicates whether 'Debit', or 'Credit') and Quantity of the transaction.

The form itself is viewed as "Continuous forms" and I've taken out the dividing lines, so it looks like a bank register and that is how it is meant to operate.

I realize this could be really confusing. Does anyone have any good ideas?

 

[benkingery]

I'm not actually sure this has helped me out exactly. On the query in the example file I was able to download, he refers to a table that doesn't exist in the example database. Given that, its really hard to replicate what he has.

I've read up about using DSums on Queries or forms. I just can't figure out how to get this to work.

In the end I want the running balance to show on my form (which is a subform). In my transaction table (AZ_Transactions) I have the following fields:

TransactionID (Autonumber)
Date (Datetime)
Type (Credit or Debit)
Reference (TEXT: Another unique number but not sequential)
QTY (Number)
Item (Text)

I realize there are probably two ways of going about this. Either 1) I can create a query that calculates the running total of transactions in the table, or 2) Create an unbound field on a form that calculates with a DSum.

My inclination has been to go with the DSum on the form which I have been getting help from: http://support.microsoft.com/kb/210495/EN-US/

Using the steps indicated in this article I am just getting errors. I think I have something wrong with the syntax especially as it relates to the forms/subform.

My forms name is: TransactionView_AZ
Subform name is: TransactionViewDetail(AZ) [This is where I'd like the actual unbound control to exist]

The main form TransactionView_AZ shows an item number and the Subform shows the transaction detail for that item (so they are linked using "item")

Here is what I have so far. I made an unbound control on TransactionViewDetail(AZ) called Balance. The control source is:

=DSum("QTY","AZ_Transactions","TransactionID <= Forms!TransactionViewDetail(AZ)!TransactionID")

The result when viewing the form is "#Error"

Any ideas out there?

 

[Rich]

Function RunSum(F As Form, KeyName As String, KeyValue, _
FieldToSum As String)
'***********************************************************
' FUNCTION: RunSum()
' PURPOSE: Compute a running sum on a form.
' PARAMETERS:
' F - The form containing the previous value to
' retrieve.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldToSum - The name of the field in the previous
' record containing the value to retrieve.
' RETURNS: A running sum of the field FieldToSum.
' EXAMPLE: =RunSum(Form,"ID",[ID],"Amount")
'***********************************************************
Dim rs As Recordset
Dim result


On Error GoTo Err_RunSum
' Get the form Recordset.
Set rs = F.RecordsetClone
' Find the current record.
Select Case rs.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, _
DB_SINGLE, DB_DOUBLE, DB_BYTE
rs.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
rs.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
rs.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
GoTo Bye_RunSum
End Select
' Compute the running sum.
Do Until rs.BOF
result = Round2CB(result + rs(FieldToSum))
' Move to the previous record.
rs.MovePrevious
Loop
Bye_RunSum:
RunSum = result

Exit Function
Err_RunSum:
Resume Bye_RunSum
End Function

 

[benkingery]

Wow! Thanks Rich. This certainly looks robust enough. I'm going to have to admit that I'm not familiar with VB code enough to know where I even need to insert my unique parameter values here. Can you help me work through this?

What will I need to change on this and where exactly will I be putting in my unique values, etc. Also, is this a Function on the form, or within a New module?