Introduction
Combo boxes are a handy way to select values for various purposes, such as filtering forms and reports. The row sources can be of three types; this article discusses how to select the appropriate type of row source query, and use them to open a filtered report.
The Form
The filter form in the sample database (Filter by Form (AA 248).accdb) has five combo box selectors used to select values for filtering a report (you can also print an unfiltered report).
Figure A. The Filter form
The filter is concatenated every time a selection is made, and displayed in the Filter textbox. The number of records found for the filter is displayed in the # Records textbox, and the Filtered option is selected in the Report Type option group.
The Clear Selections button clears all the selections and sets the report type to All. Finally, the Open Report button opens the report in print preview.
VBA Code
When an item is selected in a combo box, a public String variable is set with a filter string, using the appropriate delimiters:
Text Value
Private Sub cboLocation_AfterUpdate()
On Error GoTo ErrorHandler
'Save value to public variable for use in report filter query
strLocation = Nz(Me![cboLocation].Column(0))
pstrLocationFilter = "[ProjectCityState] = " & Chr(39) _
& strLocation & Chr(39)
Debug.Print "Location filter: " & pstrLocationFilter
Me![fraReportType].Value = 2
Call CreateFilter
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number _
& " in " & Me.ActiveControl.Name & " procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
Numeric Value
Private Sub cboProjectCode_AfterUpdate()
On Error GoTo ErrorHandler
'Save value to public variable for use in report filter query
lngProjectCode = Nz(Me![cboProjectCode].Column(0))
pstrProjectCodeFilter = "[ProjectCode] = " & lngProjectCode
Debug.Print "Project Code filter: " & pstrProjectCodeFilter
Me![fraReportType].Value = 2
Call CreateFilter
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number _
& " in " & Me.ActiveControl.Name & " procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
Date Value
Private Sub cboStartDate_AfterUpdate()
On Error GoTo ErrorHandler
'Save value to public variable for use in report filter query
pstrStartDateFilter = "[WorkDate] = " & Chr(35) _
& Nz(Me![cboStartDate].Value) _
& Chr(35)
Debug.Print "Start Date filter: " & pstrStartDateFilter
Me![fraReportType].Value = 2
Call CreateFilter
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number _
& " in " & Me.ActiveControl.Name & " procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
The CreateFilter procedure concatenates the filters (only those with selected values), and calls the CreateAndTestQuery to create and save a filtered query, which is the record source of the rptWorkScheduleFiltered report:
Public Sub CreateFilter()
On Error GoTo ErrorHandler
Dim lngCount As Long
Dim strQuery As String
Dim strSQL As String
pstrFilter = IIf(pstrLocationFilter <> "", pstrLocationFilter _
& " And ", "") _
& IIf(pstrTechnicianFilter <> "", pstrTechnicianFilter & " And ", "") _
& IIf(pstrProjectCodeFilter <> "", pstrProjectCodeFilter & " And ", "") _
& IIf(pstrStartDateFilter <> "", pstrStartDateFilter & " And ", "") _
& IIf(pstrStatusFilter <> "", pstrStatusFilter, "")
Debug.Print "Filter: " & pstrFilter
If Right(pstrFilter, 5) = " And " Then
pstrFilter = Left(pstrFilter, Len(pstrFilter) - 5)
Debug.Print "Filter: " & pstrFilter
End If
'Save filter to a custom property for use in report
strPropertyName = "Filter"
lngDataType = dbText
Call SetProperty(strPropertyName, lngDataType, _
pstrFilter)
strQuery = "qryFilteredWorkSchedule"
strSQL = "SELECT * FROM qryWorkSchedule WHERE " & pstrFilter & ";"
Debug.Print "SQL for " & strQuery & ": " & strSQL
lngCount = CreateAndTestQuery(strQuery, strSQL)
Debug.Print "No. of items found: " & lngCount
Me![txtFilter].Value = pstrFilter
Me![txtNoRecords].Value = lngCount
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number _
& " in " & Me.ActiveControl.Name & " procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
Row Source Queries
There are basically three types of row source queries: (1) a value list, where the values to be selected are typed into the Row Source property of the combo box; (2) a table/query row source, with a lookup table (or a query based on the table) as the row source; and (3) a table/query row source, with a query based on a main table, set to display unique values for the field used for filtering. Depending on the filtering needed, one or another of these row source types is appropriate.
If you have just two or three standard selections, which will not change, a value list is fine. The Status combo box uses a value list:
Figure B. A Value List row source
If you want to be able to select any value, regardless of whether it has been selected in your main table or not, then a lookup table (or any linked table), or a query based on such a table, is needed. This would be the case, for example, for a table of U.S. state abbreviations, or (in the sample database) a table of technician names, where you need to be able to select a state or technician that has not previously been selected for the main table.
Figure C. A row source based on a lookup table
Finally, you can make a row source query based on unique values from your main table. This is particularly useful when filtering by dates. If you let users enter any date for filtering, they may select a date that doesn’t have any values in the main table. Instead, use a query based on the date field with Unique Values set to Yes:
Figure D. A row source for unique values from the main table
This type of row source guarantees that you can’t select a value that doesn’t exist in the main table.
References
The code in the sample database needs the following references (in addition to the default references):
Microsoft Word 14.0 Object Library (for SortDeclarations procedure only)
Microsoft Forms 2.0 Object Library (for SortDeclarations procedure only)
If you import code or objects into a database of your own, you may need to set one or more of these references. The version number may differ, depending on your Office version; check the version you have. References are set in the References dialog, opened from the VBA window. For more information on working with references, see Access Archon #107, Working with References.
Supporting Files
The zip file containing this article, in Word 97-2003 format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site, as accarch248.zip, which is the last entry in the table of Access Archon columns for Access Watch.
| Document Name | Document Type | Place in |
| Filter by Form (AA 248).accdb | Access 2007-2010 database (can also be used in higher versions of Access) | Wherever you want |
