Sum Calculated Control On Main Form

[Mav3000]

Hi All, You've probably come across this problem many times. I've read numerous articles on this, but none address my particular problem.

I have a form [frmLedger] which has a subform [frmInvoice] which in turn has a subform [frmInvoice_Items].

I have a calculated control which is in the footer of [frmInvoice_Items] which I have successfully referenced in [frmInvoice]. i.e. [frmInvoice] shows the Sum of [frmInvoice_Items].[Invoice_Item_Value].

However, I need to show the Total Invoice Value on [frmLedger]. i.e. the Sum Total of the Sum Total.

I have tried to do this using DSum but I can't get the formula to work - it just comes up with 'Error'.

My Tables are as follows:

[tblLedger]
[Ledger_ID]

[tblInvoice]
[Invoice_ID]
[Ledger_ID]

[tblInvoice_Items]
[Invoice_Item_ID]
[Invoice_ID]
[Invoice_Item_Value]

My DSum formula in the [frmLedger] txtTotal_Invoice control looks like this:

=Dsum("[Invoice_Item_Value],"tblInvoice_Items","[tblInvoice_Items]![Invoice_ID]=[tblInvoice]![Invoice_ID] AND [tblInvoice]![Ledger_ID]=[tblLedger]![Ledger_ID]

Am I doing this correctly? or is there a way of doing this through a form? I have read on Office Online about referencing the calculated control in an SQL query, but this makes no sense to me.

Any help would be appreciated,

Richard

 

[SeeThru]

You are missing a couple of quotes....

=Dsum("[Invoice_Item_Value]","tblInvoice_Items","[tblInvoice_Items]![Invoice_ID]=[tblInvoice]![Invoice_ID] AND [tblInvoice]![Ledger_ID]"=[tblLedger]![Ledger_ID]

Assumes [Ledger_ID] is numeric

SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[SeeThru]

Sorry, missed a close bracket!


=Dsum("[Invoice_Item_Value]","tblInvoice_Items","[tblInvoice_Items]![Invoice_ID]=[tblInvoice]![Invoice_ID] AND [tblInvoice]![Ledger_ID]"=[tblLedger]![Ledger_ID])

SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[dhookom]

I doubt any of those solutions will work correctly. It isn't clear to me whether the text box in frmLedger should display results from one invoice or all invoices from the ledger.

Regardless, I would probably create a query from the tables that groups by invoiceid and totals the Invoice_Item_Value field. Then use this query as the domain in the DSum() function.

The equal sign must be inside the quotes, not outside. You can only use fields inside the quotes that are in the domain.

Duane
Hook'D on Access
MS Access MVP

 

[Mav3000]

Excellent - thank you very much for the reply. I now have the total working nicely. It doesn't automatically update however when the total on the sub-form changes.

Do I need to add some sort of 'after update' code into the sub-form to update the dsum field on the main form?

I'd like it to update while the record in the sub form is still open (although to keep the update until the record is saved is fine).

Thanks, Richard

 

[dhookom]

You can add code to the after update of the subform to Requery the text box on the main form.

Duane
Hook'D on Access
MS Access MVP