How do I create multiple cascading Combo Box based on "Find a record on my form based on the value I selected in my combo box" in MS access?

Answer 1

Each cascading Combo Box can be tied to a particular field in the record source of a form. To find a record based on a value selected from Combo Box, I would create AfterUpdate Event and add the following VBA code to it.

Private Sub Combo33_AfterUpdate()

Dim rst As DAO.Recordset

Dim strCriteria As String

 

strCriteria = "[Employee Number]='" & Me.Combo33.Value & "'"

Set rst = Me.RecordsetClone

 rst.FindFirst strCriteria

 If rst.NoMatch Then

   MsgBox "No entry found.", vbInformation

 Else

   Me.Bookmark = rst.Bookmark

 End If

 Set rst = Nothing

End Sub

In this code, we search by Employee Number and apply filter to the clone recordset of active Form. If it is found, it will set the bookmark of active Form to the dynamic recordset  (clone).