Access VBA

css navigation by

Make a Report Menu

In my client's Access database, he wanted to create queries himself and be able to send multiple reports to Excel or text without coding. Form mimics the underlying table and adds check boxes. Query looks to see which boxes are checked and distributes to each type.
Private Sub cmdSendRpts_Click()
    '01/07/2024     Generic Code to send reports where desired
    Dim RS          As Recordset
    Dim dB          As Database
    Dim SQLstr      As String
    Dim WritePath   As String
     DoCmd.RunCommand acCmdRefresh      ' updates data table before running query
    On Error GoTo cmdCloseRpt_Click_Err
    WritePath = KeyVal("DataPath")
    SQLstr = "SELECT tblReports2Print.ReportName, tblReports2Print.QueryName, tblReports2Print.Print,        tblReports2Print.DispoType" & vbCrLf
    SQLstr = SQLstr & " FROM tblReports2Print" & vbCrLf
    SQLstr = SQLstr & " WHERE (((tblReports2Print.Print) = True))" & vbCrLf
    SQLstr = SQLstr & " ORDER BY tblReports2Print.DispoType,  tblReports2Print.ReportName;"
 '   Debug.Print SQLstr
    Set dB = CurrentDb()
    Set RS = dB.OpenRecordset(SQLstr, dbOpenDynaset)
        Do While Not RS.EOF
            Select Case RS!DispoType
                Case "Excel"
                    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, RS!QueryName, WritePath & RS!ReportName & "_" & _
                    Format(Now(), "yyyymmdd"), True
                '    MsgBox "Spreadsheet " & RS!ReportName & " sent to " & WritePath, vbOKOnly, "Spreadsheet sent"
                Case "On Screen"
                    DoCmd.OpenQuery RS!QueryName, acViewPreview
                Case "Print", "Report"
                    DoCmd.OpenReport RS!QueryName, acViewPreview
                Case "Text"
                    MsgBox "Open " & RS!QueryName & " in the " & WritePath & " directory using Notepad or Word", vbExclamation
                Case Else
                    MsgBox "Report an error to Alan for an unexpected output type", vbCritical
            End Select
        MsgBox "All reports sent or on screen for action", vbOKOnly
    Exit Sub

    MsgBox Error$
    Resume cmdCloseRpt_Click_Exit
End Sub

I created a form in Datasheet format so the client could add queries at will. See eample close by.


Updated:  06/21/2024 07:42
This page added:  23 January 2024