Filtering data using a combo box

[Gerimc]

In my database I have a form called Population and from here I have a combo box contain different years. I want to be able to select a year from the combo box and it will filter the populations for different places into text boxes on the form. How would I do this ?

 

[PaulF]

depends on how your tables are set up. Please provide more info.

PaulF

 

[Gerimc]

I have a country table - contain country id field, description, contact etc...
I have a yearly rates table which contains country id, yearid, various figures for the country and population field is stored there.
I also have a year table which holds yearid and year desciption.
I want to select a particular year and this will allow me to show all the countries for that year and their populations

 

[Hasu]

Hey, I just wrote FAQ on this topic, please refer it.
faq702-3924



Hope this helps...
Hasu
(Trust on someone, someone will trust you!)

 

[WildHare]

Assuming that your form is populated via a query that joins the Year table and the Country table, it's very simple to set up a filtering mechanism. On your combo box's AFTER UPDATE event, set a filter on the YEAR to the choice made:

Sub MyCombo_AfterUpdate()
me.filter = "MyYearGuy = " & Me!MyCombo
me.filterOn = True
me.Requery
End sub

Note that it appears that you are not using your YEAR guy as a real "date" datatype, so I'm assuming it's just a four digit number, not an actual date datatype.

I have a sample on my website (see below) showing how to filter and sort a form this way, and also an example of how combos can filter other combos.

Jim




Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[Gerimc]

Jim,
I entered the code as you had shown but on my form I can see my list of options for the combo boxe but it won't let me select one. Do you know why this would be ?
Also do I type "MyYearGuy" Or do I put data of my own in here?

 

[amcodah]

Check to see that you have enabled the option of editing data in the form to "yes". I made the same mistake.

--Answer to first question, another solution---
Create a query with the requirement in yearfield yearid = frm_Yourformname!Comboboxname

Then alter the form to use that query as controlsource. Add following code to the comobox:

Sub Comboboxname_AfterUpdate()
Me.Requery
End Sub

That works perfect here, hope it helps.
~am()

 

[Gerimc]

Thanks for the tip about allow edits that worked!
How would I alter my form to allow the query to be a control source?
Also in my qurey would I put the code under yearid or year description. My combo has these two fields with the description being shown.

 

[amcodah]

Just change your control source on you'r form to point to a query/view. (propeteries of the form)

It depends on what you have in you'r combobox. I use year to navigate by so I have the following choices in my combobox.

[2000]
[2001]
[2002]
[2003]

And I've set the default value of it to be [2003] by using Year(Date()).

So what I do in my query is to set year = frm_Form!YearCombobox

As for you.. I think you would like to navigate by yearid, since it's the natural key I assume?

Just create a combobox with both fields in [yearid, year description] and then hide yearid by setting width to 0 in that combobox. This way you would still get hold of it from the query/view, but users would not see it. Only see the year description.

I'm going away for the rest of the day, but I can answer any other questions tomorrow. Anyway, hope this helps.

~am()