Form with SubForm Totals

[iamnu]

In the Subform Query Builder, I have the following field:
AcctTotal: CCur(Nz(DSum("[BillPayAmt]","[tblPayDetail]","[AcctID] = "
& [AcctID])))

AcctID is on the MainForm.

I have tried the following expressions with no success:
AcctTotal: CCur(Nz(DSum("[BillPayAmt]","[tblPayDetail]","[AcctID] = "
& [Forms]![MedicalForm]![AcctID])))
AND
AcctTotal: CCur(Nz(DSum("[BillPayAmt]","[tblPayDetail]","[Forms]!
[MedicalForm]![AcctID] = " & [Forms]![MedicalForm]![AcctID])))

In other words, I want to show the total BillPayAmt for each AcctID.

Can someone show me what I'm doing wrong?

 

[Arvin Meyer [MVP]]

Much simpler than that. First add a footer to your subform. Now if your
subform is in continuous form view, you are ready to go.

Set the controlsource of a textbox in the footer to:

= Sum([BillPayAmt])

For a datasheet, you'll need to add a textbox to the main form, and set it's
controlsource to read the subform textbox:

= Forms!YourFormName!YourSubformControlName.Form!TextboxName

 

[iamnu]

Much simpler than that. First add a footer to your subform. Now if your
subform is in continuous form view, you are ready to go.

Set the controlsource of a textbox in the footer to:

= Sum([BillPayAmt])

For a datasheet, you'll need to add a textbox to the main form, and set it's
controlsource to read the subform textbox:

= Forms!YourFormName!YourSubformControlName.Form!TextboxName
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

In the Subform Query Builder, I have the following field:
AcctTotal: CCur(Nz(DSum("[BillPayAmt]","[tblPayDetail]","[AcctID] = "
& [AcctID])))

AcctID is on the MainForm.

I have tried the following expressions with no success:
AcctTotal: CCur(Nz(DSum("[BillPayAmt]","[tblPayDetail]","[AcctID] = "
& [Forms]![MedicalForm]![AcctID])))
AND
AcctTotal: CCur(Nz(DSum("[BillPayAmt]","[tblPayDetail]","[Forms]!
[MedicalForm]![AcctID] = " & [Forms]![MedicalForm]![AcctID])))

In other words, I want to show the total BillPayAmt for each AcctID.

Can someone show me what I'm doing wrong?

Thanks for the reply, Arvin.
I already have those totals on my Subform. But what I want to do is
to get sums from BillPayAmt that belong to the AcctID, then another
for those that belong to the PayerID and another for those that belong
to the DrID.

On the MainForm, I want to see "Grand Totals" for each Acct, each
Payer, and each Dr.

 

[Arvin Meyer [MVP]]

Much simpler than that. First add a footer to your subform. Now if your
subform is in continuous form view, you are ready to go.

Set the controlsource of a textbox in the footer to:

= Sum([BillPayAmt])

For a datasheet, you'll need to add a textbox to the main form, and set
it's
controlsource to read the subform textbox:

= Forms!YourFormName!YourSubformControlName.Form!TextboxName
--
Arvin Meyer, MCP,
MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

In the Subform Query Builder, I have the following field:
AcctTotal: CCur(Nz(DSum("[BillPayAmt]","[tblPayDetail]","[AcctID] = "
& [AcctID])))

AcctID is on the MainForm.

I have tried the following expressions with no success:
AcctTotal: CCur(Nz(DSum("[BillPayAmt]","[tblPayDetail]","[AcctID] = "
& [Forms]![MedicalForm]![AcctID])))
AND
AcctTotal: CCur(Nz(DSum("[BillPayAmt]","[tblPayDetail]","[Forms]!
[MedicalForm]![AcctID] = " & [Forms]![MedicalForm]![AcctID])))

In other words, I want to show the total BillPayAmt for each AcctID.

Can someone show me what I'm doing wrong?

Thanks for the reply, Arvin.
I already have those totals on my Subform. But what I want to do is
to get sums from BillPayAmt that belong to the AcctID, then another
for those that belong to the PayerID and another for those that belong
to the DrID.

On the MainForm, I want to see "Grand Totals" for each Acct, each
Payer, and each Dr.
--------------------------------------------------
This should work if your table is set up correctly:

AcctTotal: DSum("[BillPayAmt]","[tblPayDetail]","[AcctID] = " &
[Forms]![MedicalForm]![AcctID])

You shouldn't need CCur if the field is Currency ands your textbox is
formatted as Currency. You shouldn't need NZ, because there should never be
a null in a currency field (it should be $0.00)