Running Sum In Continuous Form In Ms Access 2003

[balancetotal]

Creating a running sum in continuous form in Access 2003
--------------------------------------------------------------------------------

Hello:

I have three forms on my user-interface.

Main form is called frmPatients.
Within this main form is an embedded form called fromVisitDates.
Linked to this form is the form called fsubfrmServices .

The last form (fsubfrmService s) is of CONTINUOUS TYPE.

I know I can use DSUM to calculate a running sum on SINGLE FORM but this same function does not work in CONTINUOUS TYPE.

This is what I am trying to accomplish.

I have a textbox that is called curTotalBal.

I need to create a running sum of this value so that the balances show up in the footer of this form.

For instance, JON DOE pays a visit and he is given 3 services worth $15. He only paid $5. His balance for today is $10.

Same JON DOE pays a visit tomorrow and is given 2 more services and is charged $10. He only paid $5. His balance is $5 for today.

His overall balance is $15 ($5 from today and $10 from yesterday).

I need to add up all the balances from each visit so that the grandtotal of balances is easily visible on the footer of this form. This is for the convenience of the front desk clerk to inform JON DOE that he owes XYZ amount everytime he pays a visit.

This is what I have done so far.

I created an unbound textbox in the footer of fsubfrmServices and its control source is as follows:

=DSUM("[curTotalBal]", "tblService s", "[pKeyServicesID]<="&forms!frmPa tients!frmVisit Dates!form!fsub frmServic es!form!pkeySer vicesID)

When I run my form, I get #NAME? error.

Can anyone dissect this and help me out here???

Thanks in advance
Mike

[MMcCarthy]

Hi Mike

Firstly there is a syntax error in your code. Change as follows and see if it solves your problem.

 
=DSUM("[curTotalBal]", "tblServices", "[pKeyServicesID]<=" &
Forms!frmPatients!frmVisitDates.form!fsubfrmServices.form!pkeyServicesID)
 

This will refer to the pkeyServicesID which is a control on the subform "fsubfrmService s" which is a subform on the "frmVisitDa tes" subform which is on the "frmPatient s" main form.

Mary

[balancetotal]

Hi Mike

Firstly there is a syntax error in your code. Change as follows and see if it solves your problem.

 
=DSUM("[curTotalBal]", "tblServices", "[pKeyServicesID]<=" &
Forms!frmPatients!frmVisitDates.form!fsubfrmServices.form!pkeyServicesID)
 

This will refer to the pkeyServicesID which is a control on the subform "fsubfrmService s" which is a subform on the "frmVisitDa tes" subform which is on the "frmPatient s" main form.

Mary

Thank you Mary for your suggestion. I am sorry to inform you that it did not work. Do you have any other ideas?

Mike

[MMcCarthy]

This will refer to the pkeyServicesID which is a control on the subform "fsubfrmService s" which is a subform on the "frmVisitDa tes" subform which is on the "frmPatient s" main form.

Is this structure correct?

What error are you getting or incorrect result?

[balancetotal]

Is this structure correct?

What error are you getting or incorrect result?

Yes the structure is correct. The error I am getting is #Name?

Will it help if I were to zip a copy of my db and attach it here???

Let me know.

Mike

[MMcCarthy]

Yes the structure is correct. The error I am getting is #Name?

Will it help if I were to zip a copy of my db and attach it here???

Let me know.

Mike

Hi Mike

It will only work if the zipped file is under 40 kb.

Mary

[balancetotal]

Hi Mike

It will only work if the zipped file is under 40 kb.

Mary

Let me cut it down and then I will COMPACT&REPAIR the db before I zip it.
If it is less than 40Kb then only I will post it.

thanks for your patience.
Mike