how do I calculate subtotals in a report using vba

[bogaerts]

I made a module by witch I automaticly generate a report.
This report has 2 grouplevels (you can choose the field of the grouplevel in a form)
For instance :
grouplevel 1 contains the field "person"
grouplevel 2 contains the field "product the person harvest"


In the section Detail is the field Weight and weight.
There are different dates with different weights.
I would like to calculate the sum of the weights for 1 product and 1 person
and I would also like to know the sum of the different weights of all persons

I know in access you can calculate these subtotals easily in the design mode of a repport, but I would like to do this in vba because the report has to be dynamic.

In the attachment you can find the code for making the report (it's in dutch)

 

[mcirvine]

Hi,

I'm dutch too, I'll try to give you a hand. First of all your code seems very complex for what you want to do. You can create the report/form in access, and use unbound fields. You could then write code to supply the correct label to these fields depending on the value of subtotal these subtotals you have. Also a select case statement would be much nicer way to code these lbl.caption lines in the code instead of those nested if statements.

For your totals you want you could create additional recordsets in vba in which you query the totals using SQL.

hope that helps you some