How to Change an Access Subform Source Object Using a Macro
Question details
The user wants to dynamically change the source object of a subform in Microsoft Access based on a combo box selection.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Building a dynamic form where the displayed subform changes according to user input from a drop-down list.
- Observed behavior
- Needs to execute a statement in the combo box's After Update event to swap the active subform source.
Ensure that the subforms you intend to use as source objects already exist in your Access database, and take note of the exact names of your combo box and subform controls.
Use VBA in the Combo Box After Update Event
The most efficient way to change the subform source object is by assigning the combo box value to the subform's SourceObject property using VBA.
By utilizing the After Update event of a combo box, Microsoft Access can instantly evaluate the user's selection and trigger a VBA statement. This allows your main form to remain static while the embedded subform container switches between different forms or views.
Right-click your main form in the Navigation Pane and select 'Design View'.
Select the combo box (e.g., 'OrderType'), open the Property Sheet by pressing F4, and navigate to the 'Event' tab.
Click the ellipsis (...) next to the 'After Update' property, choose 'Code Builder', and enter the following one-line statement: Me.Orders.Form.SourceObject = Me.OrderType
Make sure to replace 'Orders' with your actual subform control name and 'OrderType' with the actual combo box name matching your database design.
Looking for a Free, Lightweight Microsoft Office Alternative?
While Microsoft Access handles complex relational databases, most everyday data tracking, reporting, and documentation can be managed seamlessly with WPS Office. WPS Office is a powerful, free alternative to Microsoft Office that includes Writer, Spreadsheet, and Presentation.
- 1. Download WPS Office: Visit the official WPS website and click the free download button.
- 2. Install the Suite: Run the downloaded installer and follow the quick setup wizard.
- 3. Start Managing Data: Open WPS Spreadsheet to seamlessly organize, track, and analyze your tabular data as a lightweight alternative to complex databases.
Frequently Asked Questions
Why am I getting a 'Control can't be found' error in Access?
This error usually occurs when the names used in your VBA code (like 'Orders' or 'OrderType') do not match the exact control names on your form. Double-check the 'Name' property in the Property Sheet for both the combo box and the subform container.
Can I change the subform source object using an Access Macro instead of VBA?
Yes, you can use the 'SetValue' macro action in a standalone Access Macro. Set the Item argument to [Forms]![MainFormName]![SubformControlName].[SourceObject] and the Expression to the name of the new form enclosed in quotes.
Does changing the subform source object affect the underlying tables?
No, dynamically changing a subform's source object only alters which form or query is displayed in the user interface. It does not delete or modify the structure of your underlying database tables.
