running sum in subform / faq702-5248 1

[swtrader]

My thanks to the author of faq702-5248...

I need it -- but I can't get it to run. Is most likely a result of my inexperience with VBA.

I need to have the running sum appear on a subform.

In the code cited, pkName and pkValue are used. If pkName (in my case, [TransID]) is on the subform, do I need to use the syntax to reference it as a subform field? If so, how?

I don't seem to understand what pkValue is. Should that be changed to the name of the field that holds the value that I want to create a running sum from?(pardon the grammar).

Also, I don't know for certain how to put code in a form module. Do I open the subform in design view and click Code to open the window where the Private Function SubSum() should be?

Thanks for all your help.

 

[TheAceMan1]

How are ya swtrader . . .

I'm at work now and can't spend the necessary time. However, I will pick this up when I get home this evening.

AceMan is USA - New York - Eastern Standard Time.

Until then take care! . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[swtrader]

Doing well 'cept for this glitch.

as reference...

frmTransMain; frmTransSub; table = tblLoanTrans ; tblLoanTrans pk = ReconRowID ; name of field to create running sum: PrinIncDec

thanks for your insight and your valuable help.

swt

 

[TheAceMan1]

Howdy swtrader . . .

Hope the wait wasn't too [blue]agonizing![/blue] So . . . lets rock shall we.

[blue]Also, I don't know for certain how to put code in a form module. Do I open the subform in design view and click Code to open the window where the Private Function SubSum() should be?[/blue]

Exactly!

[blue]I don't seem to understand what pkValue is.[/blue]

From the FAQ:

[green]'* pkName  - Existing [b]unique fieldname[/b] (usually primarykey).           *
'* pkValue - [b]Value[/b] of pkName, the value to search for in the recordset *[/green]

[green]unique fieldname[/green] is the name of any field that has no duplicates! Usually the primarykey field of the underlying table. For you in relation to the subform, this appears to be [blue]ReconRowID[/blue]. If its [blue]not unique[/blue] you'll have to select another field. [green]unique fieldname[/green] is used to lookup the starting point for the summation value of each record!

[green]pkValue[/green] is the value of [blue]ReconRowID[/blue] or the unique field for each record, which is simply Me![blue]ReconRowID[/blue] or me![blue]UniqueFieldName[/blue].

With the above, the [blue]SubSum[/blue] function in your subforms code module should look like:

[blue]Private Function SubSum()

[green]'*************************************************************
'* pkName   - Existing unique fieldname (usually primarykey) *
'* sumName  - Name of the field to runsum                    *
'*************************************************************[/green]
   
   If Not IsNull(Me![purple][b]ReconRowID[/b][/purple]) Then  [green]'Skip New Record![/green]
      SubSum = frmRunSum(Me, "[purple][b]ReconRowID[/b][/purple]", Me![purple][b]ReconRowID[/b][/purple], "[purple][b]PrinIncDec[/b][/purple]")
   End If

End Function[/blue]

Note: for clarity, [green]sumName[/green] is the name of the field you want to runsum. [blue]Not the unbound field[/blue] you added to show the sum!

[blue]Standing By! . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[swtrader]

Thanks for getting back to me, Aceman. Am stuck. Field I'm trying to populate with the running sum on each row in the subform is [SummedField]. Will you please glance at the following and see if you see apparent errors? Thanks, again. No rush, of course.


Public Function frmRunSum(frm As Form, ReconRowID As String, _
pkValue, sumName As String)
'***********************************************************************
'* frm - Always Me for easy setting of form object. *
'* This allows the function to work for subforms as well! *
'* pkName - Existing unique fieldname (usually primarykey). *
'* pkValue - Value of pkName, the value to search for in the recordset *
'* sumName - The name of the field to runsum *
'***********************************************************************
Dim rst As DAO.Recordset, subTotal
Dim PrinIncDec As Double
Set rst = frm.RecordsetClone

'Find the current record via proper syntax for Data Type.
Select Case rst.Fields(ReconRowID).Type
Case dbLong, dbInteger, dbCurrency, _
dbSingle, dbDouble, dbByte 'Numeric Data Type
rst.FindFirst "[" & ReconRowID & "] = " & ReconRowID
Case dbDate 'Date Data Type
rst.FindFirst "[" & ReconRowID & "] = #" & ReconRowID & "#"
Case dbText 'Text Data Type
rst.FindFirst "[" & ReconRowID & "] = '" & ReconRowID & "'"
Case Else
rst.MovePrevious 'Set BOF!
End Select

'Running Sum (subTotal) for each record group occurs here.
If Not rst.BOF Then
Do Until rst.BOF
subTotal = subTotal + Nz(rst(PrinIncDec), 0) 'ERROR MSG: Variable Not Defined
rst.MovePrevious
Loop
Else
subTotal = 0
End If

'frmSubSum = subTotal ERROR MSG - Variable Not Defined

Set rst = Nothing

End Function

===========

subform function.....

Private Function SubSum()

If Not IsNull(Me!ReconRowID) Then
SubSum = frmRunSum(Me, "ReconRowID", Me!ReconRowID, "PrinIncDec")
End If

End Function

=============

Control Source for SummedField on subform: =SubSum()

 

[TheAceMan1]

swtrader . . .

The function [blue]frmRunSum[/blue] that you copied from the faq into a module in the modules window, [purple]Does Not Get Edited![/purple]

Copy the functon again from the faq!

The legand in green simply shows what names mean when calling the function . . . as in the call from the subform.

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[swtrader]

Thought I had posted a "Big Thanks" earlier but don't see it. Worked perfectly, AceMan. Thank you.

swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[TheAceMan1]

swtrader . . .

Just a heads up. I've updated the FAQ to crank out more speed.

[blue]Cheers![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[swtrader]

TheAceMan1,

Inexplicably get a #Error on about 10 "top" rows (out of 14 total) after entering a number to be summed. The running sums for the bottom 4 are correct. Only way I've found to 'clear' it is to close the form and re-open -- which produces all correct running sum numbers row by row.

?

swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[TheAceMan1]

swtrader . . .

Has this been since the update? . . . I've no problems here . . . A2k?

Post back the function [blue]frmRunSum[/blue] & [blue]SumSum[/blue].

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[swtrader]

My error. Had subform as a continuous form. Once changed to datasheet, all is well. Thanks.