Resyncing a Subform Record in Microsoft Access

Provided by: Luke Chung, FMS President

It is preferable to keep the record pointer at the same subform record. The solution is very simple. Get the ID identifying the row that should be the current one.  Then use that ID value to search on the subform's RecordsetClone and use the bookmark property to resync your subform record.

Sub ResyncSubformRecord(strSubformName As String, strFieldName As String, lngID as Long)
  ' Comments: Update a subform to make a specific record the current one
  ' In      : strSubformName      Name of subform with data to resync
  '           strFieldName        Field to specify the search criteria
  '           lngID               ID value in the field to find (usually the primary key value) 

  Dim rst As DAO.Recordset

  Set rst = Me(strSubformName).RecordsetClone
    rst.FindFirst "[" & strFieldName & "] = " & lngID
    If Not rst.NoMatch Then
      Me(strSubformName).Bookmark = rst.Bookmark
    End If
  rst.Close

End Sub

Return to the tips page