Display a total in a report

[katsup]

I’ve built a multi-table query called qryRetreatFees which selects out all
the retreat fees recorded for one individual retreat participant from all
participants. Additionally it shows the pricing factors (family type,
duration of stay) that determine each fee. The query works accurately on my
test data, producing the following results:

Registrant FeeID RegistrantType Duration Fee

Tom Thumb 1 TwoAdults:2teens OneFullDay $315.00
Tom Thumb 5 Couple HalfDay 90.00
Tom Thumb 7 TwoAdults:3kids OneFullDay 210.00


Next I built a report based on that query. (Eventually the report will be a
sub-report, among several sub-reports that melded together will produce a
printed invoice, showing retreat fees, snack bar tabs, discounts, etc., but
right now I’m just trying to get it to work as a report on its own.)

I want to total the individual’s retreat fees, and show that total in the
invoice-report’s page footer, so I created another query (thinking this might
be the right way to do it) called qryRtreatFeesTotal, which used the
RetreatFee field from the first query with the function SUM on the Total line
of the QBE grid. Running the query produced the correct total for this
subset, of $615. The SQL it produced looks like this:

SELECT Sum(qryRetreatFees.RetreatFee) AS SumofRetreatFee FROM qryRetreatFees;

I then popped a text box onto the invoice-report’s page footer, and set its
control source to this:

=qryRetreatFeesTotal.SumofRetreatFee

It resulted in an #Error in the text box. And when I fire up the report
from scratch a pop-up ENTER PARAMETER VALUE window appears, asking for an
entry for qryRetreatFeesTotal.

What am I doing wrong?

 

[Ken Sheridan]

You can't reference a query directly in that way in a report. To look up
data from an external table or query you can use the DLookup function, but in
your case you don't need the second query at all; you can sum the amount s
directly in the report in an unbound control.

You cannot sum values directly in a page footer, however, only in a Group or
report footer. In your case you should group the report on the Registrant
field in the Sorting and Grouping Dialogue in report design view, and make
sure you opt to five it a group footer. You'll then see the footer on the
report in design view. Add a text box control to this footer and set its
ControlSource property to:

=Sum([Fee])

If you really do want this to appear in the page footer you should set the
Visible property of the text box control in the group footer to false (No)
and add another unbound text box to the page footer with a Controlsource
property which references the hidden text box in the group footer, e.g.

=[txtGroupTotalFee]

I'd advise against this, however, as you want to use the report as a
subreport and page footers from subreports do not show in a parent report.
You can show the value from the hidden group footer in the subreport in the
parent report's page footer, however, by referencing it via the Report
property of the subreport control (i.e., the control hosing the subreport in
the parent report):

=[SubreportControl].Report![txtGroupTotalFee]

Ken Sheridan
Stafford, England

 

[katsup]

Ken, it worked like fresh butter on corn ears hot from the pot. (Can you tell
what the retreat’s first evening’s dinner will be?) Such a simple fix!

It did seem like overkill to me to use a query to total the subset of fees,
but I couldn’t envision how I would reference the subset otherwise. The only
place fee amounts actually exist in this db is a table outlining the general
fee schedule for all family and duration types. OTOH, the table which
accumulates specific invoice charges for all registrants has only the
registrant IDs, and the pricing factor IDs, i.e., only the appropriate
pointers to the general schedule.

Now I’m guessing the reason =Sum([Fee]) totals my subset correctly is that
the report was already built on the subset, rather, on the query that
extracted the subset. Yes? If so, that’s another major step forward for me
in understanding how Access works.

Thanks so much for your swift response. I’m anticipating getting much
further ahead through the rest of the weekend now.

You can't reference a query directly in that way in a report. To look up
data from an external table or query you can use the DLookup function, but in
your case you don't need the second query at all; you can sum the amount s
directly in the report in an unbound control.

You cannot sum values directly in a page footer, however, only in a Group or
report footer. In your case you should group the report on the Registrant
field in the Sorting and Grouping Dialogue in report design view, and make
sure you opt to five it a group footer. You'll then see the footer on the
report in design view. Add a text box control to this footer and set its
ControlSource property to:

=Sum([Fee])

If you really do want this to appear in the page footer you should set the
Visible property of the text box control in the group footer to false (No)
and add another unbound text box to the page footer with a Controlsource
property which references the hidden text box in the group footer, e.g.

=[txtGroupTotalFee]

I'd advise against this, however, as you want to use the report as a
subreport and page footers from subreports do not show in a parent report.
You can show the value from the hidden group footer in the subreport in the
parent report's page footer, however, by referencing it via the Report
property of the subreport control (i.e., the control hosing the subreport in
the parent report):

=[SubreportControl].Report![txtGroupTotalFee]

Ken Sheridan
Stafford, England

I’ve built a multi-table query called qryRetreatFees which selects out all
the retreat fees recorded for one individual retreat participant from all
participants. Additionally it shows the pricing factors (family type,
duration of stay) that determine each fee. The query works accurately on my
test data, producing the following results:

