Form with SubForm Totals

  • Thread starter Thread starter iamnu
  • Start date Start date
I

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?
 
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
 
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])))
Click to expand...
AcctID is on the MainForm.
Click to expand...
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])))
Click to expand...
In other words, I want to show the total BillPayAmt for each AcctID.
Click to expand...
Can someone show me what I'm doing wrong?
Click to expand...
Click to expand...

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.
 
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])))
Click to expand...
AcctID is on the MainForm.
Click to expand...
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])))
Click to expand...
In other words, I want to show the total BillPayAmt for each AcctID.
Click to expand...
Can someone show me what I'm doing wrong?
Click to expand...
Click to expand...

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)
 
Back
Top