Update an Access Subform When the Main Form Record Changes
Question details
Update an Access subform's source when the user navigates to a different main form record.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A main form uses a combo box value to select the SourceObject displayed in a subform control.
- Observed behavior
- The combo box and other controls update as records change, but the subform's SourceObject does not refresh.
Open the main form in Design View and identify the combo box, subform control, and existing procedure or macro that changes the subform's SourceObject. Confirm that each combo box value maps to the intended source form.
Run the source-changing procedure from the main form's Current event
Use the main form's Current event to update the subform whenever Access displays a different record.
In Access, open the main form in Design View. Select the form using the form selector, open the Property Sheet, and choose the Event tab.
Set On Current to [Event Procedure], then click the ellipsis button to open the VBA editor. If a Current procedure already exists, add the call to that procedure rather than creating a second one.
Inside Form_Current, add a call such as cboSourceObject_AfterUpdate, replacing cboSourceObject with the actual combo box name. The procedure should be in the main form's module for this direct call.
In the combo box's AfterUpdate procedure, replace Me.ActiveControl in the Select Case expression with Me.cboSourceObject.Value, using the actual control name. Keep the existing cases that assign the subform control's SourceObject.
Save the form, open it in Form View, and use the record navigation buttons to move between records with different combo box values. Confirm that the subform displays the source associated with each value.
Use WPS Office for compatible documents and spreadsheets
WPS Office is a free, lightweight alternative with a familiar interface for working with Office documents, spreadsheets, and presentations. It supports a smooth transition for those files, but it does not replace Access or fix Access form events.
Frequently Asked Questions
Why doesn't the combo box's After Update event run when I navigate records?
Navigating to a record displays its stored combo box value but does not necessarily trigger After Update. Use the main form's On Current event to run the source-changing logic when the displayed record changes.
Why should I replace Me.ActiveControl in the procedure?
The combo box may not have focus when Form_Current runs. Refer to it explicitly, such as Me.cboSourceObject.Value, so the Select Case statement reads the intended control.
What if the combo box uses an After Update macro?
Run the same named macro from the main form's On Current event. If the logic is in an embedded macro, move or copy it to a named macro that both events can run.
Does WPS Office fix Access subform events?
No. The Current event and SourceObject settings must be changed in Microsoft Access. WPS Office can be used separately for compatible documents and spreadsheets.