Registrant FeeID RegistrantType Duration Fee

Tom Thumb 1 TwoAdults:2teens OneFullDay $315.00
Tom Thumb 5 Couple HalfDay 90.00
Tom Thumb 7 TwoAdults:3kids OneFullDay 210.00


Next I built a report based on that query. (Eventually the report will be a
sub-report, among several sub-reports that melded together will produce a
printed invoice, showing retreat fees, snack bar tabs, discounts, etc., but
right now I’m just trying to get it to work as a report on its own.)

I want to total the individual’s retreat fees, and show that total in the
invoice-report’s page footer, so I created another query (thinking this might
be the right way to do it) called qryRtreatFeesTotal, which used the
RetreatFee field from the first query with the function SUM on the Total line
of the QBE grid. Running the query produced the correct total for this
subset, of $615. The SQL it produced looks like this:

SELECT Sum(qryRetreatFees.RetreatFee) AS SumofRetreatFee FROM qryRetreatFees;

I then popped a text box onto the invoice-report’s page footer, and set its
control source to this:

=qryRetreatFeesTotal.SumofRetreatFee

It resulted in an #Error in the text box. And when I fire up the report
from scratch a pop-up ENTER PARAMETER VALUE window appears, asking for an
entry for qryRetreatFeesTotal.

What am I doing wrong?

 

[Ken Sheridan]

I'm a little concerned that you are storing the fee amounts only in the
generic fee schedule table and looking them up. If the fee amounts in this
table change over time, as one would expect them too, the amounts in all
previous invoices would show the new fees, whereas you'd want them to show
the fees at the time of the invoice, so the fee amounts should be stored with
each invoice, getting the current value when the invoice is created.

You'll find a similar scenario in the sample Northwind database on the
Orders Subform, where the unit price of a product is looked up in the
AfterUpdate event procedure of the ProductID control and assigned to the
UnitPrice field in the OrderDetails table.

Ken Sheridan
Stafford, England

Ken, it worked like fresh butter on corn ears hot from the pot. (Can you tell
what the retreat’s first evening’s dinner will be?) Such a simple fix!

It did seem like overkill to me to use a query to total the subset of fees,
but I couldn’t envision how I would reference the subset otherwise. The only
place fee amounts actually exist in this db is a table outlining the general
fee schedule for all family and duration types. OTOH, the table which
accumulates specific invoice charges for all registrants has only the
registrant IDs, and the pricing factor IDs, i.e., only the appropriate
pointers to the general schedule.

Now I’m guessing the reason =Sum([Fee]) totals my subset correctly is that
the report was already built on the subset, rather, on the query that
extracted the subset. Yes? If so, that’s another major step forward for me
in understanding how Access works.

Thanks so much for your swift response. I’m anticipating getting much
further ahead through the rest of the weekend now.

You can't reference a query directly in that way in a report. To look up
data from an external table or query you can use the DLookup function, but in
your case you don't need the second query at all; you can sum the amount s
directly in the report in an unbound control.

You cannot sum values directly in a page footer, however, only in a Group or
report footer. In your case you should group the report on the Registrant
field in the Sorting and Grouping Dialogue in report design view, and make
sure you opt to five it a group footer. You'll then see the footer on the
report in design view. Add a text box control to this footer and set its
ControlSource property to:

=Sum([Fee])

If you really do want this to appear in the page footer you should set the
Visible property of the text box control in the group footer to false (No)
and add another unbound text box to the page footer with a Controlsource
property which references the hidden text box in the group footer, e.g.

=[txtGroupTotalFee]

I'd advise against this, however, as you want to use the report as a
subreport and page footers from subreports do not show in a parent report.
You can show the value from the hidden group footer in the subreport in the
parent report's page footer, however, by referencing it via the Report
property of the subreport control (i.e., the control hosing the subreport in
the parent report):

=[SubreportControl].Report![txtGroupTotalFee]

Ken Sheridan
Stafford, England

I’ve built a multi-table query called qryRetreatFees which selects out all
the retreat fees recorded for one individual retreat participant from all
participants. Additionally it shows the pricing factors (family type,
duration of stay) that determine each fee. The query works accurately on my
test data, producing the following results:

Registrant FeeID RegistrantType Duration Fee

Tom Thumb 1 TwoAdults:2teens OneFullDay $315.00
Tom Thumb 5 Couple HalfDay 90.00
Tom Thumb 7 TwoAdults:3kids OneFullDay 210.00


Next I built a report based on that query. (Eventually the report will be a
sub-report, among several sub-reports that melded together will produce a
printed invoice, showing retreat fees, snack bar tabs, discounts, etc., but
right now I’m just trying to get it to work as a report on its own.)

