Using Bookmarks To Position Records In A Form To Match Combo Box Selection
In some form applications, a user will select a desired record to display in a form using a drop-down combo box. The trick is to now bring up that record in the form. This process can be accomplished using bookmarks and a cloned recordset. Here's an example:
Program Code
Private Sub cmbClientSelected_AfterUpdate()
Dim rst As DAO.Recordset
Dim lngClientContactID As Long
Dim bkmBookmark As Variant
' *****************************************************************
' Exit If The User Deleted the Content of the Combo Box
' *****************************************************************
If IsNull(Me.cmbClientSelected) Or Me.cmbClientSelected = "" Then
Exit Sub
End If
On Error GoTo Bookmark_Err
' *********************************************************************
' Using The Form's RecordsetClone, Locate The Record Selected
' From the Combo Box
' *********************************************************************
lngClientContactID = CLng(Me.cmbClientSelected)
Set rst = Me.RecordsetClone
rst.FindFirst "CCClientContactID = " & lngClientContactID
If rst.NoMatch Then
MsgBox "Record not found"
rst.Close
Set rst = Nothing
Exit Sub
End If
bkmBookmark = rst.Bookmark
' *********************************************************************
' Position the form record to the Selected Value
' *********************************************************************
Me.Bookmark = bkmBookmark
' *********************************************************************
' Close The Recordset Clone
' *********************************************************************
rst.Close
Set rst = Nothing
Bookmark_Exit:
Exit Sub
Bookmark_Err:
MsgBox Error$
Resume Bookmark_Exit
End Sub