Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Posts
    11

    Red face Unanswered: Using VBA code, dynamically add records to detail section of existing report

    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?

    HTML 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

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    From Access help and documentation:
    You can use the CreateControl and CreateReportControl methods only in form Design view or report Design view, respectively.
    A solution would consists in opening the report in design view (it can be hidden: DoCmd.OpenReport "MyReport", acViewDesign, , acHidden), create the requested controls, then switch to Preview (acViewPreview) or Print (acViewNormal) view mode.
    Have a nice day!

  3. #3
    Join Date
    Aug 2011
    Posts
    11

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

    Quote Originally Posted by Sinndho View Post
    From Access help and documentation:

    A solution would consists in opening the report in design view (it can be hidden: DoCmd.OpenReport "MyReport", acViewDesign, , acHidden), create the requested controls, then switch to Preview (acViewPreview) or Print (acViewNormal) view mode.
    Thanks. I'll give this a try.

    Sharon

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •