Complicated Calculated Field in Form is driving me crazy! (1 Viewer)

[telepathetic]

Hello!! I have one main form called Receipts, and I have two subforms called Assets and Expenses. There is a many-to-one relationship for each of the subforms to the main form keyed off the Receipt ID. I want to include a Total field in the main form that totals the current Assets and Expenses associated with the current Receipt ID. Ideally this would update itself with every additional Asset or Expense entered. I have tried so many things and am having so many problems... I'm probably missing some easy solution, but in case you're curious-- here's what i've tried so far: I've created a new textbox in the main form called "Total", and I have assigned it to = DSum ("[Amount]", "Assets", "[Receipt ID] = Forms![Receipts]![Receipt ID]") + DSum ("[Amount]", "Expenses", "[Receipt ID] = Forms![Receipts]![Receipt ID]")
This seems to work very occasionally. I've tried to update the field more often by adding Requery's in the subforms, but that gives many errors-- i'm assuming the subforms can't requery any main form control.
Thank you for any help!

 

[Rich@ITTC]

Hi Telepathetic

I think it would be better not to use DSum, but Sum instead.

In the Total field on the main form try:

=(Sum(Me!Receipts!Amount))+(Sum(Me!Expenses!Amount))

HTH

Rich Gorvin

 

[Rich]

Add a hidden text box to each subform footer
each one =Sum([Amount])
On the main form =[Forms]![MainFormName]![SubformName1 subform].[Form]![TextboxName]+[Forms]![MainFormName]![SubformName2 subform].[Form]![TextboxName]
As you tab to new record the sum will be updated if you wish the sum updated as you enter data in the after update event of the control amount put Me.Refresh
HTH

 

[telepathetic]

thank you so much for the help-- works very nicely-- one last question-- is there an easy way to update the total as you're typing in the amount, not just after you tab out of the field, but as you're actually entering the number realtime?

 

[Pat Hartman]

No. How would you know which number was the last? There needs to be some indication that the data entry is complete.