0 Replies - 628 Views - Last Post: 07 April 2016 - 02:46 PM

#1 [email protected]  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 25
  • Joined: 24-January 12

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

Posted 07 April 2016 - 02:46 PM

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




Is This A Good Question/Topic? 0
  • +

Page 1 of 1