I want to total the individual’s retreat fees, and show that total in the
invoice-report’s page footer, so I created another query (thinking this might
be the right way to do it) called qryRtreatFeesTotal, which used the
RetreatFee field from the first query with the function SUM on the Total line
of the QBE grid. Running the query produced the correct total for this
subset, of $615. The SQL it produced looks like this:

SELECT Sum(qryRetreatFees.RetreatFee) AS SumofRetreatFee FROM qryRetreatFees;

I then popped a text box onto the invoice-report’s page footer, and set its
control source to this:

=qryRetreatFeesTotal.SumofRetreatFee

It resulted in an #Error in the text box. And when I fire up the report
from scratch a pop-up ENTER PARAMETER VALUE window appears, asking for an
entry for qryRetreatFeesTotal.

What am I doing wrong?

 

[katsup]

Ken, thanks for your observation, and the tip about Northwind. In fact, I
had the same reaction to this design: storage space not being an issue in
this small app I preferred to store the fee amounts with each invoice also.

I got persuaded by a MicroSoft MVP however, that that constituted poor
design; and since maintaining historical data for this non-profit org wasn't
important to them, I went ahead with the "look up everything" model. It's
certainly forcing me to think in a new way and learn new skills, so that's
become the main personal value in this project.

May I bend your ear with another question?

I've now built just about all the tables, forms, and queries that will
produce individual invoices for each retreatant, and experimented
successfully with forming the main invoice report out of the 8 or so
individual subreports.

The trouble is I cut one important corner to get to this stage: I built each
of the 8 queries that underlie the 8 subreports by manually entering in the
retreatant's keyfield identifier number in the Criteria line of the QBE grid.

Of course, I don't want to give the user access to the QBE grid. I want
them to click a "Preview/Print Invoice" command button I guess, and then
choose the retreatant's name from a dropdown combo box. The keyfield
identifier number associated with each retreatant's name would then be fed to
the main invoice report and the 8 queries and subreports.

I haven't yet discovered how to feed that keyfield identifier number to the
main invoice report, and get it to fan out to the 8 queries and their
subreports. Can you point me in the general direction?

The subreports are all independant of each other, and of the main report,
except for the retreatant keyfield identifier, if that simplifies things.

I'm a little concerned that you are storing the fee amounts only in the
generic fee schedule table and looking them up. If the fee amounts in this
table change over time, as one would expect them too, the amounts in all
previous invoices would show the new fees, whereas you'd want them to show
the fees at the time of the invoice, so the fee amounts should be stored with
each invoice, getting the current value when the invoice is created.

You'll find a similar scenario in the sample Northwind database on the
Orders Subform, where the unit price of a product is looked up in the
AfterUpdate event procedure of the ProductID control and assigned to the
UnitPrice field in the OrderDetails table.

Ken Sheridan
Stafford, England

Ken, it worked like fresh butter on corn ears hot from the pot. (Can you tell
what the retreat’s first evening’s dinner will be?) Such a simple fix!

It did seem like overkill to me to use a query to total the subset of fees,
but I couldn’t envision how I would reference the subset otherwise. The only
place fee amounts actually exist in this db is a table outlining the general
fee schedule for all family and duration types. OTOH, the table which
accumulates specific invoice charges for all registrants has only the
registrant IDs, and the pricing factor IDs, i.e., only the appropriate
pointers to the general schedule.

Now I’m guessing the reason =Sum([Fee]) totals my subset correctly is that
the report was already built on the subset, rather, on the query that
extracted the subset. Yes? If so, that’s another major step forward for me
in understanding how Access works.

Thanks so much for your swift response. I’m anticipating getting much
further ahead through the rest of the weekend now.

You can't reference a query directly in that way in a report. To look up
data from an external table or query you can use the DLookup function, but in
your case you don't need the second query at all; you can sum the amount s
directly in the report in an unbound control.

You cannot sum values directly in a page footer, however, only in a Group or
report footer. In your case you should group the report on the Registrant
field in the Sorting and Grouping Dialogue in report design view, and make
sure you opt to five it a group footer. You'll then see the footer on the
report in design view. Add a text box control to this footer and set its
ControlSource property to:

=Sum([Fee])

If you really do want this to appear in the page footer you should set the
Visible property of the text box control in the group footer to false (No)
and add another unbound text box to the page footer with a Controlsource
property which references the hidden text box in the group footer, e.g.

=[txtGroupTotalFee]

I'd advise against this, however, as you want to use the report as a
subreport and page footers from subreports do not show in a parent report.
You can show the value from the hidden group footer in the subreport in the
parent report's page footer, however, by referencing it via the Report
property of the subreport control (i.e., the control hosing the subreport in
the parent report):

=[SubreportControl].Report![txtGroupTotalFee]

Ken Sheridan
Stafford, England

:

I’ve built a multi-table query called qryRetreatFees which selects out all
the retreat fees recorded for one individual retreat participant from all
participants. Additionally it shows the pricing factors (family type,
duration of stay) that determine each fee. The query works accurately on my
test data, producing the following results:

