By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,905 Members | 1,637 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,905 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Jun 13 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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

Jun 14 '07 #2

P: n/a
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


Jun 14 '07 #3

P: n/a
"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
Jun 17 '07 #4

P: n/a
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

Jun 17 '07 #5

P: n/a
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.
Jun 18 '07 #6

P: n/a

"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.

Jun 18 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.