[ 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
Website copyright © 1995-2013 Tony Toews