How to Filter Form Records Using an Access Combo Box by Project Manager
Question details
The user needs to filter project records in a Microsoft Access form based on the Project Manager selected from a combo box dropdown.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Creating or modifying an Access form to include a combo box filter in the header for easier record navigation and data sorting.
- Observed behavior
- Records in the form update to display only the data matching the selected project manager from the combo box, or show all records when the combo box is cleared.
Ensure your database tables use a normalized design instead of multi-value fields or value-list lookups, as these make advanced filtering much more difficult.
Configure the Combo Box Row Source and After Update Event
Set up the combo box to pull a distinct list of project managers and use VBA to apply the filter when a selection is made.
Using a distinct SQL query ensures that each project manager only appears once in the dropdown, even if they manage multiple projects. The VBA code applied to the After Update event handles both applying the filter and clearing it when the user empties the selection.
Open the form in Design View. Select your combo box in the form header, open the Property Sheet, and set the Row Source to: SELECT DISTINCT [Project Manager] FROM [Project Log Table] ORDER BY [Project Manager];
Still in the Property Sheet for the combo box, switch to the 'Event' tab. Find the 'After Update' property, click the dropdown arrow to select [Event Procedure], and click the ellipsis (...) to open the VBA editor.
In the VBA editor, write the condition to check for a null value. Use: If IsNull(Me.cboPMSelect) Then set Me.Filter = "" and Me.FilterOn = False. Otherwise, set Me.Filter = "[Project Manager] = """ & Me.cboPMSelect & """" and set Me.FilterOn = True.
Looking for a Free, Lightweight Office Alternative?
While Microsoft Access is a powerful database tool, everyday office tasks like documents, spreadsheets, and presentations can be seamlessly handled by WPS Office. It offers full compatibility with Microsoft Office file formats in a lightweight, free, and easy-to-use suite.
- 1. Download WPS Office: Visit the official WPS Office website and download the free installation package for your operating system.
- 2. Install the Software: Run the installer and follow the on-screen prompts to complete the lightweight installation in minutes.
- 3. Open Your Office Files: Launch WPS Office and easily open, edit, and save your existing Word, Excel, and PowerPoint documents without formatting loss.
Frequently Asked Questions
Why is my Access combo box showing duplicate project manager names?
This happens if you bind the combo box directly to a table without grouping the results. Use the 'SELECT DISTINCT' SQL keyword in the combo box's Row Source property to display only unique names.
How do I clear the form filter to show all records again?
You can clear the filter by deleting the text in the combo box. The VBA code in the After Update event checks if the value is Null and sets 'Me.FilterOn = False', which clears the filter and returns all records.
Why is a normalized table design recommended over multi-value fields for filtering?
Multi-value fields store data in hidden system tables, making it difficult to write standard SQL queries and VBA filters. A normalized table structure ensures standard relationships, simplifying combo box population and record filtering.
Can I use macros instead of VBA to filter the form?
Yes, you can use the 'ApplyFilter' action in an Access Macro assigned to the combo box's After Update event, but VBA provides more robust control, especially for evaluating Null values to clear the active filter.
