Sum Recordset of Subform

[Melinda]

Hello,
I have a subform ([frmSub]) that includes the total invoiced for each job
listed ([TotalInvoiced]). I would like my main form ([frmMaster]) to display
the total billed for all jobs in the subform. I've been told I can do this
using RecordsetClone, but I'm not sure how. Also, how can I get my
calculation to update if the data in the subform changes? I'm not much of a
programmer, so a brief explanation of each command would be great.
Thanks for your input!
Melinda

 

[Rob Oldfield]

It would be easier to just use the dsum function. Something like...

=dsum("[TotalInvoiced]","SubFormTable/Query","[SubFormForeignKeyName]="&[Con
trolOnMainFormContainingPK])

 

[Melinda]

Hmmm...I those criteria won't work and I'm not sure what to use. My main
form is unbound and my subform gets its data from a query which uses criteria
set in the main form. Hence, the RecordsetClone. Ideas?

 

[Rob Oldfield]

It sounds like, if that's the case, that you've already applied the criteria
to the query... what's the code that you're currently using the set that
query up?

Hmmm...I those criteria won't work and I'm not sure what to use. My main
form is unbound and my subform gets its data from a query which uses criteria
set in the main form. Hence, the RecordsetClone. Ideas?

It would be easier to just use the dsum function. Something like...

=dsum("[TotalInvoiced]","SubFormTable/Query","[SubFormForeignKeyName]="&[Con
trolOnMainFormContainingPK])

 

[Melinda]

I used the query builder and a whole bunch of
Like([Forms]![frmMaster]![FieldName]) type criteria. So, maybe I'm just
setting up the DSum wrong. Missing brackets or quotes.
=DSum([TotalInvoiced],qryMaster)?

 

[Rob Oldfield]

Try =DSum("[TotalInvoiced]","qryMaster")

I used the query builder and a whole bunch of
Like([Forms]![frmMaster]![FieldName]) type criteria. So, maybe I'm just
setting up the DSum wrong. Missing brackets or quotes.
=DSum([TotalInvoiced],qryMaster)?

It sounds like, if that's the case, that you've already applied the criteria
to the query... what's the code that you're currently using the set that
query up?

 

[Melinda]

Thanks Rob! That worked perfectly! And was easier than the RecordsetClone
thing!