How can I use grouping from VB code?

[OkieGlocker]

Hello to all - new to this board and need some advanced help.

I have a report that was inherited from a previous developer that used 30 unbound Text Boxes and IIF statements to count students who graduated in a certain year and met 2 other criteria. This report has the output grouped by County and, within County, by School. It works well, even if it is a little slow but it is high maintenance. I have been given the task of making the report low-to-no maintenance and speeding it up.

I have created a QueryDef to create a RecordSet that uses a GradYr field as a parameter. This report covers a span of 15 years, but the report doesn't know which years until runtime when it grabs the highest year from GradYr, then counts backwards 14 years to get the range for the report. That part works fine and looping through the years is no problem. For each iteration of the loop I requery with a different GradYr from the parameter and stick the count in an array. After the loop is completed, I pull my results out of the array to populate the report fields.

Here's my problem: How do I get a coded solution to recognize the groupings of the report page and give me totals for each group? When I run the report it is much quicker, but it only displays totals in every field.

I've tried this code in the OnOpen and OnActivate events of the report and the OnFormat and OnPrint events of the group footers and nothing has given me what I need. I either get all totals printing or a blank report, so this may or may not be an event issue.

How can I get my code to run within the report groups and give me totals in the footers for school (inner group) and County (outer group)? I'm stumped on it.

Thanks in advance for any help any of you can give me.

David

 

[AppStaff]

Might be a good idea to post your code and a sample of the output you'd like.

 

[ZmrAbdulla]

Is that not easy by placing an unbound textbox in the groupfooter and setting it's control source

=Sum([[COLOR=red]GroupCountTextBoxName[/color]])

Please change the GroupCountTextBoxName to exact name of the control.
Also the same in th reportfooter ?

Zameer Abdulla
Visit Me

 

[OkieGlocker]

Here's a partial listing of the code. I left out the Public variable declarations and only showed how the first column is displayed. I'll show you what a portion of the report itself looks like below the code.

'01/18/2005
'This report is driven from the gradyr field in the query from the table Informix_Ohlap.
'This determines the latest year to be used on the first report (rptStuCount).
'Once gy15 is determined, gy14 is simply a matter of subtracting 1 year gy14=gy15-1,
'gy13=gy15-2, gy12=gy15-3...gy1=gy15-14.
'
'From this point, all you have to do is plug in each respective gradyr variable
'into its year group. To get the labels, use the Right() function.
'Let L15 = "'" & Right(gy15,2). This should produce '09

'Private Sub Report_Open(Cancel As Integer)
Private Sub Report_Activate()

' Set up Error Handler <==============

Dim rs As Recordset
Dim EnrollRs As Recordset
Dim HistRs As Recordset
Dim db As Database
Dim EnrollDb As Database
Dim HistDb As Database
Dim rsSearchStr As String
Dim qdfHist As QueryDef
Dim qdfEnroll As QueryDef
Dim HistCount(1 To 10) As String
Dim EnrollCount(11 To 15) As String
Dim iHist As Integer, iEnroll As Integer

' Before this goes out, check that all db's and rs's are closed! <========

Set db = CurrentDb
Set HistDb = CurrentDb
Set EnrollDb = CurrentDb

'Create the History qdf and initialize a RecordSet of zeros.
' This will be requeried later.

Set qdfHist = HistDb.CreateQueryDef("", "PARAMETERS pGradyr TEXT; " _
& "SELECT DISTINCT(ssn) FROM informix_ohlap " _
& "WHERE (status = '05' " _
& "OR status = '05P' " _
& "OR status = '08' " _
& "OR status = '09' " _
& "OR status = '10' " _
& "OR status = 'D') " _
& "AND gradyr = [pGradyr]")
qdfHist.Parameters!pGradyr = "1980"
Set HistRs = qdfHist.OpenRecordset

'Create the Enroll qdf and initialize a RecordSet of zeros.
Set qdfEnroll = EnrollDb.CreateQueryDef("", "PARAMETERS pGradYr TEXT; " _
& "SELECT DISTINCT(ssn) FROM informix_ohlap " _
& "WHERE gradyr = [pGradyr]")
qdfEnroll.Parameters!pGradyr = "1980"
Set EnrollRs = qdfEnroll.OpenRecordset

