Microsoft Access Report Printing Criteria Selection Form

[ Main | Access | Tips ]

I use a parameter selection form for most of the reports in a given system. For a sample screen shot see Granite Fleet Manager Report Criteria Selection.  The user selects the various criteria or combinations of criteria on the form. These various selection parameters are unbound controls, such as combo boxes, on the Report Selection form.

There are three main fields which are used to pass data to the report.  One is the SQL compatible Where clause, which I use in the reports Filter property, the human readable criteria which is on the report header and the user entered additional information field which is also on the report header.

When the user clicks on a command button requesting a specific report and action the following code is executed passing the type of command button chosen.   I also allow the user to key in extra information for whatever purpose which is stored in the Additional_Heading field.

If IsNull(Me.cboUserSelectableReportName) Then
	MsgBox "Please select a report."
Else
	Forms![GlobalOptionsHidden]![Additional Heading] = Me.Additional_Heading
	Call StdReportParmProcessing("Preview")
End If

The appropriate SQL commands are created using the below code which will be used for the reports Where clause.  Actually I use these in the reports Filter clause in the OnLoad event as you can't pass a Where clause to Leban's PDF file creation or if you want to use the reports recordsource to create an Excel spreadsheet.

    Me.Filter = Forms!GlobalOptionsHidden.ReportWhere
    Me.FilterOn = True

When the user is viewing the report they may not recall exactly which parameters were used. Or the report may be given to someone else to view.  So I create human readable parameters as well in the variable ReportParms. This is placed in a text box on the GlobalOptionsHidden form called [Report Parms].

On the report itself, in the report header section exists a Text Box with it's control source set to "=[Forms]![Report Printing Parm Sel]![Report Parms]" with the grow property set to yes. (Substitute the name of your report printing parameter selection form in the above statement.)

Sub StdReportParmProcessing(ReportAction As String)
    On Error GoTo StdReportParmProcessing_Err

    Dim Where As String, Human As String, WhereListBox As String, HumanListBox As String
    Dim ReportName As String, lngEquipmentCount As Long, i As Variant

    ReportName = Nz(Me.cboUserSelectableReportName.Column(1))
    
    If Me.FromServiceDate.Enabled = True And _
            (Not IsNull(Me.FromServiceDate) Or Not IsNull(Me.ToServiceDate)) Then
            
        If IsNull(Me.FromServiceDate) And Not IsNull(Me.ToServiceDate) Then
            ' As having only a Todate is nonsensical move it to FromDate
            Me.FromServiceDate = Me.ToServiceDate
            Me.ToServiceDate = Null
        End If

        If Not IsNull(Me.FromServiceDate) And IsNull(Me.ToServiceDate) Then
            ' Select just From Date
            Human = Human & "  and Service Order date is " & Me.FromServiceDate
            Where = Where & " AND (srServiceDate = " & Format(Me.FromServiceDate, JetDateFmt) & ")"
        ElseIf Not IsNull(Me.FromServiceDate) Then
            ' Range of Dates
            Human = Human & " and Service Order date is from " & Me.FromServiceDate & " to " & Me.ToServiceDate
            Where = Where & " AND (srServiceDate >= " & Format(Me.FromServiceDate, JetDateFmt) & " AND " & _
                "srServiceDate <= " & Format(Me.ToServiceDate, JetDateFmt) & ")"
        End If
    End If

The Customer combo box has several additional values insert in it using a Union query.  These particular defaults allow the user to select either Our Units, All Customers in addition to the specific customer.

SELECT Customer.cID, Customer.cName, IIf([cInactiveQ],"Inactive","") AS Inactive FROM Customer 
UNION SELECT -1," (Our Units)","" From zNull 
UNION SELECT -2," (All Customers)","" From zNull
ORDER BY Inactive, Customer.cName;

An appropriate default, in this case -1, is chosen in the control property sheet.   In this particular case if eCustomerID is null then this is an internal unit.

    If Me.cboCustomerID.Enabled = True And Not IsNull(Me.cboCustomerID) Then
        If Me.cboCustomerID = -1 Then
            Where = Where & " AND eCustomerID Is Null"
        ElseIf Me.cboCustomerID = -2 Then
            Human = Human & " and All Customers"
            Where = Where & " AND eCustomerID Is Not Null"
        Else
            Human = Human & " and Customer is " & Me.cboCustomerID.Column(1)
            Where = Where & " AND eCustomerID = " & Me.cboCustomerID
        End If
    End If

Below is the code for temporary locations list box where the user can select multiple locations.  Thus I go through the selected items in the list box adding them to the Where clause and the human readable portion.

    If Me.lstTempLocn.Enabled = True Then
        HumanListBox = "": WhereListBox = ""
        
        For Each i In Me.lstTempLocn.ItemsSelected
            Select Case Me.lstTempLocn.ItemData(i)
            Case -1 ' Ignore as all temp locations
            Case 0 ' Unassigned temp location
                HumanListBox = HumanListBox & "Unassigned to " & Forms!GlobalOptionsHidden!goTempLocnCaption
                WhereListBox = WhereListBox & " or isNull(elLocationID)"
            Case Else
                HumanListBox = HumanListBox & ", " & Me.lstTempLocn.Column(1, i)
                WhereListBox = WhereListBox & " or elLocationID=" & Me.lstTempLocn.ItemData(i)
            End Select
            
        Next i
        If Len(HumanListBox) > 0 Then
            If Me.lstTempLocn.ItemsSelected.Count > 1 Then
                Human = Human & " and " & Forms!GlobalOptionsHidden!goTempLocnCaption & " are " & Mid(HumanListBox, 3)
            Else
                Human = Human & " and " & Forms!GlobalOptionsHidden!goTempLocnCaption & " is " & Mid(HumanListBox, 3)
            End If
            Where = Where & " AND (" & Mid(WhereListBox, 5) & ")"
        End If
    End If

And finally the finishing code.  Note that I removed code for other controls on that form that was basically duplicate of code I've shown here. 

    Where = Mid(Where, 6) ' Remove the leading " AND "
    Human = Mid(Human, 6)

'    MsgBox Where & vbCrLf & Human
    
    Forms!GlobalOptionsHidden![Report Parms] = Human
    Forms!GlobalOptionsHidden!ReportWhere = Where
    Select Case ReportAction
    Case "Print"
        DoCmd.OpenReport "user " & ReportName
    Case "Preview"
        DoCmd.OpenReport "user " & ReportName, acViewPreview
        DoCmd.Maximize
    Case "Email"
        Call EmailYourselftheReportasaPDF("user " & ReportName)
    Case Else
        MsgBox "You shouldn't be here.  Talk to Tony."
    End Select

[ Access | Main ]

   Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Granite Fleet Manager - the best designed fleet maintenance tracking and management system available