Suggest technique for a totals control based on subform

[markmarko]

I have a SalesDetails subform on which user selects various services to be
installed. The choices are looked up from a tblSalesCodes. We have an install
fee which is $19.95 for first services and 9.95 for each subsequent service.

The service choices includes both individual and bundled services. What I
mean by this is that user could select a choice that has 1 service, and
therefore the install fee is $19.95. Or they could select a choice that has 2
service, so install fee is $29.90. Or they could select 2 choices, each with
one service, so it's $29.90.

On tblSalesCodes, I have entered how many services are with any given
package, so for instance (call it CountForInstallFee), if user selects a 2
product code, there is a field in the record indicating 2 services.

I need to have a calculated field on the main form which will add all the
CountForInstallFee from all the Sales details for that order.

So far, I have it set up like this... A Totals query on tblSalesDetails with
2 fields... SalesOrder# (GroupBy) and CountForInstallFee (Sum).

The source for my form is a query called Query-SalesOrderEntry. In order to
be able to access my Totals Query, I added it to Query-SalesOrderEntry.

It works for the first record only. All other records do not calculate the
install fee.

Any suggestions?

 

[markmarko]

Of course, just after posting the question, I solved the issue.

I'll explain in case anyone else has the same problem.

I modified the setup... Instead of the calc-control being on the main form,
I placed it in the footer of the subform. That way, it didn't have to be in
the form's query. When the Product control is changed, it requeries the form
to recalculate the sum.