Registrant FeeID RegistrantType Duration Fee

Tom Thumb 1 TwoAdults:2teens OneFullDay $315.00
Tom Thumb 5 Couple HalfDay 90.00
Tom Thumb 7 TwoAdults:3kids OneFullDay 210.00


Next I built a report based on that query. (Eventually the report will be a
sub-report, among several sub-reports that melded together will produce a
printed invoice, showing retreat fees, snack bar tabs, discounts, etc., but
right now I’m just trying to get it to work as a report on its own.)

I want to total the individual’s retreat fees, and show that total in the
invoice-report’s page footer, so I created another query (thinking this might
be the right way to do it) called qryRtreatFeesTotal, which used the
RetreatFee field from the first query with the function SUM on the Total line
of the QBE grid. Running the query produced the correct total for this
subset, of $615. The SQL it produced looks like this:

SELECT Sum(qryRetreatFees.RetreatFee) AS SumofRetreatFee FROM qryRetreatFees;

I then popped a text box onto the invoice-report’s page footer, and set its
control source to this:

=qryRetreatFeesTotal.SumofRetreatFee

It resulted in an #Error in the text box. And when I fire up the report
from scratch a pop-up ENTER PARAMETER VALUE window appears, asking for an
entry for qryRetreatFeesTotal.

What am I doing wrong?

 

[Ken Sheridan]

I'd certainly go for storing the 'historical' fee values with the invoices;
it’s the standard approach for good reasons. I suspect the advice you were
given must have been on the basis of a misunderstanding of your requirements.

Anyway, consider my ear well and truly bent:

