CODEWITHSUNDEEP
mysqlms-accesscomboboxvbasubform
jstacy00
jstacy00

Reputation: 85

Combobox searches subform for all matching records

I borrowed some code from another Access database from one of my colleagues to simply search a table which is bound to a subform by searching from a separate "people" table. I'm a little rusty on the syntax, so I'm unsure how to correct the code to make it work for my program.

So once a person is selected from the combobox, it is supposed to search the "owner" field in the "vehicles" table for all matching records and display them. 

Private Sub Form_Open(Cancel As Integer)
    Combo0.SetFocus
End Sub

Private Sub Combo0_AfterUpdate()

    ' Find the record that matches the control.
    Dim rs As DAO.Recordset

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Owner.vehicles] = '" & Str(Nz(Me![Combo0], 0)) & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

    Forms!frmVehReg!subFrmVehReg.SetFocus

    Forms!frmVehReg!subFrmVehicles.Form!Make.SetFocus


End Sub

Upvotes: 0

Views: 243

Answers (1)

mnieto
mnieto

Reputation: 3874

You can either change the recordsource or the filter of the subform. To filter (I'm supposing that in the vehicles table you have a field Owner of type string):

Forms!frmVehReg!subFrmVehicles.Form.Filter = "Owner = '" & Str(Nz(Me![Combo0], 0)) & "'"

To change the recordsource:

Forms!frmVehReg!subFrmVehicles.Form.RecordSource = "Select * from vehicles where Owner = '" & Str(Nz(Me![Combo0], 0)) & "'"

Upvotes: 2

Related Questions