?
Solved

filters on form

Posted on 2012-08-28
13
Medium Priority
?
381 Views
Last Modified: 2012-09-12
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
Comment
Question by:seamus9909
  • 7
  • 4
  • 2
13 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38342540
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38342559
Correction:

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

Open in new window

0
 

Author Comment

by:seamus9909
ID: 38342707
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 61

Expert Comment

by:mbizup
ID: 38342738
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38342794
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38342817
... etc.

You would extend that further for additional combos as needed.  It is called 'cascading combos'.
0
 

Author Comment

by:seamus9909
ID: 38342927
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38342951
Just use DISTINCT.
0
 

Author Comment

by:seamus9909
ID: 38343051
I tried that and it doesn't work it shows Eveything.  That's why I tried distinct row
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 38343099
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
 

Author Comment

by:seamus9909
ID: 38343932
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38345969
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38345992
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question