Member Avatar for sharon.chapman7
sharon.chapman7 -1 Newbie Poster

Hi,
When I use the code below, the data is written to the detail section, but only shows in the design mode. How do I get the data to display on the report?

Dim stDocName As String
    Dim db As Database
    Dim rs As Recordset
    Dim rs2 As Recordset
    Dim sSQL As String
    Dim fld As DAO.Field ' recordset field
    Dim txtNew As Access.TextBox ' textbox control
    Dim rpt As Report ' hold report object
    Dim lngTop As Long ' holds top value of control position
    Dim lngLeft As Long ' holds left value of controls position
    Dim location As Long

    ' initialise position variables
    lngLeft = 0
    lngTop = 0

    'Create the report
    'Set rpt = CreateReport

    stDocName = "rptInvoice"
    DoCmd.OpenReport stDocName, acViewDesign

    sSQL = "Select SOWCode, SowDescription, Qty, Rate, SOWTotal from tbltmpInvoice"

    ' set properties of the Report
'    With rpt
'        .Width = 8500
'        .RecordSource = sSQL
'    End With

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(sSQL)

    'Count all records in the table tblCompany
    rs.MoveLast
    rs.MoveFirst

    location = location + 900

Read_Records:

    ' Create corresponding label and text box controls for each field.
    For Each fld In rs.Fields

        ' Create new text box control and size to fit data.
        Set txtNew = CreateReportControl(stDocName, acTextBox, _
        acDetail, , fld.Value, location, lngTop)
        'txtNew.SizeToFit

        'Relocate text field
        location = location + 1000

    Next

    DoCmd.Close

    ' Increment top value for next control
    'lngTop = lngTop + txtNew.Height + 25

    stDocName = "rptInvoice"
    DoCmd.OpenReport stDocName, acViewPreview

    'DoCmd.Close

Exit_Err_SelectCustomer_AfterUpdate:
    rs.Close
    rs2.Close
    db.Close
    Set rpt = Nothing
    Exit Sub
  • 1 Contributor
  • 0 Replies
  • 218 Views

Dani AI

Generated

— controls show in Design because they were created unbound (or with a literal value) and the report never had a RecordSource set when the controls were created. Key fixes: assign the report's RecordSource first, create controls whose ControlSource is the field name (not fld.Value), give each control a unique Name, save the report, then open Preview.

Minimal step sequence that fixes the behavior:

  • Open the report in Design and get the Report object: DoCmd.OpenReport then Set rpt = Reports(stDocName).
  • Set rpt.RecordSource = sSQL before creating controls so Access knows the available fields.
  • When calling CreateReportControl, pass the field name (e.g., fld.Name) as the ColumnName/ControlSource argument — not the current field value.
  • Save the report explicitly: DoCmd.Close acReport, stDocName, acSaveYes, then open Preview.

Example (conceptual) snippet showing the essential changes:

DoCmd.OpenReport rptName, acViewDesign
Set rptObj = Reports(rptName)
rptObj.RecordSource = sSql

For Each f In rs.Fields
  Set txt = CreateReportControl(rptName, acTextBox, acDetail, , f.Name, curLeft, curTop, 1500, 300)
  txt.Name = "txt_" & f.Name
  curLeft = curLeft + txt.Width + 200
Next

DoCmd.Close acReport, rptName, acSaveYes
DoCmd.OpenReport rptName, acViewPreview

Notes and quick troubleshooting: avoid calling rs.MoveLast on an empty recordset (check If rs.BOF And rs.EOF Then), remove or initialise the unused rs2, and always save the report after design changes. If the goal is repeating rows from tbltmpInvoice, a simpler and more robust pattern is to use a subreport or bind the detail section directly to that table instead of creating controls dynamically.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.