VBA/Excel/Access/Word/Access/Report

Adding Controls to the Report

 
Sub NewReport()
    Dim myReport As Report
    Dim strReportName As String
    Dim txtReportColumns As Access.TextBox
    Dim lblReportLabel As Access.Label
    Dim intWidth As Integer
    strReportName = "myReport"
    Set myReport = CreateReport
    myReport.RecordSource = "SELECT * FROM Employees"
    myReport.Section("Detail").Height = 350
    Set txtReportColumns = CreateReportControl(myReport.Name, acTextBox, _
     , , "txtCustNumber")
    Set lblReportLabel = CreateReportControl(myReport.Name, acLabel, _ 
    acPageHeader)
        lblReportLabel.Name = "lblCustNumber"
        lblReportLabel.Caption = "Customer Number"
        lblReportLabel.Width = 2000
        lblReportLabel.Height = 300
        lblReportLabel.FontBold = True
    Set txtReportColumns = CreateReportControl(myReport.Name, acTextBox, _
     , , "txtCustLastName", 3000)
    Set lblReportLabel = CreateReportControl(myReport.Name, acLabel, _
     acPageHeader, , ,3000)
        lblReportLabel.Name = "lblLastName"
        lblReportLabel.Caption = "Last Name"
        lblReportLabel.Width = 2000
        lblReportLabel.Height = 300
        lblReportLabel.FontBold = True
    Set txtReportColumns = CreateReportControl(myReport.Name, acTextBox, _
             , , "txtCustFirstName", 6000)
             
        Set lblReportLabel = CreateReportControl(myReport.Name, acLabel, _
         acPageHeader, , , 6000)
        lblReportLabel.Name = "lblFirstName"
        lblReportLabel.Caption = "First Name"
        lblReportLabel.Width = 2000
        lblReportLabel.Height = 300
        lblReportLabel.FontBold = True
         DoCmd.Save , strReportName
            DoCmd.Close , , acSaveYes
            DoCmd.OpenReport strReportName, acViewPreview
End Sub

Create a named report

 
Sub NewReport()
    Dim myReport As Report
    Dim strReportName As String
    strReportName = "myReport"
    Set myReport = CreateReport
    DoCmd.Save , strReportName
    DoCmd.Close , , acSaveYes
End Sub

Creating an Empty Report

 
Sub NewReport()
    Dim myReport As Report
    Set myReport = CreateReport
    DoCmd.Close , , acSaveYes
End Sub

Open report

 
Sub runReport()
    Dim con As ADODB.Connection
     Set con = New ADODB.Connection
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=C:\mydb.mdb;"
    DoCmd.OpenReport "rptCustomer", acViewPreview
End Sub

Print preview Report

 
Sub cmdPrint_Click()
   
   DoCmd.OpenReport "rptSales", acViewPreview
   
End Sub