How to Filter and Refresh Access List Boxes by Date
Question details
The user needs a method to dynamically filter two list boxes on a Microsoft Access form so that they only show records matching a specific date entered into a form control, and refresh automatically when the date changes.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Displaying specific daily records in form list boxes by filtering database queries based on user-input dates.
- Observed behavior
- The list boxes must automatically update their displayed rows to match the newly entered date whenever the Event Date field is modified.
Ensure you have your Microsoft Access database opened in Design View, and note the exact names of your Event Date text box control and the list boxes you want to filter.
Update RowSource Queries and Add VBA Requery Code
Modify the list boxes' queries to use the date control as a parameter, and trigger a requery using the AfterUpdate event.
To properly link the list boxes to the date control, you must define the form's control as a parameter within the RowSource query of each list box. After linking them, Microsoft Access needs a VBA command to refresh the data whenever the date changes.
Open your form in Design View, select your first list box, and open the Property Sheet. Go to the Data tab, find the 'Row Source' property, and click the ellipsis (...) to open the Query Builder.
In the Query Builder, locate your date column. In the Criteria row, enter: `Forms![YourFormName]![Event Date] OR Forms![YourFormName]![Event Date] IS NULL`. Save and close the Query Builder, then repeat this for the second list box.
Select your 'Event Date' control on the form. In the Property Sheet, go to the Event tab, select '[Event Procedure]' for the 'After Update' property, and click the ellipsis (...) to open the VBA editor.
Inside the VBA editor for the AfterUpdate event, type `Me.lst_PeopleCountsByDay.Requery` and `Me.lst_OutOfService.Requery` on separate lines. Save your VBA code.
To ensure the list boxes also filter correctly when you navigate between different records, select the Form itself, go to its 'On Current' event, choose '[Event Procedure]', and add the same two Requery lines in the VBA editor.
Try WPS Office for your Document and Spreadsheet Needs
While Microsoft Access handles specialized database management, WPS Office provides a powerful, free, and lightweight alternative for your daily document processing, spreadsheet analysis, and presentations. Enjoy a familiar interface and seamless migration without the heavy subscription fees of Microsoft Office.
Frequently Asked Questions
Why aren't my list boxes updating after I change the date?
This usually happens if the `Requery` command is missing or incorrectly placed. Ensure you have added the VBA code `Me.YourListBoxName.Requery` specifically in the `AfterUpdate` event of your Event Date control.
What does 'IS NULL' do in the criteria expression?
Adding `OR Forms![FormName]![Event Date] IS NULL` ensures that if the date control is left blank, the list box will display all records instead of returning an empty list.
Can I filter a list box using multiple form controls?
Yes, you can add additional parameters to your RowSource query by referencing other controls (e.g., `Forms![FormName]![OtherControl]`) and combining them using `AND` or `OR` operators in the Query Builder.
