1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Using VBA code, dynamically add records to detail section of existing report

Discussion in 'MS Access' started by sharon7, Apr 7, 2016.

  1. sharon7

    sharon7 New Member

    Joined:
    Feb 23, 2012
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    0
    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?

    Code:
        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
    
    
     

Share This Page