How to create compund filters on a form

in the attached Sample database, I have a form "frmProvider".  I would like to give the user the ability to filter on one or more criteria and then have the form only show the records that meet that criteria.

i created 4 unbound combo boxes and can not figure out a way if the user selects more than one combo box to filter on each criteria selected.


Also on the state filter combo box its not working correctly either.

i have attached the sample database if this helps.
Filters.accdb
seamus9909Asked:
Who is Participating?
 
IrogSintaCommented:
Right now it works if you clear out the selection in a combo box and either press either Tab or Enter.  This of course is inconvenient.  You have a few choices.  
Add another choice to each combo box saying something like ANY COUNTY or ANY SPECIALITY.
Add a clear button  that clears all combo boxes.
Add a small clear button beside each combo box to clear each one individually.
Add a double click event to the label of each combo box that clears it (if you go this route you would need to have a note somewhere on your form instructing the user about this since it's not obvious).
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Create a GO button, and in that button's OnClick event write SQL code that goes something like this:

Private Sub go_click()

Dim sSQL as string, sWhere as string

sSQL = "SELECT blah, blah, blah FROM YourTable"

If Nz(Me.Combo1.Value) <> '' then
   sWhere = sWhere = "column1 = " & Me.Combo1.Value & " AND"
End IF

If Nz(Me.Combo2.Value) <> '' then
   sWhere = sWhere = "column2 = " & Me.Combo2.Value & " AND"
End IF

If Nz(Me.Combo3.Value) <> '' then
   sWhere = sWhere = "column3 = " & Me.Combo3.Value & " AND"
End IF

If Nz(Me.Combo4.Value) <> '' then
   sWhere = sWhere = "column4 = " & Me.Combo4.Value & " AND"
End IF

If Len(sWhere) > 0
  sWhere = "WHERE " & Left(sWhere, Len(sWhere) - 4)
end if

Me.RecordSource = sSQL & sWhere
Me.Requery

End Sub

Enjoy.
Jim
0
 
seamus9909Author Commented:
So here is the Syntax that I applied to my specific form.  I can not compile this.


Private Sub Command46_Click()
Dim sSQL As String, sWhere As String

sSQL = "SELECT * FROM tblProviders"

If Nz(Me.Combo44.Value) <> '' then
   sWhere = sWhere = "column1 = " & Me.Combo44.Value & " AND"
End If

If Nz(Me.Combo40.Value) <> '' then
   sWhere = sWhere = "column2 = " & Me.Combo40.Value & " AND"
End If

If Nz(Me.Combo42.Value) <> '' then
   sWhere = sWhere = "column3 = " & Me.Combo42.Value & " AND"
End If

'If Nz(Me.Combo4.Value) <> '' then
'   sWhere = sWhere = "column4 = " & Me.Combo4.Value & " AND"
'End If

If Len(sWhere) > 0
  sWhere = "WHERE " & Left(sWhere, Len(sWhere) - 4)
End If

Me.RecordSource = sSQL & sWhere
Me.Requery


End Sub
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>sSQL = "SELECT * FROM tblProviders"
add a space before the right double-quote.  My bad.   Should be..

sSQL = "SELECT * FROM tblProviders "

Also, you'll need to delineate what your feeding into the parameters, like this...

'Number - use single quotes
sWhere = sWhere = "column2 = '" & Me.Combo40.Value & "' AND"

'Dates - Use pound signs
sWhere = sWhere = "column2 = #" & Me.Combo40.Value & "# AND"

>'If Nz(Me.Combo4.Value) <> '' then
Better to name your controls to what they represent, such as cbo_customer_number, cbo_invoice_date, cbo_state, etc.  I'm not likely to remember what Combo4 means.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Also for kicks and giggles, until we have this down ...

End If

msgbox sSQL & sWhere   '<--  add this line, and look at it when it runs.

Me.RecordSource = sSQL & sWhere
Me.Requery
0
 
seamus9909Author Commented:
Getting closer

When running the procedure i get a compile error no the If Len(...


Dim sSQL As String, sWhere As String

sSQL = "SELECT * FROM Providers "

If Nz(Me.Cbo_city.Value) <> "" Then
   sWhere = "column1 = '" & Me.Cbo_city.Value & " AND"
End If


If Nz(Me.Cbo_county.Value) <> "" Then
   sWhere = sWhere = "column2 = " & Me.Cbo_county.Value & " AND"
End If

If Nz(Me.Cbo_State.Value) <> "" Then
   sWhere = sWhere = "column3 = "" & Me.Cbo_State.Value & " And ""
End If

'If Nz(Me.Combo4.Value) <> '' then
'   sWhere = sWhere = "column4 = " & Me.Combo4.Value & " AND"
'End If

If Len(sWhere) > 0
  sWhere = "WHERE " & Left(sWhere, Len(sWhere) - 3)
End If

MsgBox sSQL & sWhere   '<--  add this line, and look at it when it runs
Me.RecordSource = sSQL & sWhere
Me.Requery
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>sWhere = sWhere = "column2 = " & Me.Cbo_county.Value & " AND"
I have one two many equals sign in these lines.  My bad.  Change the above lines to

sWhere = sWhere  & "column2 = " & Me.Cbo_county.Value & " AND"
                               ^^^
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Where you have Column1, Colum2, Column3, Column4, change those to the column names in the table that you're trying to query.

>   sWhere = sWhere = "column3 = "" & Me.Cbo_State.Value & " And "
After the AND there should only be the double quote.
The idea is to have a sinqle-qoote around the passed Me.cbo_state.Value.

>sWhere = sWhere = "column2 = " & Me.Cbo_county.Value & " AND"
Keep the space to the right of the AND, as it will cause an error if it runs on to the next block.

>  sWhere = "WHERE " & Left(sWhere, Len(sWhere) - 3)
Change the 3 to a 4, so it removes the final AND and the space.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<sorry for so many iterations here>
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Trust me, once you get the hang of this it'll be easy..
0
 
IrogSintaCommented:
I thought it strange that you weren't using a continuous form to show your filtered choices so I revised your database a bit.  I also made use of the form's filter property when selecting your combo boxes.

One suggestion.  You can use the State abbreviation as the primary key instead of the 2 other fields that you have with numeric IDs.
Filters.accdb
0
 
seamus9909Author Commented:
Thanks IrogSintra


A quick Question, on the combo boxs, if they decide to not utilize one when filtering, is there a way to clear the value in the combo?
0
 
IrogSintaCommented:
Just curious. Which route did you select to clear out a value in a combo box?
0
 
seamus9909Author Commented:
i really havent been able to get this to work correctly.  I added a clear button next to each combo box.

Private Sub Command53_Click()
Me.cmbState.Value = Null
Forms!frmProvider.frmProvider_sf.Requery
End Sub

However this does not requery the entire form, so the form is still displaying the results of the filter!   it does clear the value from the combo box.    Also the form you provided is confusing me, when I tab through the various fields the doesnt scroll horizonally, so you cant see all the fields.  The other thing that confuses me is the layout of the form, I am trying to rearrange the fields , I wanted Salutation to be the first field and I can't figure out how to move the fields around.

Thank you
0
 
IrogSintaCommented:
I'd like to suggest that you take time to give descriptive names to your controls.  This will help you in the long run when you revisit your code.  If you see a routine called btnState_Click, you'd know right away what this procedure pertains to.  Command53_Click, on the other hand, doesn't give much help.
    That aside, here's how your clear process should be:
Me.cmbState.Value = Null
Call FilterProviders

   As for the layout of the form, when you are in design mode, just drag the fields around and put them in the order that you want.  You can drag around a group to select them all and move them as one.
    Let me look into the horizontal scrolling issue.
0
 
IrogSintaCommented:
Okay, so apparently having it on a subform doesn't work too well if the form is wider than the screen when it comes to automatic horizontal scrolling.  So here is your db without the subform.

Also, in moving controls around in the design mode, you can hold down the Ctrl key and click on the controls that you want to move as a group.
Filters--1-.accdb
0
 
seamus9909Author Commented:
That is awesome works fine!   Now I know you are going to kill me.   This form will be used by the user to select a doctor and link it to a claim that is entered in the system.  Is there a way to make a selection of one of the records and then populate on another form?
0
 
IrogSintaCommented:
You should really open up another thread since this question is unrelated to your original post.
0
 
seamus9909Author Commented:
Will do thx for all Ur help
0
 
IrogSintaCommented:
I just noticed an error with the db I uploaded.  You need to make a correction.  Make your combo box cmbState Unbound and delete the form's On Load event.
0
 
IrogSintaCommented:
Here's the corrected version.  Sorry about that.
Filters--1-.accdb
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.