properly displaying results of every record in a report (DAO)

[Richard Hollenbeck]

Thanks to everybody who has helped me to get this thing finally working
(somewhat). I got the field names to properly display in the
PageHeaderSection, so I won't post the code from that. Now I'm trying to
populate the records (this is based on a cross-tab query "qryTableOfGrades"
in the Detail section but it only displays the data from the last record in
every record. Here's the code. I wonder what I'm doing wrong:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim rs As DAO.RecordSet
Dim db As DAO.Database
Dim qdf As QueryDef
Dim i As Integer
Dim j As Integer
Dim intNumOfFields As Integer
Dim intRecordCount As Integer

Set db = CurrentDb
Set qdf = db.QueryDefs("qryTableOfGrades")

With qdf
.Parameters("[Forms]!frmSelectCourse]![cboSelectCourse") =
[Forms]![frmSelectCourse]![cboSelectCourse]
End With

Set rs = qdf.OpenRecordset()

rs.MoveLast
intRecordCount = rs.RecordCount
rs.MoveFirst

intNumOfFields = rs.Fields.Count

'Populate data labels
'The first three fields contain student names, course number, and course
discription and don't get used here.
For i = 4 To 45 ' make empty labels invisible.
Me.Controls("lblData" & i).Caption = ""
Me.Controls("lblData" & i).Visible = False
Next i

For j = 1 To intRecordCount
For i = 4 To intNumOfFields - 1 'I don't need the first three fields
here.
Me.Controls("lblData" & i).Visible = True 'make useable labels
visible
Me.Controls("lblData" & i).Caption = rs.Fields(i)
Next i
Next j

Set qdf = Nothing
Set rs = Nothing
set db = Nothing
End Sub

[Gary Floam]

Why are you looping through j at all?

Also

Me.("lblData" & i).Visible = True
Me("lblData" & i).Caption = rs.Fields(i).name

will do instead of

Me.Controls("lblData" & i).Visible = True
Me.Controls("lblData" & i).Caption = rs.Fields(i)

Hope this helps,

Gary
"Richard Hollenbeck" <ri****************@verizon.netwrote in message
news:2j_bi.4492$4t5.1810@trndny07...

Thanks to everybody who has helped me to get this thing finally working
(somewhat). I got the field names to properly display in the
PageHeaderSection, so I won't post the code from that. Now I'm trying to
populate the records (this is based on a cross-tab query "qryTableOfGrades"
in the Detail section but it only displays the data from the last record in
every record. Here's the code. I wonder what I'm doing wrong:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim rs As DAO.RecordSet
Dim db As DAO.Database
Dim qdf As QueryDef
Dim i As Integer
Dim j As Integer
Dim intNumOfFields As Integer
Dim intRecordCount As Integer

Set db = CurrentDb
Set qdf = db.QueryDefs("qryTableOfGrades")

With qdf
.Parameters("[Forms]!frmSelectCourse]![cboSelectCourse") =
[Forms]![frmSelectCourse]![cboSelectCourse]
End With

Set rs = qdf.OpenRecordset()

rs.MoveLast
intRecordCount = rs.RecordCount
rs.MoveFirst

intNumOfFields = rs.Fields.Count

'Populate data labels
'The first three fields contain student names, course number, and course
discription and don't get used here.
For i = 4 To 45 ' make empty labels invisible.
Me.Controls("lblData" & i).Caption = ""
Me.Controls("lblData" & i).Visible = False
Next i

For j = 1 To intRecordCount
For i = 4 To intNumOfFields - 1 'I don't need the first three fields
here.
Me.Controls("lblData" & i).Visible = True 'make useable labels
visible
Me.Controls("lblData" & i).Caption = rs.Fields(i)
Next i
Next j

Set qdf = Nothing
Set rs = Nothing
set db = Nothing
End Sub

[Richard Hollenbeck]

Thank you Gary,

I will try eliminating the 'j' loop and do your other suggestions, then
write back.

Rich

"Gary Floam" <fl***@comcast.netwrote in message
news:oN******************************@comcast.com. ..

Why are you looping through j at all?

Also

Me.("lblData" & i).Visible = True
Me("lblData" & i).Caption = rs.Fields(i).name

will do instead of

Me.Controls("lblData" & i).Visible = True
Me.Controls("lblData" & i).Caption = rs.Fields(i)

Hope this helps,

Gary
"Richard Hollenbeck" <ri****************@verizon.netwrote in message
news:2j_bi.4492$4t5.1810@trndny07...

>Thanks to everybody who has helped me to get this thing finally working
(somewhat). I got the field names to properly display in the
PageHeaderSection, so I won't post the code from that. Now I'm trying to
populate the records (this is based on a cross-tab query
"qryTableOfGrades" in the Detail section but it only displays the data
from the last record in every record. Here's the code. I wonder what
I'm doing wrong:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim rs As DAO.RecordSet
Dim db As DAO.Database
Dim qdf As QueryDef
Dim i As Integer
Dim j As Integer
Dim intNumOfFields As Integer
Dim intRecordCount As Integer

Set db = CurrentDb
Set qdf = db.QueryDefs("qryTableOfGrades")

With qdf
.Parameters("[Forms]!frmSelectCourse]![cboSelectCourse") =
[Forms]![frmSelectCourse]![cboSelectCourse]
End With

Set rs = qdf.OpenRecordset()

rs.MoveLast
intRecordCount = rs.RecordCount
rs.MoveFirst

intNumOfFields = rs.Fields.Count

'Populate data labels
'The first three fields contain student names, course number, and course
discription and don't get used here.
For i = 4 To 45 ' make empty labels invisible.
Me.Controls("lblData" & i).Caption = ""
Me.Controls("lblData" & i).Visible = False
Next i

For j = 1 To intRecordCount
For i = 4 To intNumOfFields - 1 'I don't need the first three
fields here.
Me.Controls("lblData" & i).Visible = True 'make useable labels
visible
Me.Controls("lblData" & i).Caption = rs.Fields(i)
Next i
Next j

Set qdf = Nothing
Set rs = Nothing
set db = Nothing
End Sub

[Richard Hollenbeck]

"Gary Floam" <fl***@comcast.netwrote in message
news:oN******************************@comcast.com. ..

Why are you looping through j at all?

Also

Me.("lblData" & i).Visible = True
Me("lblData" & i).Caption = rs.Fields(i).name

will do instead of

Me.Controls("lblData" & i).Visible = True
Me.Controls("lblData" & i).Caption = rs.Fields(i)

Hope this helps,

Gary
"Richard Hollenbeck" <ri****************@verizon.netwrote in message
news:2j_bi.4492$4t5.1810@trndny07...

>Thanks to everybody who has helped me to get this thing finally working
(somewhat). I got the field names to properly display in the
PageHeaderSection, so I won't post the code from that. Now I'm trying to
populate the records (this is based on a cross-tab query
"qryTableOfGrades" in the Detail section but it only displays the data
from the last record in every record. Here's the code. I wonder what
I'm doing wrong:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim rs As DAO.RecordSet
Dim db As DAO.Database
Dim qdf As QueryDef
Dim i As Integer
Dim j As Integer
Dim intNumOfFields As Integer
Dim intRecordCount As Integer

Set db = CurrentDb
Set qdf = db.QueryDefs("qryTableOfGrades")

With qdf
.Parameters("[Forms]!frmSelectCourse]![cboSelectCourse") =
[Forms]![frmSelectCourse]![cboSelectCourse]
End With

Set rs = qdf.OpenRecordset()

rs.MoveLast
intRecordCount = rs.RecordCount
rs.MoveFirst

intNumOfFields = rs.Fields.Count

'Populate data labels
'The first three fields contain student names, course number, and course
discription and don't get used here.
For i = 4 To 45 ' make empty labels invisible.
Me.Controls("lblData" & i).Caption = ""
Me.Controls("lblData" & i).Visible = False
Next i

For j = 1 To intRecordCount
For i = 4 To intNumOfFields - 1 'I don't need the first three
fields here.
Me.Controls("lblData" & i).Visible = True 'make useable labels
visible
Me.Controls("lblData" & i).Caption = rs.Fields(i)
Next i
Next j

Set qdf = Nothing
Set rs = Nothing
set db = Nothing
End Sub

I was looping through j to loop through each record and through i to loop
through every field number 3. I'm still only getting data from the last
record in the recordset for every single record. I tried eliminating the j
loop but it didn't help:

For i = 1 To rs.RecordCount - 1
For j = 4 To rs.Fields.Count - 1
Me("lblData" & j).Visible = True
Me("lblData" & j).Caption = rs.Fields(j)
Next j
Next i

[UrbanSpaceman]

I was looping through j to loop through each record and through i to loop

through every field number 3. I'm still only getting data from the last
record in the recordset for every single record. I tried eliminating the
j loop but it didn't help:

Yes you are looping correctly, but unfortunately not over the recordset. You
need to do a MoveNext, like this:

For i = 1 To rs.RecordCount - 1
For j = 4 To rs.Fields.Count - 1
Me("lblData" & j).Visible = True
Me("lblData" & j).Caption = rs.Fields(j)
Next j
rs.MoveNext
Next i

Also you could replace "For i = 1 To rs.RecordCount - 1" with:

Do Until rs.EOF
....
Loop

HTH

[Richard Hollenbeck]

Yes you are looping correctly, but unfortunately not over the recordset.

You need to do a MoveNext, like this:

For i = 1 To rs.RecordCount - 1
For j = 4 To rs.Fields.Count - 1
Me("lblData" & j).Visible = True
Me("lblData" & j).Caption = rs.Fields(j)
Next j
rs.MoveNext
Next i

Also you could replace "For i = 1 To rs.RecordCount - 1" with:

Do Until rs.EOF
...
Loop

HTH

I added your ideas but I also added a message box to see if the correct
values are ever being read. Sure enough, they are correctly cycling through
all the records. But when they get displayed, it is just the last record's
values in every record--even with the rs.MoveNext. I have these labels in
the detail section and I'm in the sub,

"Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) ."

Here's my new code:

Do Until rs.EOF 'loop through every record
For j = 3 To rs.RecordCount - 1 'loop through every field
Me("lblData" & j + 1).Visible = True 'make labels visible if
there is data
Me("lblData" & j + 1).Caption = Nz(rs.Fields(j), 0)
'populate the label captions with field data
MsgBox rs.Fields(j).Name & ": " & Nz(rs.Fields(j), 0)
Next j
rs.MoveNext
Loop

***** So to summarize, the correct data is actually being read, as is
demonstrated in the MsgBox, but only the last record is printed to every
record.

[Richard Hollenbeck]

"Richard Hollenbeck" <ri****************@verizon.netwrote in message
news:gikdi.2766$Sm5.982@trndny04...

>Yes you are looping correctly, but unfortunately not over the recordset.
You need to do a MoveNext, like this:

For i = 1 To rs.RecordCount - 1
For j = 4 To rs.Fields.Count - 1
Me("lblData" & j).Visible = True
Me("lblData" & j).Caption = rs.Fields(j)
Next j
rs.MoveNext
Next i

Also you could replace "For i = 1 To rs.RecordCount - 1" with:

Do Until rs.EOF
...
Loop

HTH

I added your ideas but I also added a message box to see if the correct
values are ever being read. Sure enough, they are correctly cycling
through all the records. But when they get displayed, it is just the last
record's values in every record--even with the rs.MoveNext. I have these
labels in the detail section and I'm in the sub,

"Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) ."

Here's my new code:

(oops! I got the loop backwards this time. Here it is corrected)

Do Until rs.EOF ' loop through every record (this works but is printing
wrong)
For j = 3 To rs.Fields.Count - 1 'loop through every field
Me("lblData" & j + 1).Visible = True 'make labels visible if
there is data
Me("lblData" & j + 1).Caption = Nz(rs.Fields(j), 0) 'populate
the label captions with field data
Next j
rs.MoveNext
Loop

except there was a MsgBox in there to track the data being read.

***** So to summarize, the correct data is actually being read, as is
demonstrated in the MsgBox, but only the last record is printed to every
record.