VBA & Macro Problems

Recalculate an Access Parent Form Without Losing Subform Focus

Kushani NimanthikaKushani Nimanthika Nov 20, 2026 868 views

Question details

Recalculate an Access parent form while preserving the current subform record and active control.

Recalculate an Access Parent Form Without Losing Subform Focus
Product
Microsoft Access
Device & OS
not provided
Scenario
A record changes in a subform, and the parent form needs to recalculate.
Observed behavior
Calling Me.Parent.Recalc recalculates the parent form but moves focus to the first subform record.
Before you start

Identify the subform record's unique primary key, the subform control name on the parent form, and the controls whose focus you want to restore. The example below assumes a saved record with a numeric primary key.

Solution 1Recommended

Save the record key and restore the subform control after recalculation

Use this approach when recalculating the parent form moves the subform away from the edited record or control.

The example looks up the saved primary key in the subform's current recordset, then uses the matching bookmark to return to that record. It also saves the active control name before recalculation.

Replace OrderID with your numeric primary-key field and sfrOrders with the name of the subform control on the parent form. The subform control name may differ from the name of the form it contains.

1
Open the subform's event procedure

Open the subform in Design View, open its Property Sheet, and select the Event tab. Use the form's After Update event to run the code after a record is saved, or use a specific control's After Update event if recalculation is needed only for that control.

2
Add the VBA code

Choose [Event Procedure] for the event and click the builder button. Add this code inside the event procedure: Dim savedID As Long Dim savedControl As String Dim rs As DAO.Recordset savedID = Me!OrderID savedControl = Screen.ActiveControl.Name Me.Parent.Recalc Set rs = Me.RecordsetClone rs.FindFirst "[OrderID] = " & savedID If Not rs.NoMatch Then Me.Parent.Controls("sfrOrders").SetFocus Me.Bookmark = rs.Bookmark Me.Controls(savedControl).SetFocus End If rs.Close Set rs = Nothing

3
Test the saved record and focus

Open the parent form in Form View, edit a subform record, and save it or move to another record. Check that the parent recalculates and that the intended subform record and control are restored.

Check the event and recordset: The active control must be a focusable control on the subform, and the saved record must still be present in its current recordset. A text primary key requires a properly quoted and escaped FindFirst criterion instead of the numeric example.
Free Microsoft Office alternative

Work with Office documents in WPS Office

WPS Office is a free, lightweight alternative for everyday document, spreadsheet, and presentation work. Its familiar interface and Microsoft Office format compatibility make it easy to move existing files into your workflow. It does not run or fix Access forms or VBA databases.

Open and edit Word, Excel, and PowerPoint files, including DOCX, XLSX, and PPTXUse a familiar interface for documents, spreadsheets, and presentationsWork with spreadsheet data alongside your Access database workflowUse a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Is there an After Update event for the whole subform record?

Yes. In the subform's Property Sheet, select the form and open the Event tab to find its After Update event. It runs after a changed record is saved; a control's After Update event runs after that control's value is changed and accepted.

How do I find the name of the active control?

In VBA, use Screen.ActiveControl.Name before calling Me.Parent.Recalc. Store the name in a variable and use Me.Controls(savedControl).SetFocus to restore it, provided it is a focusable control on the subform.

Why use the primary key to find the record again?

A unique primary key identifies the record even if its position in the subform changes. Search Me.RecordsetClone with FindFirst, check NoMatch, and assign the matching bookmark to Me.Bookmark.

Do controls have different names on each row of a continuous subform?

No. A continuous or tabular subform repeats the same control for each displayed record. Restore the current record first, then call SetFocus on the control by its name.

What if the saved record cannot be found after recalculation?

Check rs.NoMatch after FindFirst. The record may no longer be in the subform's current recordset because of a filter or other data change; in that case, the example skips restoring the bookmark and control.