Access - How to display data on a subform from multiple combobox

Question

I have a form that I have 3 different combobox's that I want to be able dynamically filter on. The ComboBox's are called cboVendor, cboRegion, cboPosition. When I filter on the ComboBox's individually it works fine, but when I try to filter on Vendor then Region then Position, the subform updates the data on only on the last combobox dropdown I select instead of limiting the data to only the previously selected combobox.

I was able to get each combobox to dynamically update depending on the the other boxes, I just cant get the subform to display the data this way.

Below are the individual subs i have for each combobox:

Private Sub cboVendor_AfterUpdate()
Dim myVendor As String
myVendor = "Select * from TblVendor where ([Vendor] = '" & Me.cboVendor & "')"
Me.tbl_Vendor_subform1.Form.RecordSource = myVendor
Me.tbl_Vendor_subform1.Form.Requery
Me.cboRegion = Null
Me.cboPosition = Null
Me.cboRegion.Requery

End Sub

Private Sub cboRegion_AfterUpdate()
Dim myRegion As String
myRegion = "Select * from TblVendor where ([Region] = '" & Me.cboRegion & "')"
Me.tbl_Vendor_subform1.Form.RecordSource = myRegion
Me.cboPosition = Null
Me.cboPosition.Requery

End Sub



Private Sub cboPosition_AfterUpdate()
Dim myPosition As String
myPosition = "Select * from TblVendor where ([Position] = '" & Me.cboPosition & "')"
Me.tbl_Vendor_subform1.Form.RecordSource = myPosition

End Sub

Harun24HR Harun24HR · Accepted Answer · 2016-02-10T06:56:07

Try using this command.

strDataSource = "Select * from TblVendor where ([Vendor] = '" & Me.cboVendor & "' AND [Region] = '" & _
            Me.cboRegion & "' AND [Position] ='" & Me.cboPosition & "')"

Me.tbl_Vendor_subform1.Form.RecordSource = strDataSource
Me.tbl_Vendor_subform1.Form.Requery

Access - How to display data on a subform from multiple combobox

1 Answers