Print values of recordset in report

[hamlett5]

I know this has to be a pretty basic function, and I have a goofy error somewhere that I am hoping someone here can point out. I am simply trying to loop through a recordset and display the values contained in a field "TAG". Code is

Private Sub Print_Report_Click()
Dim db As DAO.Database
Dim rpt As Report
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim sSQL As String
Dim txtbx As Access.TextBox
Dim lblnew As Access.Label
Dim lngleft As Long
Dim lngtop As Long
 
 
sSQL = "SELECT TAG FROM ESGBKR"
 
Set db = CurrentDb
Set rst = db.OpenRecordset(sSQL, dbOpenDynaset)
Set rpt = CreateReport
rpt.RecordSource = sSQL
 
'Set columns
Set lblnew = CreateReportControl(rpt.Name, acLabel, acDetail, , "TAG")
 
lngtop = lngtop + 200
If Not (rst.EOF And rst.BOF) Then
    rst.MoveFirst
Do Until rst.EOF = True
    For Each fld In rst.Fields
   Set txtbx = CreateReportControl(rpt.Name, acTextBox, acDetail, fld.name, lngleft, lngtop)
    txtbx.SizeToFit
    lngtop = lngtop + 25 + txtbx.Height
    Next fld
 
    rst.MoveNext
Loop
Else
MsgBox "No Records Available"
End If
 
DoCmd.OpenReport rpt.Name, acViewPreview
 
rst.Close
Set rst = Nothing
Set db = Nothing
Set rpt = Nothing
 
End Sub

It seems to loop through my table, and give me the right amount of records (i.e., 5), but each record has the same value, the first one. So the table looks like this
Tag
X4-2A
X4-2B
X4-2C
X4-2D
X4-2E

I get a report that shows a column title "TAG" and then five values below that, each of which is "X4-2A". Any ideas what I have coded incorrectly to not get the correct values?

Thanks much

[zmbd]

Line 13:
Take copy that SQL and paste it into a normal query (open in the query designer, switch to SQL view, copy-n-paste). Run this query and see what records you are actually pulling.

This is also a fairly unusual means of creating a report.
Normally, the fields in the underlying record source would have all of this setup and then the report would do all of the pretty layout and formatting.

I would move the lines 27 to 31 above 26.
Eliminate the do-loop starting at line 26

See what you get then.


(here's a working example from MS of what I think OP is trying to accomplish and upon which I based my coding suggestions: Creating Access Reports Office 2000
One thing to Note here: ADODB is used as this is in ACC2000 and there was much gnashing of teeth about what type of record set to use back then; however, in ACC2007/2010 ADODB reference may not be set as the move appears to be back to DAO as a default; thus, instead of the ADODB, use DAO or go into the references and set the ADODB library.)

[ADezii]

1. Aside from being a very unorthodox approach, you are missing a couple of key steps.
   1. You are looping thru the Recordset but the Height of the Detail Section does not allow you to see all five Values. The Height of the Detail Section needs to be adjusted (Code Line# 19).
   2. The Text Boxes are created but you have not defined their Control Source (Code Line# 31).
   3. The Text Boxes and associated Labels are bunched together. Either move the Label to the Form Header or set the width properly so they do not overlap.
2. I made some revisions to hopefully point you in the right direction, but I still do not agree with your basic approach.

Dim db As DAO.Database
Dim rpt As Report
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim sSQL As String
Dim txtbx As Access.TextBox
Dim lblnew As Access.Label
Dim lngleft As Long
Dim lngtop As Long
 
sSQL = "SELECT TAG FROM ESGBKR"
 
Set db = CurrentDb
Set rst = db.OpenRecordset(sSQL, dbOpenDynaset)
Set rpt = CreateReport
 
With rpt
  .RecordSource = sSQL
  .Section(acDetail).Height = 400
End With
 
'Set columns
Set lblnew = CreateReportControl(rpt.Name, acLabel, acDetail, , "TAG")
 
lngtop = lngtop + 200
 
If Not (rst.EOF And rst.BOF) Then
  Set txtbx = CreateReportControl(rpt.Name, acTextBox, acDetail, rst.Fields(0).Name, lngleft, lngtop)
    With txtbx
      .SizeToFit
      .ControlSource = "TAG"
    End With
    lngtop = lngtop + 25 + txtbx.Height
 
    rst.MoveNext
Else
  MsgBox "No Records Available"
End If
 
DoCmd.OpenReport rpt.Name, acViewPreview
 
rst.Close
Set rst = Nothing
Set db = Nothing
Set rpt = Nothing

[zmbd]

ADezii
Aside from being a very unorthodox approach, you are missing a couple of key steps

That means alot coming from ADezii!
I'm glad he took the time to review the code too... I missed quite a few things there!