Multiple criteria filter help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi! Ihave a form called "VendorSearch" that has comboboxes called
"vendorCounty", (which is linked to a zipcode table and displays county and
state) "vendorSpecialty", (which is linked to a table that has Vendor
Specialty names displayed Like "adjuster".) and a command button that when
clicked sets the filter for a form named "PropVendCounty" to filter the
records displayed. for instance: I select from the combobox VendorCounty the
value "adams county PA" and then from the VendorSpecialtycombo I select
"Adjuster" Then click on the comand button to open the form "propVendCounty:
to display the filtered records. My code only finds the county "Adams" But
does not give me adams county PA, it gives me all the adams counties in the
US. Also...How do I incorporate the criteria from the "VendorSpecialty" combo
? I am Includeing my decrepit code to embarrasss myself and for your
amusment. I really do need help on this one...many thanks in advance!

Private Sub CountyFind_Click()
On Error GoTo Err_CountyFind_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria2 As String
stDocName = "PropVendCounty"
stLinkCriteria = "[VendorCounty]=" & "'" & Me![VendorCounty] & "'"
stLinkCriteria2 = "[VendorCounty]=" & "'" & Me![VendorCounty] & "'"

DoCmd.OpenForm stDocName, , ,

Forms!PropVendCounty.Filter = "[VendorCounty] = '" & _
stLinkCriteria & " AND [VendorCountyState] = '" & stLinkCriteria2 & "'"
Forms!PropVendCounty.FilterOn = True

Exit_CountyFind_Click:
Exit Sub
Err_CountyFind_Click:
MsgBox Err.Description
Resume Exit_CountyFind_Click
End Sub
 
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "PropVendCounty"

stLinkCriteria="[VendorCounty]='" & Me![VendorCounty] & "'"
stLinkCriteria=stLinkCriteria & " AND [VendorSpecialty]='" &
Me![VendorSpecialty] & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Thank you very much for the reply! It does work, but, the combo for county
also has a state , that state also must be included in the criteria. example:
when the dropdown is clicked it shows adams in the county colum and PA in the
stae colum. using your code it only filters the county and the state is
ignored. I really appreciate your help! thanks!
 
stLinkCriteria="[VendorCounty]='" & Me.[VendorCounty].Column(0) &"'"
stLinkCirteria=stLinkCriteria & " AND [VendorState]='" &
Me.[VendorCounty].Column(1) &"'"
stLinkCriteria=stLinkCriteria & " AND [VendorSpecialty]='" &
Me![VendorSpecialty] & "'"

Column(0) is for your first column in your combobox and Column(1) for
the second. You may need to change the numbers if they are not
corresponding with the right fields.
 
Forgive me for my lack of ability...but thank you for your help! So now my
code will look like this since I need both columns:
stLinkCriteria="[VendorCounty]='" & Me.[VendorCounty].Column(0) &"'"
stLinkCirteria=stLinkCriteria & " AND [VendorState]='" &
Me.[VendorCounty].Column(0),(1) &"'"
stLinkCriteria=stLinkCriteria & " AND [VendorSpecialty]='" &
Me![VendorSpecialty] & "'"
 
Ed said:
Forgive me for my lack of ability...but thank you for your help! So now my
code will look like this since I need both columns:
stLinkCriteria="[VendorCounty]='" & Me.[VendorCounty].Column(0) &"'"
stLinkCirteria=stLinkCriteria & " AND [VendorState]='" &
Me.[VendorCounty].Column(0),(1) &"'"
stLinkCriteria=stLinkCriteria & " AND [VendorSpecialty]='" &
Me![VendorSpecialty] & "'"
Click to expand...


There is an extraneous (0), in there. It should be:

Me.[VendorCounty].Column(1) &"'"
 
Back
Top