Per record, running sum, on a tabular form 1

[gearhead03]

I have been working with this for several days. I am attempting to make a check register in an existing database. I would like a tabular form that shows the amount of the check or deposit and right next to it the running total. I can't figure how to make it happen. I am not a code wizard. I have tried this:

http://tek-tips.com/faqs.cfm?fid=5248

and

http://support.microsoft.com/default.aspx?scid=kb;en-us;210338

These seem to (as best I can tell) deal with a running sum for a form that shows 1 record at a time and it sums from that record back to the beginning. Is what I am asking possible?

Example of what I would like to see:
Date Payee Amount Total
01/01/07 Beginning Balance 500.00 500.00
01/02/07 Joe's Snack Shak -100.00 400.00
01/02/07 Office Max -250.00 150.00
01/04/07 DEPOSIT 100.00 250.00

any help is appreciated in advance
Thanks

Mark A. Kale

 

[lameid]

I see how the code works... The catch is that all functions evaluate the same on a continuous form when using anything that evaluates to a value for the current record.

So in looking at the code with the below declaration, we see that we have to pass idName and idValue to it based on the current record. This means that at least idValue has to be passed based on the value in the record not the ME!value as posted in the other procedure.

frmRunSum(curForm As Form, idName As String, idValue, sumField As String)

The below is an attempt to fix the FAQ (faq702-5248)for your purposes...

Public Function UniqueNamePerForm(UniqueField)

'*******************************************************************
'Replaced with UniqueField '* idName   - Unique field name.                                   *
'* sumField - The name of the field to runsum                      *
'*******************************************************************
   
   If Not IsNull(Me!idName) Then 'Skip New Record!
      UniqueNamePerForm = frmRunSum(Me, "idName", UniqueField, "sumField")
   End If

End Function

Note that all I changed was adding a parameter to the function and replacing a value.

The usage is changed in that you have to pass the unique value to the function in your control source...

 = UniqueNamePerForm(<Field Name Of Unique Value>)

Note that the carrots are used to designate you need to replace with the appropriate value.

I hope that works out.

 

[gearhead03]

Thanks LameId
I left this code at the house so I will try iy tonight when I get home and let you know!

Mark A. Kale

 

[TheAceMan1]

How are ya lameid . . .

There's nothing wrong with the code. Did you get my return email about the faq! I ran two simulations without a hitch! and all parts are needed.

See Ya! . . . . . .

Be sure to see thread181-473997

 

[TheAceMan1]

How are gearhead03 . . .

Perhaps I could've been clearer in the calling routine. So in the following you supply all names in [purple]purple[/purple]:

[blue]   If Not IsNull(Me![purple][b]PrimaryKeyName[/b][/purple]) Then 'Skip New Record!
      UniqueNamePerForm = frmRunSum(Me, "[purple][b]PrimaryKeyName[/b][/purple]", Me![purple][b]PrimaryKeyName[/b][/purple], "[purple][b]SumFieldName[/b][/purple]")
   End If[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[lameid]

Aceman1,
No I did not get your e-mail... We just switched Anti-Spam services. Apparently it is wrong to deny delivery of high certainty of Spam <sigh>.

Hopefully anything else from tek-tips would come through at least to my quarantine. What domain do those messages come from, so I can whitelist it?

You tried it on a continuous form and got different running sums down the screen for each value? I did not test it but I don't think it will work as intended.

 

[TheAceMan1]

[blue]What domain do those messages come from, so I can whitelist it?[/blue]

That would be [blue]nyc.rr.com[/blue].

[blue]You tried it on a continuous form and got different running sums down the screen for each value? [purple]I did not test it but I don't think it will work as intended.[/purple][/blue]

Well . . . thats easy to say. Not Only did I test it throughly in 97, 2K,2002,2003, and XP before I posted, but have run two simulations since your email! and no problemo! Gotta be something your not doing? There's no reason it doesn't work for ya! . . . perhaps you can explain!

See Ya! . . . . . .

Be sure to see thread181-473997

 

[lameid]

Gearhead03, can you post either a working solution or the reults you are getting?

AceMan,

I guess we'll have to wait on Gearhead03 to give us more information then.

Based on his orginal post...

These seem to (as best I can tell) deal with a running sum for a form that shows 1 record at a time and it sums from that record back to the beginning.

It seemed to me the issue was single versus continuous forms Since my experience tells me when you reference a control on a continuous form you get results based on it for all instances of the control, I thought the solution would be my modification. If that is not the case, I have entirely missed the boat on what the issue is (not too surprising since I made a huge inference).

 

[TheAceMan1]

lameid . . .

[blue]gearhead03[/blue] is close in their assumption, but not close enough!

Realize . . . an unbound control in the detail area with [blue]control source[/blue] equal to an equation, peforms the equation for each record! You can easily test this by setting a simple equation like:

[blue]=[PrimarykeyName][/blue]

Here you'll see the primary key listed again (one for one) for each record. This also shows each record appears to aquire the focus during run of the equation.

This is the primary reason the routine works. We simply sum from the current record all the way back to the first, per record.

The intermediary call to [blue]UniqueNamePerForm()[/blue] has two purposes:
[ol][li]To allow the form object [blue]Me[/blue] to be passed to the main routine. Passing Me in this way allows the code to work for subforms as well![/li]
[li]To prevent the error that occurs (#Name Error or #Error) when the equation executes against a new record.[/li][/ol]
Note that [blue]UniqueNamePerForm[/blue] can be a common name as long as the [blue]Private[/blue] keyword is used form forms:

[blue][purple][b]Private[/b][/purple] Function frmRunSum(curForm As Form, idName As String, _
                          idValue, sumField As String)[/blue]

The main routine performs as follows:
[ol][li]Clone the forms recordset.[/li]
[li]Get the data type of the field as this affects the syntax for finding the record where the equation is currently executing.[/li]
[li]Find the record.[/li]
[li]Sum all records in the clone from the found record all the way back to the 1st.[/li]
[li]Return each total sum back thru the function.[/li][/ol]
Simple really! . . .

See Ya! . . . . . .

Be sure to see thread181-473997

 

[gearhead03]

Sorry, I didn't get a chance to try any of this. I got home late last night. I think that I was getting the #name error. i will for sure try a couple of these ideas tonight. I may need some clarification, I may be naming a textbox or field incorrectly.

Mark A. Kale

 

[gearhead03]

Ace!!!

Your stuff works great!!! (of course you already knew this).

My own inexperience was holding me up. The post that you changed the purple fields to "PrimaryKeyName" helped me to realize what I should be putting in there. I interpreted the directions "idName - Unique field name." from the faq to mean a new field with a unique name.

Thanks again for your help.

Mark A. Kale

 

[TheAceMan1]

gearhead03 . . .

Because of this thread, its apparent I need to make things clearer in the fag . . . and will do so! . . . Thanks for the input . . .

See Ya! . . . . . .

Be sure to see thread181-473997

 

[lameid]

Aceman,

For what it is worth, the faq was clear to me but of course there is always room for improvement. I just thought I saw something that wasn't there because I thought I read an issue that apparently was not there...

For some reason I was thinking that any control would not have the record value (record scope) at run time. I thought it would come from the current record. I see now of course that this varies depending on the scope of the procedure and where it is run from. Note the difference of course between control and field.

It has been an education!