Free Resources from FMS

Additional Resources

 

Thank you! Thank you! I just finished reading this document, which was part of a link in the recent Buzz newsletter. I have printed it for others to read, especially those skeptical on the powers of Access and its capabilities.

Darren D.

 

Free Catalog

 

Resyncing a Subform Record in Microsoft Access

Provided by: Luke Chung, FMS President

In a Microsoft Access form with a linked subform, when the master record is updated, the subform resets itself to the first record.

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