How to Filter an Access Subform and Datasheet from a List Box
Question details
The user needs to filter a notes form based on a selected record ID and dynamically update a list box based on an option group selection.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Designing an Access database form where users can select records from a list box or option group to dynamically filter subforms, popup forms, or update data sources.
- Observed behavior
- Looking for the proper VBA methods and form design techniques to open a filtered notes form for the current record and correctly requery a list box's row source when criteria change.
Ensure you have enabled macros and VBA execution in your Access Trust Center settings, and create a backup of your database before modifying form events or SQL statements.
Filter and Open a Form using VBA
Use the DoCmd.OpenForm method in the After Update event to open and filter a notes form for the specific current record.
By utilizing VBA in the After Update event of a form control, you can instantly launch a secondary popup or subform filtered specifically to the selected record (such as a LoanID). Continuous forms are often recommended over Datasheet views for this task because they provide better control over header elements and data entry layout.
Right-click your main form in the Access navigation pane and select Design View. If you are using a continuous form, ensure your list box or combo box is placed in the Form Header.
Select the list box or combo box. Open the Property Sheet (F4), navigate to the Event tab, click the On After Update property, and click the ellipsis (...) to open the Code Builder.
Enter the following VBA code to open the form filtered by the current ID: DoCmd.OpenForm "popupformname", , , "LoanID = " & Me.LoanID. Replace 'popupformname' and 'LoanID' with your actual form and field names.
Within the same VBA block, you can also pass the selected ID as the default value for new records to streamline data entry.
Update a List Box Using an Option Group
Dynamically change a list box's displayed data by building an SQL statement based on the selected option group value and requerying the control.
Enhance Your Daily Productivity with WPS Office
While WPS Office does not feature a direct relational database tool like Microsoft Access, it serves as a powerful, lightweight, and completely free alternative for all your document, spreadsheet, and presentation needs. Experience familiar interfaces and unmatched compatibility without the heavy subscription fees.
Frequently Asked Questions
Why should I use a continuous form instead of a datasheet view in Access?
Continuous forms provide significantly more design control than datasheet views. They allow you to add custom buttons, place combo boxes in headers or footers, and apply specific formatting that standard datasheets restrict.
How do I set a default value for new records based on a list box selection?
You can assign the value in the After Update event of your list box using VBA. For example, setting Me.YourControl.DefaultValue = """" & Me.ListBoxName & """" ensures the next new record automatically inherits the selected ID.
What does requery mean in Microsoft Access VBA?
Requerying a control (such as a list box or combo box) using the .Requery method forces Access to re-evaluate the data source (RowSource) and fetch the most up-to-date records from the underlying table or updated SQL string.
