Getting subtotals from detail section into group footer.

[bigdavidt]

I am working on a report where I am trying to get subtotals by putting code in my report's VBA window. So far my code is crashing because it comes to the EOF(). What is the correct syntax to avoid this problem?

 

[dhookom]

Subtotals in reports rarely if ever require code. Share more about your code and what you are attempting to do with the code.

Duane
Hook'D on Access
MS Access MVP

 

[bigdavidt]

I am working on a pair of reports for sister companies that have fields for a KeyCode, Date, Orders, SaleAmount, and Description. There is a group footer that shows sums for a year (in this case, 2006 and 2007). There is another group with a header showing customer information, and another that shows grand totals for each customer. The records are displayed in descending order according to date.

The trickey part is that the grand totals are not supposed to show data for the most recent six months. There are ways to set up Access queries to do this, but they are slow, and in the case of one of the reports, some of the recent data seems to have caused the grand totals to zero out. I have been working on VBA code for the code window in the report files themselves to try and do these calculations. (I did find a way to change the background color of records for the most recent six months som people could easily see them.)

One approach I tried displayed only totals for one year in the grand totals section. Another seemed to give me totals for everything, and other approaches gave me grand totals that were flat out bizarre.

What sort of syntax should I use, and what other things should I do to my report files?

Here is the code for changing the background color. Thank you in advance.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim lngShade, lngWhite As Long
Dim booMailDate As Boolean
Dim intRecYear, intRecMonth, intTYear, intTMonth As Integer

'Gets the year and month values.
intRecYear = Me.RYEAR 'Gets the year for the record date.
intRecMonth = Me.RMONTH 'Gets the month for the record month.
intTYear = Me.TYEAR 'Gets the year for today's date.
intTMonth = Me.TMONTH 'Gets the month for today's date.

'Sets the values of the variables for determining the background
'color of text boxes in the detail section of the report.
lngShade = RGB(200, 200, 200) 'Sets the background color to a light gray.
lngWhite = RGB(255, 255, 255) 'Sets the background color to white.

'Determines if a record's date is within the most recent six months, including the present one.
booMailDate = Not IIf(intTYear = intRecYear And intTMonth >= 6, _
intTYear = intRecYear And intRecMonth + 5 >= intTMonth, _
intTYear = intRecYear And intTMonth >= intRecMonth Or _
intTYear = intRecYear + 1 And intRecMonth - 7 >= intTMonth)

'Sets the background color of text boxes in the detail section of the report.
If booMailDate Then 'Records not from the most recent six months (including the present one) have a white background.
Me.KEY_CODE.BackColor = lngWhite
Me.MAIL_DATE.BackColor = lngWhite
Me.Description.BackColor = lngWhite
Me.ORDERS.BackColor = lngWhite
Me.SALES.BackColor = lngWhite
Else 'Records from the most recent six months (including the present one) have a light gray background.
Me.KEY_CODE.BackColor = lngShade
Me.MAIL_DATE.BackColor = lngShade
Me.Description.BackColor = lngShade
Me.ORDERS.BackColor = lngShade
Me.SALES.BackColor = lngShade
End If
End Sub

 

[dhookom]

grand totals are not supposed to show data for the most recent six months"
I don't see in your code any values that are being summed or any recordsets that would cause EOF. The code only changes the formats.

If you want to create a total in a group or report header or footer that aggregates specific records within the section, you can use an expression like:

=Sum(Abs( {your condition} ) * {Expression To Sum})

Duane
Hook'D on Access
MS Access MVP

 

[bigdavidt]

That is not a bad idea, but I am afraid my filtering criteria will not fit into the ControlSource property.

What I had tried to do was to declare a variable:

Dim intVarName As Integer = 0

In the area where the background color was set I had a statement like this:

intVarName = intVarName + Me.ORDERS

It is easy to put this total into the footer box for number of orders, like so:

Me.ORDERS6 = intVarName

What I need to do is to get the sum for more than one year, minus the most recent six months worth of data. For instance, should I declare another variable, such as:

Dim intVarTotal As Integer = 0

and then have the following:

intVarTotal = Me.ORDERS6 + intVarName
Me.ORDERS6 = intVarTotal

I was thinking along these lines before, but I either did not account for some quirk of Access or I did not flesh out my logic adequately.

 

[dhookom]

I wouldn't even try use code to sum values in a report. If you use report section events, these might occur twice per record.

How do you get this error [red]comes to the EOF()[/red] when you aren't opening any recordsets?


I'm not sure why you can't use a simple expression like:

=Sum(Abs(DateDiff("m",[DateField],Date())>6 ) * [ORDERS])

Duane
Hook'D on Access
MS Access MVP