This is a report selector form. All of the buttons on the form print different variations on the same report. The report has a text box with a control source =GetReportTitle() that displays a description of the particular variation.
This also illustrates the use of string, date, and boolean criteria in a WHERE clause.
This is the standard module.
Global ReportTitle As String Function GetReportTitle() As String GetReportTitle = ReportTitle End Function
This is the class module for my form
Option Compare Database Option Explicit Private Sub PrintECS(withheader As String, withcrit As String) ReportTitle = withheader On Error Resume Next DoCmd.OpenReport "rptExecClaimStat", acViewPreview, , withcrit End Sub Private Sub Command26_Click() DoCmd.Close End Sub Private Sub Option1_Click() Call PrintECS("Full Report", "") End Sub Private Sub Option2_Click() Dim crit As String, resp resp = InputBox("Enter the Division:") If Len(Nz(resp, "")) > 0 Then crit = "DivAbbrv = '" & resp & "'" Call PrintECS("Single Division", crit) End If End Sub Private Sub Option3_Click() Dim crit As String, resp1, resp2 resp1 = InputBox("Starting date:") If IsDate(resp1) Then resp2 = InputBox("Ending date:") If IsDate(resp2) Then crit = "dtChanged Between #" & resp1 & "# And #" & resp2 & "#" Call PrintECS("Changed between " & resp1 & " and " & resp2, crit) End If End If End Sub Private Sub Option4_Click() Dim crit As String crit = "HighPriority = True" Call PrintECS("High Priority, all dates", crit) End Sub Private Sub Option5_Click() Dim crit As String, resp1, resp2 resp1 = InputBox("Starting date:") If IsDate(resp1) Then resp2 = InputBox("Ending date:") If IsDate(resp2) Then crit = "(dtChanged Between #" & resp1 & "# And #" & resp2 & "#) And (HighPriority=True)" Call PrintECS("High Priority Cases, changed between " & resp1 & " and " & resp2, crit) End If End If End Sub Private Sub Option6_Click() Dim crit As String, resp1, resp2 resp1 = InputBox("Starting date:") If IsDate(resp1) Then resp2 = InputBox("Ending date:") If IsDate(resp2) Then crit = "dtCreated Between #" & resp1 & "# And #" & resp2 & "#" Call PrintECS("New Cases added between " & resp1 & " and " & resp2, crit) End If End If End Sub Private Sub Option7_Click() Dim crit As String, resp1, resp2 resp1 = InputBox("Starting date:") If IsDate(resp1) Then resp2 = InputBox("Ending date:") If IsDate(resp2) Then crit = "dtClosed Between #" & resp1 & "# And #" & resp2 & "#" Call PrintECS("Cases Closed between " & resp1 & " and " & resp2, crit) End If End If End Sub Private Sub Option8_Click() Dim crit As String DoCmd.OpenForm "frmSelectCaseMgr_popup", acNormal, , , acFormEdit, acDialog If Not IsNull(WhichCaseMgrID) Then crit = "CaseManagerID = " & WhichCaseMgrID Call PrintECS("Single Case Manager", crit) End If End Sub