Filter by Form with Row Source Queries


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).

AW 1713-A

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

1
Private Sub cboLocation_AfterUpdate()

 

1
On Error GoTo ErrorHandler

 

1
<strong><em>   'Save value to public variable for use in report filter query</em></strong>
1
strLocation = Nz(Me![cboLocation].Column(0))
1
pstrLocationFilter = "[ProjectCityState] = " &amp; Chr(39) _
1
&amp; strLocation &amp; Chr(39)
1
Debug.Print "Location filter: " &amp; pstrLocationFilter
1
Me![fraReportType].Value = 2
1
Call CreateFilter

 

1
ErrorHandlerExit:
1
Exit Sub

 

1
ErrorHandler:
1
MsgBox "Error No: " &amp; Err.Number _
1
&amp; " in " &amp; Me.ActiveControl.Name &amp; " procedure; " _
1
&amp; "Description: " &amp; Err.Description
1
Resume ErrorHandlerExit

 

1
End Sub

 

Numeric Value

1
Private Sub cboProjectCode_AfterUpdate()

 

1
On Error GoTo ErrorHandler

 

1
<strong><em>   'Save value to public variable for use in report filter query</em></strong>
1
lngProjectCode = Nz(Me![cboProjectCode].Column(0))
1
pstrProjectCodeFilter = "[ProjectCode] = " &amp; lngProjectCode
1
Debug.Print "Project Code filter: " &amp; pstrProjectCodeFilter
1
Me![fraReportType].Value = 2
1
Call CreateFilter

 

1
ErrorHandlerExit:
1
Exit Sub

 

1
ErrorHandler:
1
MsgBox "Error No: " &amp; Err.Number _
1
&amp; " in " &amp; Me.ActiveControl.Name &amp; " procedure; " _
1
&amp; "Description: " &amp; Err.Description
1
Resume ErrorHandlerExit

 

1
End Sub

 

Date Value

1
Private Sub cboStartDate_AfterUpdate()

 

1
On Error GoTo ErrorHandler

 

1
<strong><em>   'Save value to public variable for use in report filter query</em></strong>
1
pstrStartDateFilter = "[WorkDate] = " &amp; Chr(35) _
1
&amp; Nz(Me![cboStartDate].Value) _
1
&amp; Chr(35)
1
Debug.Print "Start Date filter: " &amp; pstrStartDateFilter
1
Me![fraReportType].Value = 2
1
Call CreateFilter

 

1
ErrorHandlerExit:
1
Exit Sub

 

1
ErrorHandler:
1
MsgBox "Error No: " &amp; Err.Number _
1
&amp; " in " &amp; Me.ActiveControl.Name &amp; " procedure; " _
1
&amp; "Description: " &amp; Err.Description
1
Resume ErrorHandlerExit

 

1
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:

1
Public Sub CreateFilter()

 

1
On Error GoTo ErrorHandler

 

1
Dim lngCount As Long
1
Dim strQuery As String
1
Dim strSQL As String

 

1
pstrFilter = IIf(pstrLocationFilter &lt;&gt; "", pstrLocationFilter _
1
&amp; " And ", "") _
1
&amp; IIf(pstrTechnicianFilter &lt;&gt; "", pstrTechnicianFilter &amp; " And ", "") _
1
&amp; IIf(pstrProjectCodeFilter &lt;&gt; "", pstrProjectCodeFilter &amp; " And ", "") _
1
&amp; IIf(pstrStartDateFilter &lt;&gt; "", pstrStartDateFilter &amp; " And ", "") _
1
&amp; IIf(pstrStatusFilter &lt;&gt; "", pstrStatusFilter, "")
1
Debug.Print "Filter: " &amp; pstrFilter

 

1
If Right(pstrFilter, 5) = " And " Then
1
pstrFilter = Left(pstrFilter, Len(pstrFilter) - 5)
1
Debug.Print "Filter: " &amp; pstrFilter
1
End If

 

1
<strong><em>   'Save filter to a custom property for use in report</em></strong>
1
strPropertyName = "Filter"
1
lngDataType = dbText
1
Call SetProperty(strPropertyName, lngDataType, _
1
pstrFilter)

 

1
strQuery = "qryFilteredWorkSchedule"
1
strSQL = "SELECT * FROM qryWorkSchedule WHERE " &amp; pstrFilter &amp; ";"
1
Debug.Print "SQL for " &amp; strQuery &amp; ": " &amp; strSQL
1
lngCount = CreateAndTestQuery(strQuery, strSQL)
1
Debug.Print "No. of items found: " &amp; lngCount
1
Me![txtFilter].Value = pstrFilter
1
Me![txtNoRecords].Value = lngCount

 

1
ErrorHandlerExit:
1
Exit Sub

 

1
ErrorHandler:
1
MsgBox "Error No: " &amp; Err.Number _
1
&amp; " in " &amp; Me.ActiveControl.Name &amp; " procedure; " _
1
&amp; "Description: " &amp; Err.Description
1
Resume ErrorHandlerExit

 

1
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:

AW 1713-B

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.

AW 1713-C

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:

AW 1713-D

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

Want More?

Office Watch has the latest news and tips about Microsoft Office.  Delivered once a week.