Filter by Form with Row Source Queries

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Give it a try. You can unsubscribe at any time.

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

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:

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.