I have been learning Access for a couple months and am now in a pickle.
I work for a company that has multiple vendors in multiple cities/states. A single vendor could have locations in up to a dozen different cities and each city could have twenty or more vendors.
I currently have a form (frmVendorDetails) based on a query (qryVendors) which is based on 4 tables (tblVendors, tblVendorInfo, Cities, & States). tblVendorInfo is what ties the other three together. Each of the other three have only 2 fields, ID and whatever it's titled. There are foreign keys for all three in tblVendorInfo.
On my frmVendorDetails, I have 3 comboboxes (cboVendor, cboCity, cboState).
I need to be able to filter the form by using each of the comboboxes on their own. (I have been able to accomplish this).
Now, I need to be able to filter cboCity if either cboState or cboVendor has a value.
And finally, I need cboVendor to filter if there is a value in cboCity or cboState.
I have tried using cascading comboboxes, but that doesn't allow me to use each combobox individually.
Here's what I'm trying now and when I try to click on cboCity, it's blank after this code runs.
Code:
Private Sub cboVendor_AfterUpdate()
If IsNull(cboVendor.Value) Then Exit Sub
Dim strWhere As String
strWhere = "ID=" & Me.cboVendor
Me.Form.Filter = strWhere
Me.Form.FilterOn = True
Me.cboCity.RowSource = "SELECT Cities.ID, Cities.City, tblVendors.VendorName, tblVendors.ID" & _
"FROM tblVendors INNER JOIN (Cities INNER JOIN tblVendorCities ON Cities.ID = tblVendorCities.City_ID) ON tblVendors.ID = tblVendorCities.Vendors_ID" & _
"WHERE tblVendors.ID = " & Nz(Me.cboVendor) & _
"ORDER BY Cities.City"
cboCity = Null
cboCity.Requery
End Sub
Any ideas would be helpful. Thank you!