What you need here are parameter queries. You may be familiar with these
where a prompt is generated by the system along the lines of [Enter
Retreatant's name:], but a parameter does not have to be a system generated
one, it can be a reference to a control on a form. In your case the control
would be a combo box which simply listed all Retreatants by name
alphabetically, but whose value would be a hidden numeric ID value. For the
sake of this example lets assume you have a table Retreatants with columns
RetreatantID, FirstName and LastName. You'd create an unbound dialogue form,
frmInvoiceReportDlg say, and add a combo box to it. Name it cboRetreatant or
something appropriate. The RowSource property of the combo box would be:

SELECT RetreatantID, FirstName & " " & Lastname AS FullName
FROM Retreatants
ORDER BY LastName, FirstName;

To get the combo box to show the names you set its properties like so:

BoundColum 1
ColumnCount 2
ColumnWidths 0cm;8cm

For the last one you'll use the rough equivalent in inches. The second
dimension isn't crucial so long as its wide enough so use 3 inches say; the
first one must be zero, though, as this hides the RetreatantID column.
Because the BoundColumn property is 1 this first hidden column supplies the
value of the control.

Add a button to the form to open the report in preview. The button wizard
can be used, or you can enter the code yourself in the button's Click event
procedure. If you do the latter you can add some validation code to ensure a
user picks a retreatant first:

If Not IsNull(Me.cboRetreatant) Then
DoCmd.OpenReport "YourReportNameGoesHere", View:=acViewPreview
Else
MsgBox "Please select a retreatant first.", vbExclamation, "Invalid
Operation"
End If

Al that's necessary now is to set up the queries to reference the
cboRetreatant control on the frmInvoiceReportDlg form. To do this in query
design view enter the following in the criteria row of the RetreatantID
column for each query:

Forms!frmInvoiceReportDlg!cboRetreatant

Access will add some brackets for you, which is quite normal.

However there is another way you could do this in subreports if the parent
report includes the RetreatantID column in its underlying RecordSource. If
it doesn't but is an unbound 'container' report for unlinked subreports base
the parent report on the following query as its RecordSource:

SELECT RetreatantID
FROM Retreatants
WHERE RetreatantID = Forms!frmInvoiceReportDlg!cboRetreatant;

This is the SQL equivalent of what you did in query design view. The
subreports' queries now don't need any parameters at all because you can link
each subreport to the parent report. This is done by setting each subreport
control's LinkMasterFields and LinkChildFields properties to RetreatantID.
The parent report will be restricted to the retreatant selected in the combo
box and each subreport will similarly be restricted by its link with the
parent report. By 'subreport control' incidentally I mean the controls in
the parent form which house the subreports.

Ken Sheridan
Stafford, England

 

[katsup]

Ken, you seem to have a knack for identifying exactly what I don’t know, and
filling in the knowledge gap succinctly.

I was suspecting that parameter queries were the ticket, but indeed, didn’t
understand that I could reference a control in the criteria row of a query.
I have now done that, and the dialogue form works just fine to establish the
parameter for, and preview, the container report, showing and totaling all
test data accurately. (By way of explaining the delay, I’ve also survived a
major heat and storm wave, several ISP disruptions, and being pulled away to
another project.)

The second approach, involving LinkMasterFields and LinkChildFields, struck
me as more elegant, but I couldn’t get it to work, so went with referencing
the cboRetreatant dialogue form control in each query.

Does your ear still have some flex in it, or have I worn it out? I’m having
trouble getting grand totals in the container report’s footer.

The container report, MainInvoiceReport, contains 6 subreports of different
kinds of charges (egg., retreat fees, accommodation level, snack bar tab,
etc.). Each subreport details every charge incurred in that category by the
Retreatant, and additionally totals the category in a subreport group footer,
in a textbox with its control source set to an expression like

= Sum([RetreatFee])

The subreports are grouped by Registrant (aka Retreatant), as you suggested.


Two more subreports contain credits to be applied against the charges.
These are: payments; and credits such as early registration discounts,
bursaries, refunds, and the like. These 2 subreports also detail each
credit, and total the credits in their group footers.

In the container MainInvoiceReport I’m trying to produce, in 3 text boxes
and using the expression builder, a grand total of the 6 charge totals,
another grand total of the 2 credit totals, and a balance owing.

Here’s the expression to grandtotal the 6 charges subreports, in case that
helps:

=( Reports![subrepICRetreatFee2]![SumRetreatFee] +
Reports![subrepCabinCharge]![SumCabinCharge] +
Reports![subrepStorehouseTab]![SumItemTotal] +
Reports![subrepGroceryPurchase]![SumGroceryAmount] +
Reports![subrepPersonalServicesPurchase]![SumPersonalServiceAmount] +
Reports![subrepMiscellaneous]![SumMiscChargeAmount] )


The result is

#Name?

My result is

}-(

I'd certainly go for storing the 'historical' fee values with the invoices;
it’s the standard approach for good reasons. I suspect the advice you were
given must have been on the basis of a misunderstanding of your requirements.

Anyway, consider my ear well and truly bent:

What you need here are parameter queries. You may be familiar with these
where a prompt is generated by the system along the lines of [Enter
Retreatant's name:], but a parameter does not have to be a system generated
one, it can be a reference to a control on a form. In your case the control
would be a combo box which simply listed all Retreatants by name
alphabetically, but whose value would be a hidden numeric ID value. For the
sake of this example lets assume you have a table Retreatants with columns
RetreatantID, FirstName and LastName. You'd create an unbound dialogue form,
frmInvoiceReportDlg say, and add a combo box to it. Name it cboRetreatant or
something appropriate. The RowSource property of the combo box would be:

SELECT RetreatantID, FirstName & " " & Lastname AS FullName
FROM Retreatants
ORDER BY LastName, FirstName;

To get the combo box to show the names you set its properties like so:

BoundColum 1
ColumnCount 2
ColumnWidths 0cm;8cm

For the last one you'll use the rough equivalent in inches. The second
dimension isn't crucial so long as its wide enough so use 3 inches say; the
first one must be zero, though, as this hides the RetreatantID column.
Because the BoundColumn property is 1 this first hidden column supplies the
value of the control.

Add a button to the form to open the report in preview. The button wizard
can be used, or you can enter the code yourself in the button's Click event
procedure. If you do the latter you can add some validation code to ensure a
user picks a retreatant first:

If Not IsNull(Me.cboRetreatant) Then
DoCmd.OpenReport "YourReportNameGoesHere", View:=acViewPreview
Else
MsgBox "Please select a retreatant first.", vbExclamation, "Invalid
Operation"
End If

Al that's necessary now is to set up the queries to reference the
cboRetreatant control on the frmInvoiceReportDlg form. To do this in query
design view enter the following in the criteria row of the RetreatantID
column for each query:

Forms!frmInvoiceReportDlg!cboRetreatant

Access will add some brackets for you, which is quite normal.

However there is another way you could do this in subreports if the parent
report includes the RetreatantID column in its underlying RecordSource. If
it doesn't but is an unbound 'container' report for unlinked subreports base
the parent report on the following query as its RecordSource:

SELECT RetreatantID
FROM Retreatants
WHERE RetreatantID = Forms!frmInvoiceReportDlg!cboRetreatant;

This is the SQL equivalent of what you did in query design view. The
subreports' queries now don't need any parameters at all because you can link
each subreport to the parent report. This is done by setting each subreport
control's LinkMasterFields and LinkChildFields properties to RetreatantID.
The parent report will be restricted to the retreatant selected in the combo
box and each subreport will similarly be restricted by its link with the
parent report. By 'subreport control' incidentally I mean the controls in
the parent form which house the subreports.

Ken Sheridan
Stafford, England

Ken, thanks for your observation, and the tip about Northwind. In fact, I
had the same reaction to this design: storage space not being an issue in
this small app I preferred to store the fee amounts with each invoice also.

I got persuaded by a MicroSoft MVP however, that that constituted poor
design; and since maintaining historical data for this non-profit org wasn't
important to them, I went ahead with the "look up everything" model. It's
certainly forcing me to think in a new way and learn new skills, so that's
become the main personal value in this project.

May I bend your ear with another question?

I've now built just about all the tables, forms, and queries that will
produce individual invoices for each retreatant, and experimented
successfully with forming the main invoice report out of the 8 or so
individual subreports.

The trouble is I cut one important corner to get to this stage: I built each
of the 8 queries that underlie the 8 subreports by manually entering in the
retreatant's keyfield identifier number in the Criteria line of the QBE grid.

Of course, I don't want to give the user access to the QBE grid. I want
them to click a "Preview/Print Invoice" command button I guess, and then
choose the retreatant's name from a dropdown combo box. The keyfield
identifier number associated with each retreatant's name would then be fed to
the main invoice report and the 8 queries and subreports.

I haven't yet discovered how to feed that keyfield identifier number to the
main invoice report, and get it to fan out to the 8 queries and their
subreports. Can you point me in the general direction?

The subreports are all independant of each other, and of the main report,
except for the retreatant keyfield identifier, if that simplifies things.

 

[Ken Sheridan]

When I was a kid my dad used to say I looked like an elephant with the wind
behind it because my ears stick out, so there's still some room for manoeuvre.

Subreports are not members of the Reports collection; that only contains
open parent reports. You have to refer to them differently therefore. The
way its done is via the Report property of the parent form's subreport
control; that's the control which houses the subreport. The syntax to refer
to a control on a subreport, therefore, is:

Reports!MainInvoiceReport!SubreportControlName.Report!SumRetreatFee

in code in the parent report's module it can be shortened to:

Me!SubreportControlName.Report!SumRetreatFee

and when referencing it in the ControlSource of a control on the parent form
to:

SubreportControlName.Report!SumRetreatFee

In the last case Access will wrap the object names in brackets for you.

The above is also true for subforms in which case you reference a control on
a subform via the Form property of the subform control on the parent form.

Ken Sheridan
Stafford, England

Ken, you seem to have a knack for identifying exactly what I don’t know, and
filling in the knowledge gap succinctly.

I was suspecting that parameter queries were the ticket, but indeed, didn’t
understand that I could reference a control in the criteria row of a query.
I have now done that, and the dialogue form works just fine to establish the
parameter for, and preview, the container report, showing and totaling all
test data accurately. (By way of explaining the delay, I’ve also survived a
major heat and storm wave, several ISP disruptions, and being pulled away to
another project.)

The second approach, involving LinkMasterFields and LinkChildFields, struck
me as more elegant, but I couldn’t get it to work, so went with referencing
the cboRetreatant dialogue form control in each query.

Does your ear still have some flex in it, or have I worn it out? I’m having
trouble getting grand totals in the container report’s footer.

The container report, MainInvoiceReport, contains 6 subreports of different
kinds of charges (egg., retreat fees, accommodation level, snack bar tab,
etc.). Each subreport details every charge incurred in that category by the
Retreatant, and additionally totals the category in a subreport group footer,
in a textbox with its control source set to an expression like

= Sum([RetreatFee])

The subreports are grouped by Registrant (aka Retreatant), as you suggested.


Two more subreports contain credits to be applied against the charges.
These are: payments; and credits such as early registration discounts,
bursaries, refunds, and the like. These 2 subreports also detail each
credit, and total the credits in their group footers.

In the container MainInvoiceReport I’m trying to produce, in 3 text boxes
and using the expression builder, a grand total of the 6 charge totals,
another grand total of the 2 credit totals, and a balance owing.

Here’s the expression to grandtotal the 6 charges subreports, in case that
helps:

=( Reports![subrepICRetreatFee2]![SumRetreatFee] +
Reports![subrepCabinCharge]![SumCabinCharge] +
Reports![subrepStorehouseTab]![SumItemTotal] +
Reports![subrepGroceryPurchase]![SumGroceryAmount] +
Reports![subrepPersonalServicesPurchase]![SumPersonalServiceAmount] +
Reports![subrepMiscellaneous]![SumMiscChargeAmount] )


The result is

#Name?

My result is

}-(

 

[katsup]

Noble creatures, elephants. And though I miss the feel of ivory under my
fingers at my modern piano, I'm content with what the lack of it signifies
about our treatment of that animal today.

The grand total worked, sort of. (Sheepish grin, if I can jump species
metaphorically...)

Referring to the report property of the parent form’s subreport controls
worked perfectly to get a grand total of the 6 charge subtotals, and 2 credit
subtotals…when the test retreatants had amounts in each and every category to
be totaled.

When they had amounts in only some but not all of the categories, the grand
total fields showed #ERROR.

Is it possible for you to hazard a guess as to what’s going wrong? Should I
set a zero default value in the table definitions for all the currency fields
to make this problem go away? (I don’t mind having $0.00s show up in main
invoice report categories…)

Here’s the code that worked when all categories had non-zero values:

=((Reports!MainInvoiceReport!subrepICRetreatFee2.Report!SumRetreatFee)+(Reports!MainInvoiceReport!subrepCabinCharge.Report!SumCabinCharge)+(Reports!MainInvoiceReport!subrepStorehouseTab.Report!SumItemTotal)+(Reports!MainInvoiceReport!subrepGroceryPurchase.Report!SumGroceryAmount)+(Reports!MainInvoiceReport!subrepPersonalServicesPurchase.Report!SumPersonalServiceAmount)+(Reports!MainInvoiceReport!subrepMiscellaneous.Report!SumMiscChargeAmount))

When I was a kid my dad used to say I looked like an elephant with the wind
behind it because my ears stick out, so there's still some room for manoeuvre.

Subreports are not members of the Reports collection; that only contains
open parent reports. You have to refer to them differently therefore. The
way its done is via the Report property of the parent form's subreport
control; that's the control which houses the subreport. The syntax to refer
to a control on a subreport, therefore, is:

Reports!MainInvoiceReport!SubreportControlName.Report!SumRetreatFee

in code in the parent report's module it can be shortened to:

Me!SubreportControlName.Report!SumRetreatFee

and when referencing it in the ControlSource of a control on the parent form
to:

SubreportControlName.Report!SumRetreatFee

In the last case Access will wrap the object names in brackets for you.

The above is also true for subforms in which case you reference a control on
a subform via the Form property of the subform control on the parent form.

Ken Sheridan
Stafford, England

Ken, you seem to have a knack for identifying exactly what I don’t know, and
filling in the knowledge gap succinctly.

I was suspecting that parameter queries were the ticket, but indeed, didn’t
understand that I could reference a control in the criteria row of a query.
I have now done that, and the dialogue form works just fine to establish the
parameter for, and preview, the container report, showing and totaling all
test data accurately. (By way of explaining the delay, I’ve also survived a
major heat and storm wave, several ISP disruptions, and being pulled away to
another project.)

The second approach, involving LinkMasterFields and LinkChildFields, struck
me as more elegant, but I couldn’t get it to work, so went with referencing
the cboRetreatant dialogue form control in each query.

Does your ear still have some flex in it, or have I worn it out? I’m having
trouble getting grand totals in the container report’s footer.

The container report, MainInvoiceReport, contains 6 subreports of different
kinds of charges (egg., retreat fees, accommodation level, snack bar tab,
etc.). Each subreport details every charge incurred in that category by the
Retreatant, and additionally totals the category in a subreport group footer,
in a textbox with its control source set to an expression like

= Sum([RetreatFee])

The subreports are grouped by Registrant (aka Retreatant), as you suggested.


Two more subreports contain credits to be applied against the charges.
These are: payments; and credits such as early registration discounts,
bursaries, refunds, and the like. These 2 subreports also detail each
credit, and total the credits in their group footers.

In the container MainInvoiceReport I’m trying to produce, in 3 text boxes
and using the expression builder, a grand total of the 6 charge totals,
another grand total of the 2 credit totals, and a balance owing.

Here’s the expression to grandtotal the 6 charges subreports, in case that
helps:

=( Reports![subrepICRetreatFee2]![SumRetreatFee] +
Reports![subrepCabinCharge]![SumCabinCharge] +
Reports![subrepStorehouseTab]![SumItemTotal] +
Reports![subrepGroceryPurchase]![SumGroceryAmount] +
Reports![subrepPersonalServicesPurchase]![SumPersonalServiceAmount] +
Reports![subrepMiscellaneous]![SumMiscChargeAmount] )


The result is

#Name?

My result is

}-(

 

[Ken Sheridan]

The error is being caused by one or more of the subreport's not returning any
data. You could create a convoluted expression which examines the HasData
property of each subreport in an IIf function call and adds zero if the
property is False:

=IIf(Reports!MainInvoiceReport!subrepICRetreatFee2.Report.HasData,
Reports!MainInvoiceReport!subrepICRetreatFee2.Report!SumRetreatFee,0) +
IIf(Reports!MainInvoiceReport!subrepCabinCharge.Report.HasData,
Reports!MainInvoiceReport!subrepCabinCharge.Report!SumCabinCharge,0) +
IIf(<and so on>

Another way would be to leave the text box's ControlSource blank and assign
the values to it in the Format event procedure of whatever section the
subreports and grand total text box are in:

Dim rpt as Report
Dim curGrandTotal As Currency

' examine each subreport in turn for data and
' increment the curGrandTotal variable only if report has data
Set rpt = Me.subrepICRetreatFee2.Report
If rpt.HasData Then
curGrandTotal = curGrandTotal + rpt!SumRetreatFee
End If

Set rpt =Me.subrepCabinCharge.Report
If rpt.HasData Then
curGrandTotal = curGrandTotal + rpt!SumRetreatFee
End If

<and so on for other subreports>

' assign value to unbound text box on the form
Me.txtGrandTotal = curGrandTotal

In essence these two approaches are the same, but the second uses structured
code which is more readable and hence more easily maintainable.

Regardless of this particular problem I would always set the DefaultValue of
currency fields to zero. Nulls are semantically ambiguous because they are
not values but the absence of a value, so a Null currency field could mean
anything. The example I usually cite is that of a customer's CreditRating
field. If this is Null does it mean the customer is extended zero credit, or
are they extended unlimited credit, or does it mean something else? You
might interpret it in one way, but the data itself doesn't mean anything.
Purists would go even further and say that Nulls should not be allowed in
tables at all because the database relational model stipulates that every
column position in every row in a table must contain a legitimate value of
the relevant attribute type. Consequently a Null at a column position in a
row in a table is forbidden by the relational model because Null is not a
value. Chris Date, who along with Ted Codd pretty well invented the
relational model, is the foremost proponent of this view. I'm a little more
lenient in my view of the matter, but do tend to be on Date's side of the
fence on this to a large degree.

Ken Sheridan
Stafford, England

 

[katsup]

I’ll try the structured code approach, but a few questions arise:

1. The subreports are in the main report’s detail section, and the grand
total text box is in its footer, i.e., 2 different sections. I’m guessing I
should supply the event procedure code to just the footer section, but your
explanation seems to assume that both the subreports and the grand total text
box are in the same section. Have I misunderstood?

2. Where do I put this code? Do I right-click the Report Footer section
identifier bar, select Properties, and choose the Code Builder for the “On
Format†property of the Event tab?

3. The suggested code uses “Meâ€, as in Me.subrepCabinCharge.Report, and I
confess I didn’t understand that element when you referred to it previously
when working in the parent report’s “moduleâ€. What’s Me? (What’s a module?)

When I started this project, I relied on Access' table wizards to supply
most of the data definition details. Dem wiz's don supply default zeros.
(Oh, just shoot me.)
But I have now set the DefaultValue of every blessed currency field in my
nearly dozen tables to zero. That simple action cleared up several other
little blemishes in my reports and forms. Add my vote for relegating Null
values to existential polemics.

 

[Ken Sheridan]

If the report only relates to one retreatant, and each subreport consequently
only appears once then you can put the code either in the detail section's
Format event procedure or the Report Footer section. Either way it will
assign the value to the text box in the footer. The footer's event procedure
is perhaps a more logical place and your description of how to do this is
spot on.

If the report returns multiple retreatants then you could do the same, but
use a group footer, having grouped the report by retreatant, could be used to
get the total per retreatant. A grand total for all retreatants could also
be included in the report footer, which could be done in a variety of ways.

A module is an object which contains code. There are standard modules,
which you see in the modules tab, in which you can put custom functions or
procedures to be used anywhere in the database, e.g. you might create a
function to return a person's current age from their data of birth. There
are also class modules. Each form and report has a class module which
contains the event procedures associated with objects in a form or report. A
button on a form can have a Click event procedure which contains code
executed when the button is clicked. A class module can also be independent
of a form or report and used for defining custom objects. One I use
frequently is a class module which opens a common dialogue to browse to a
file and return the path to the file. A custom class module is used by
creating a new instance of the class in code; the properties and methods of
the class as defined in the module can then be invoked. Standard modules and
custom class modules are created from scratch by the developer, whereas form
and report class modules are automatically created when you use the interface
to enter code as an event procedure, or if you click the code button on the
toolbar in design view to open the module and add a procedure or function; a
procedure or function in a form or report's class module is only available
while the form or report is open and are normally called by other code within
the same module.

Technically speaking the Me keyword refers to the instance of the class in
which the code is executing. What this means in effect is that you can use
it as a short way of referring to the form or report in whose module the code
is being executed, rather than using a fully qualified reference of the
Reports!MyReport or Reports("MyReport") type.

Wizards are fine for saving the tedium of setting up objects manually so
long as you understand what the wizard is doing and don't just accept the end
result blindly. Treat them as a starting point to create the basic object,
be it a table, form, report etc., which you can then fine tune to suit the
requirements of the application.

 

[katsup]

It’s working, and what pleasure!

I coded grand totals for both charges and credits in the OnFormat event
procedure, and the test data run executes correctly for both scenarios: all
categories containing data, and only some containing data. One cosmetic
feature could be improved: I get empty spaces (no subreport headers, no
borders, no nuttin’) on the invoice when subreports had no data to furnish.
So I’m sniffing around the help files for clues to put at least a border and
header in that space. OnNoData property? Visible property?

That's the last major hurdle for completing this project. Now there are
just a few easy loose-ends reports to brew up and the user interface. And
two weeks ahead of deadline!

This would have been absolutely impossible without your guidance, Ken. What
a faithful shepherd you’ve been, pulling me out of my fumbling mistakes each
time, and giving me a major tutorial on the Access context into the bargain.
Please accept my warm thanks.