By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,480 Members | 763 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,480 IT Pros & Developers. It's quick & easy.

Running Sum In Continuous Form In Ms Access 2003

P: 6
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 (fsubfrmServices) 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]", "tblServices", "[pKeyServicesID]<="&forms!frmPatients!frmVisitDates!form!fsubfrmSe rvic es!form!pkeyServicesID)

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

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

Thanks in advance
Mike
Mar 16 '07 #1
Share this Question
Share on Google+
6 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Mike

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. =DSUM("[curTotalBal]", "tblServices", "[pKeyServicesID]<=" &
  3. Forms!frmPatients!frmVisitDates.form!fsubfrmServices.form!pkeyServicesID)
  4.  
This will refer to the pkeyServicesID which is a control on the subform "fsubfrmServices" which is a subform on the "frmVisitDates" subform which is on the "frmPatients" main form.

Mary
Mar 16 '07 #2

P: 6
Hi Mike

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. =DSUM("[curTotalBal]", "tblServices", "[pKeyServicesID]<=" &
  3. Forms!frmPatients!frmVisitDates.form!fsubfrmServices.form!pkeyServicesID)
  4.  
This will refer to the pkeyServicesID which is a control on the subform "fsubfrmServices" which is a subform on the "frmVisitDates" subform which is on the "frmPatients" 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
Mar 19 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
This will refer to the pkeyServicesID which is a control on the subform "fsubfrmServices" which is a subform on the "frmVisitDates" subform which is on the "frmPatients" main form.
Is this structure correct?

What error are you getting or incorrect result?
Mar 20 '07 #4

P: 6
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
Mar 20 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Mar 20 '07 #6

P: 6
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
Mar 21 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.