'First use this RecordSet to get the Max gradyr.
'Create the SQL statement to drive the RecordSet
Let rsSearchStr = "SELECT gradyr FROM informix_ohlap ORDER BY gradyr desc;"

'Open a SnapShot recordset with just gradyr and sort descending so the
'first record is the latest year.
Set rs = db.OpenRecordset(rsSearchStr, dbOpenSnapshot)
With rs
'Test to see if the table has records
If Not .BOF And Not .EOF Then
.MoveFirst
Let gy15 = !gradyr
Else
MsgBox "ERROR! No records found in informix_ohlap."
End If
End With
rs.Close

'Define the 14 other gradyr variables
Let gy14 = gy15 - 1
Let gy13 = gy15 - 2
Let gy12 = gy15 - 3
Let gy11 = gy15 - 4
Let gy10 = gy15 - 5
Let gy9 = gy15 - 6
Let gy8 = gy15 - 7
Let gy7 = gy15 - 8
Let gy6 = gy15 - 9
Let gy5 = gy15 - 10
Let gy4 = gy15 - 11
Let gy3 = gy15 - 12
Let gy2 = gy15 - 13
Let gy1 = gy15 - 14

'This array is used in the For Loop in the next block
'to dynamically calculate the parameter pGradYr
Dim H_ar(1 To 10) As Integer
H_ar(1) = 14
H_ar(2) = 13
H_ar(3) = 12
H_ar(4) = 11
H_ar(5) = 10
H_ar(6) = 9
H_ar(7) = 8
H_ar(8) = 7
H_ar(9) = 6
H_ar(10) = 5

'Requery qdfHist to get the counts for each year and store them
' in the array HistCount(1 to 10)

For iHist = 1 To 10
qdfHist.Parameters!pGradyr = gy15 - (H_ar(iHist))
HistRs.Requery qdfHist
If Not HistRs.BOF And Not HistRs.EOF Then
HistRs.MoveLast
HistCount(iHist) = CStr(HistRs.RecordCount)
Else
HistCount(iHist) = "0"
End If
Next iHist

'This array is used in the For Loop in the next block
'to dynamically calculate the parameter pGradYr
Dim E_ar(11 To 15) As Integer
E_ar(11) = 4
E_ar(12) = 3
E_ar(13) = 2
E_ar(14) = 1
E_ar(15) = 0
'Requery qdfEnroll to get the counts for each year and store them
' in the array EnrollCount(#)
For iEnroll = 11 To 15
qdfEnroll.Parameters!pGradyr = gy15 - (E_ar(iEnroll))
EnrollRs.Requery qdfEnroll
If Not EnrollRs.BOF And Not EnrollRs.EOF Then
EnrollRs.MoveLast
EnrollCount(iEnroll) = CStr(EnrollRs.RecordCount)
Else
EnrollCount(iEnroll) = "0"
End If
Next iEnroll

'Load the results

'Col 1
Let L1 = "'" & Right(gy1, 2)
Let GTL1 = "'" & Right(gy1, 2)
Let Me![1count].Visible = True
Let Me![1GTcounter].Visible = True
Let rCounter1 = HistCount(1)
Let rGTCounter1 = rCounter1

The Report:

High School '95 '96 '97 '98 '99 '00 ...
Adair County
Watts HS 0 637 257 370 631 827 ...
Westville HS 0 637 257 370 631 827 ...
Stillwell HS 0 637 257 370 631 827 ...

Rather than printing the school totals from the schools footer, which are the only controls visible in this group, the report is printing grand totals for each year.

The detail section of the report contains two unbound text boxes for each column: one that I accumulate totals to be printed in the school footer (Running sum over group) and one to accumulate grand totals (Running sum over all), which I reference from controls in the Report Footer. Yes, the first thing I checked were the controls in the school footer to be sure they were referencing the correct control in the details section. It seems to me that the break for each group is not firing, but I haven't been able to figure out just how to make that happen.

Thank you for your assistance.

David