Access Diva Filtering Reports

Filtering Reports...

Reports are a way to organize and present data to Users from the simple to the more complex. Here we'll review a couple of different way to filter reports giving Users control over what they see. Typically you have...

If your field is numeric...
DoCmd.OpenReport "YourReport", acViewPreview, , "[FieldInReport]=" & Me![ControlOnForm]

If your field is text…
DoCmd.OpenReport "YourReport", acViewPreview, , "[FieldInReport]='" & Me![ControlOnForm] & "'"

But we want to give them more flexibility...

The difficult I do immediately, the impossible takes a little bit longer.

Reports

Tips (Main)

Home

Print Full or Partial Sheet of Same Labels

Print One or More Labels for One or More Clients (Records)

Multiple copies of 1(one) Report using a Cartesian Product query

Keep Main Report/Subreport Records Together

Multicolumn Report with Images

Ranking Reports Using Shapes

Print Constant Number of Lines (or add a Constant Number of Lines)

Custom Page Header

Send Report (or Labels) to Specific Printer

Single Report Filter

Multiple Reports Filter

Once you decide what fields you want to filter on you can create your Form. When making your Form be sure to set it as a pop-up and moveable. After all, you want them to be able to move it out of the was of the Report. In the example above it's not especially important as the Report is going out to Excel but if using with an Access Report then the Form is guaranteed to stay on top. This way the User can change the Filters without opening and closing the Report or the Form. Once your Form is done you can start adding the code, see below...

(Note, if using an Excel report then you are going to have to close the open Excel workbook before outputting the updated one or your User will get an error message; the Access Report will simply refresh so no worries there.)

