Linking Combo Boxes to a List Box (1 Viewer)

[Knight]

Please let me begin by thanking any replies. While I'm sure that this is a frequent topic, I was unable to find the answer to my question in any of the past messages in any of the forums. Be assured that I have tried to do so for quite the length of time.

------------------------------------------------------------------------------
This is what I want to do:

I want to apply a filter [Based off of a Query] to a list box from a selection made in one of several combo boxes. The categories within each combo box were entered by me, and they do not display data from any other queries.

There are five combo boxes, I want the most recent selection made in one of them to be the filter that is applied.
-------------------------------------------------------------------------------

Once again I apologize if this is a frequent question. I was not able to find my answer elsewhere. Thank you very much for you help in resolving this problem.

 

[Jack Cowley]

This article should get you started, but if you have a query in which you can select on or more combo boxes as criteria for the query then I suggest you read this article.

hth,
Jack

 

[Knight]

Thanks

Thanks, That clears it up a bit!

 

[Jack Cowley]

You are welcome... Good luck with your project.

Jack

 

[Knight]

Additional Help with Filtering List Boxes from Combo Boxes

I looked over that site from Microsoft on synchronizing combo boxes. Unfortunately, I'm still in the dark. After following the intructions and adding the appropriate things within the Northwind sample, I was given an error message looking for parameters for the combo boxes.


While I understand a little better now, if anyone knows how I can apply a filter to a list box, based on the selection from a combo box, in which I've added the values myself, I would me much obliged.

Thank-you very much for everyone's time and previous help!

 

[Jack Cowley]

Instead of using a Value List for you list box create a table and store the data there and use the table as the Row Source for the list box. Now you can filter the data, add new values, etc. much easier than using a Value List. I seldom, if ever, use a value list as it is to difficult to manipulate the data.

hth,
Jack

 

[Knight]

I'm sorry to keep using up everyone's time, Jack's especially. You've been a big help, thanks.

I realized after reading Mr. Jack Cowley's response that I had worded my last post incorrectly. What I meant was this:

If there is anyway to apply a filter to a list box by choosing the filter from a combo box, I would greatly appreciate it.


Once again, and as always, Thank you very much for your help in advance.

 

[Jack Cowley]

The answer to your question is yes. If your list box is based on a query then you can use the selection in the combo box as criteria for the query that is the Row Source for the list box.

If, for example, you have a table of people and their ages you would create a query based on this table and you would use code like this:

[Forms]![FormName]![ComboBoxName]

in the Age column's criteria line then when you select 23 from the combo box the list box will list all the people who are 23 years old.

In the After Update event of the combo box you would want code like this:

Me.ListBoxName.Requery

If you follow this example then you can filter your list box, but it should be based on a query as I stated above....

Hang in there, you will solve this problem!

Jack

 

[Knight]

I'm getting closer...where do I put the string of code:
[Forms]![FormName]![ComboBoxName]

Do I put it in the SQL code of the query? And also, working from your above example, do I create a query that has all the ages in it and then use that for my choices? or do I create individual queries for each age group?


Thanks for your help, I'm finally starting to get the hang of this.

Happy New Years!

 

[Knight]

Jack, I was able to figure it all out, and I can now successfully sort my list box as I wanted. I cannot thank you enough for all of your help.

----------------------------------------------------------------
These should be my final questions...
I am working with several combo boxes, each displaying its own set of filters for the list box.


How can I have all the other combo boxes return to a "null" value when I make a selection from a different combo box.

-----------------------------------------------------------------

If I leave a blank space in the combo box, (have it display a field from the source query that doesn't have any data, and thus applies no filter), Then what would the code be so that when I make a selection from another combo box, all the others return/go to this "null value"/"Empty Space"


Thanks Jack, I should be able to stop pestering you after this


Thank you very much for your wonderful help, and thanks in advance to anyone else able to help.

 

[Jack Cowley]

In the After Update event of each combo box use code like this:

If Not IsNull(Combo0) Then
Me.Combo1 = Null
Me.Combo2 = Null
End if

Change the names above to the correct name of the combo boxes and you should be good to go...

Good luck!

Jack

 

[Knight]

I think I've finally finished this thing off, Your help has been invaluable, Jack. I can't thank you enough.

Have a Wonderful New Year!

 

[Jack Cowley]

I am glad I was able to assist you! Good luck with your project....

Jack