• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

filters on form

So I have a form that I have attached.  The form has mutliple filters,

State
County
City
Speciality.


If the user selects to engage the State Filter, and then wants to further filter the counties they would only like the Counties that are in the state to be offered in the filter pulldown.


So for State-County- City- Specialities
Filters1.accdb
0
seamus9909
Asked:
seamus9909
  • 7
  • 4
  • 2
1 Solution
 
mbizupCommented:
Add the following code to the After Update event of your state combobox:


Me.cmbCounty.Rowsource = "SELECT County FROM Provider WHERE County Is Not Null AND State = '" & Me.cmbState.Column(1) & "'"

Open in new window

0
 
mbizupCommented:
Correction:

    Me.cmbCounty.RowSource = "SELECT County FROM Provider WHERE County Is Not Null AND State = " & Me.cmbState 

Open in new window

0
 
seamus9909Author Commented:
So its gets a little more complicated.

So I select State then County, now I want to see only those Cities in the State and County/
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
mbizupCommented:
Its exactly the same concept.

You would need code in the after update event of the county combo, this time limiting the rowsource of the city combo by State and City.
0
 
mbizupCommented:
It would be something like this (can't open your DB at the moment):

    Me.cmbCity.RowSource = "SELECT City FROM Provider WHERE State = " & Me.cmbState & " AND County = '" & Me.cmbCounty & "'"

Open in new window


Check the field names etc - and I'm using embedded quotes around County since it is text if I recall.
0
 
mbizupCommented:
... etc.

You would extend that further for additional combos as needed.  It is called 'cascading combos'.
0
 
seamus9909Author Commented:
This is seeming to work fine.  i am noticing the behaviour of the combo box is alittle weird.  or instance if I click on the pull down and there are multiple entries I see every one.

I tried using distinctrow in the control source but that seems to not have an effect.
0
 
mbizupCommented:
Just use DISTINCT.
0
 
seamus9909Author Commented:
I tried that and it doesn't work it shows Eveything.  That's why I tried distinct row
0
 
mbizupCommented:
If your SELECT statement only has one field, Distinct will work.   But if you are selecting multiple fields it will be where the *combination* of all selected fields is distinct.   Also make sure the DISTINCT keyword is in the code that sets the rowsource, not just in the property sheet.
0
 
seamus9909Author Commented:
humm still not working,  This appears to work until I enter a state and then when I select the pull down for County its no longer distint. In other words , if I don't select state, and select the pull down for county I don't see every occurance of county. the same is true for the subsquent combo boxes
0
 
Jeffrey CoachmanMIS LiasonCommented:
I agree with the concepts set forth by mbizup.

Some other notes:
1. If you are selecting the State, County and City, then it is best if all three of these entities are in your Stares Table:
I am noticing that "county" is missing in your States table.
So it is hard to add "County" to this discussion, if it does not exist in your table...

2. Some County/City names may repeat.
For example "Johnson county" exists in Kansas, Texas and Iowa...
This goes for the city as well (Brooklyn, NY, ...and Brooklyn Ohio.)
Perhaps not relevant here, ...just mentioning it...

3. I think you have made this more complicated tan it needs to be right now...

You are using a "FilterProviders" function in your code that (tries to) filter for all the criteria at once.
You put this code on all the comboboxes...?
So in effect you are trying to filter for all the criteria, on each selection in each combobox...

Where did you get this code?
Did it ever work in the way you need?
IMHO, code like that should be used only after all the selections have been made...
("Click a button to apply the filter")

Again, this is why I agree with mbizups approach.
...Each selection "Cascades" down to the next combo, when all selections are made, ... ...Then you can apply the filter...

Always "Keep It Simple" at first.

There are dozens of sample database and tutorial around the web to demonstrate this basic technique.
Try one,, ...then make sure it works in the way you want, ...then try adapting it to work in your database.
http://fontstuff.com/access/acctut10.htm
http://www.candace-tripp.net/download/CascadeCombo2007.zip
http://www.candace-tripp.net/download/cascadecombo2k.zip
http://www.candace-tripp.net/download/2table_cascadecombo2k.zip

Here is my basic sample on this type of situation.
It is based on the basic principles descried by mbizip, (Cascade, Select Distinct,...etc) ...so no points please

JeffCoachman
Access-EEQ7845315CascadingCombob.mdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
Again, no points wanted for my post above, all I posted was a sample of all the concepts mbizup stated.
There is nothing "groundbreaking" in my sample.
;-)

Jeff
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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 7
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now