Private Sub cmdSendToExcel_Click()
'From http://allenbrowne.com/ser-62.html
        Dim dbs As DAO.Database
        Dim qryDef As DAO.QueryDef
        Dim strSQL As String
        Dim strWhere As String
        Dim lngLen As Long
        Set dbs = CurrentDb
 
    strSQL = "SELECT blah, blah, blah...
 
    'Number
    If Not IsNull(Me.cboCustomerID) Then
        strWhere = strWhere & "([crCustomerID] = " & Me.cboCustomerID & ") AND "
    End If
 
    'Date
    If Not IsNull(Me.txtRunDate) Then
        strWhere = strWhere & "([crRunDate] >= " & Format(Me.txtRunDate, conJetDate) & ") AND "
    End If
 
    If Not IsNull(Me.txtStartDate) Then
        strWhere = strWhere & "([crRunDate] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
    End If
 
    If Not IsNull(Me.txtEndDate) Then
        strWhere = strWhere & "([crRunDate] <= " & Format(Me.txtEndDate, conJetDate) & ") AND "
    End If
 
    If Not IsNull(Me.cboStatusID) Then
        strWhere = strWhere & "([crStatusID] = """ & Me.cboStatusID & """) AND "
    End If
 
    lngLen = Len(strWhere) - 5
 
    If lngLen <= 0 Then
        strSQL = strSQL
        Set qryDef = dbs.CreateQueryDef("qryExportCoilRunCheck", strSQL)
        'DoCmd.OpenQuery qryDef.Name
        qryDef.Close
        Set qryDef = Nothing
        DoEvents
        Call SendToExcel("qryExportCoilRunCheck", "RunCheck")
        DoEvents
        DoCmd.DeleteObject acQuery, "qryExportCoilRunCheck"
    Else
        strWhere = Left$(strWhere, lngLen)
 
        strSQL = strSQL & " WHERE " & strWhere
        Set qryDef = dbs.CreateQueryDef("qryExportCoilRunCheck", strSQL)
        'DoCmd.OpenQuery qryDef.Name
        qryDef.Close
        Set qryDef = Nothing
        DoEvents
        Call SendToExcel("qryExportCoilRunCheck", "RunCheck")
        DoEvents
        DoCmd.DeleteObject acQuery, "qryExportCoilRunCheck"
    End If
 
        dbs.Close
        Set dbs = Nothing
End Sub

Function SendToExcel(strTQName As String, strSheetName As String)
'From https://btabdevelopment.com/export-a-table-or-query-to-excel-to-specific-worksheet/
'strTQName is the name of the table or query you want to send to Excel
'strSheetName is the name of the sheet you want to send it to
 
        Dim rst As DAO.Recordset
        Dim ApXL As Object
        Dim xlWBk As Object
        Dim xlWSh As Object
        Dim strPath As String
 
        On Error GoTo Err_Handler
 
            'strPath = "C:\ctl\ReportTemplates\rptCoilRunCheck.xlsx"
 
            Set rst = CurrentDb.OpenRecordset(strTQName)
            Set ApXL = CreateObject("Excel.Application")
 
            Set xlWBk = ApXL.Workbooks.Open(strPath)
            ApXL.Visible = True
 
            Set xlWSh = xlWBk.Worksheets(strSheetName)
            xlWSh.Range("A3").Value = Date
 
            rst.MoveFirst
            xlWSh.Range("A5").CopyFromRecordset rst
            ' selects the first cell to unselect all cells
            xlWSh.Range("A5").select
 
        xlWSh.Activate
        xlWSh.Cells.Rows(4).AutoFilter
        xlWSh.Cells.Rows(4).EntireColumn.AutoFit
 
        rst.Close
        Set rst = Nothing
        'Remove prompts to save the report
        ApXL.DisplayAlerts = False
        xlWBk.SaveAs "C:\ctl\MyReports\CoilRunCheck.xlsx", 51
        ApXL.DisplayAlerts = True
        'ApXL.Quit
 
        Exit Function
Err_Handler:
        DoCmd.SetWarnings True
        MsgBox Err.Description, vbExclamation, Err.Number
        Exit Function
 
End Function

Module behind the Form

Code on Command Button

The Multiple Report Filter utilizes the multiple types of filters including the code shown above only it's within a Select Case statement, i.e.

Select Case Me.cboReportOpener
    Case 1  'Report 1
	'Filter Code Here
    Case 2  'Report 2
	'Filter Code Here
    Case 3  'Report 3
	'Filter Code Here
End Select

So, you could have a one liner on Case 1, code that send your Report to Excel, like the above on Case 2, and one to PDF on Case 3.

In this example you will need a Table, i.e. tlkpReports, to hold the name of the Reports which will be used as the Row Source for cboReportOpener, the Select Reports combo box. The Primary Key from that Table is what you will be using in the Select Case statement.

The advantage of this type of Form is if you don't have a lot of Reports or your database has several sections, i.e. Contacts, Invoicing, you can organize them according to section or just have them all in one place.

Private Sub cmdClear_Click()
On Error Resume Next
    'Purpose:   Clear all the search boxes.
    Dim ctl As Control
 
    'Clear all the controls in the Form Detail section.
    For Each ctl In Me.Section("Detail").Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next
 
    ClearListBox Me.lstLocationID  'Only use if you have a List Box
 
End Sub

I use Command Buttons instead of regular labels on the Multiple Reports Filter so the User doesn't have to clear all the Filters and start again. They can just clear one or more as they see fit.

Clearing the Filters...

Sub ClearListBox(pctlListBox As ListBox)
     '============================================================
     ' Purpose: clear all selection from a list box control
     ' Programmer: Duane Hookom
     ' Called From: Multiple
     ' Date: 2/21/2003
     ' Parameters: list box object
     '============================================================
     On Error GoTo ClearListBox_Err
     Dim strErrMsg As String 'For Error Handling
 
     Dim varitem As Variant
 
     For Each varitem In pctlListBox.ItemsSelected
     pctlListBox.Selected(varitem) = False
     Next
 
 
ClearListBox_Exit:
     On Error Resume Next
     Exit Sub
 
ClearListBox_Err:
     Select Case Err
     Case Else
     strErrMsg = strErrMsg & "Error #: " & Format$(Err.Number) & vbCrLf
     strErrMsg = strErrMsg & "Error Description: " & Err.Description
     MsgBox strErrMsg, vbInformation, "ClearListBox"
     Resume ClearListBox_Exit
     End Select
 End Sub

If you use a List Box then you will need to use Duane Hookom's code below to clear it. You will also need to use it on the Event Procedure of the Command Button to clear the List Box.