Filtering Contents of a Drop Down List Based on Another Field

[simulationguy]

This is causing my some grief :)

I have a form that displays records in a tabular format, one field is a combo box that displays values from another table. But I only want some of those values to be displayed based on another field on my form. So as I go to the next record I want the values in the combo box that I select to change.

I did a query based on the field value and then in the combo box entry event I did a requery and it worked great for restricting my fields but it hosed all the other records.

So lets say record 1 Area=1 I want values A,B,C and record 2 Area=2 I want values D,E,F. I go to record 1, click the combo box and A,B,C show up in my list I select "A"

Then when I went to record 2, click on the combo box, D,E,F show up fine but when I select D, the field in record 1 goes blank because its value is no longer in my list.

I think I'm almost there just missing one detail.

[slenish]

Hi Simulation guy,

I think I have been messing with something very simular to what you are having problems with.

I can try to help you out if you give me a little more information. Remember im going to try. Im pretty new to this but your problem sounds almost exactly like mine was which took me a week to figure out.

If you can give me just a little more info I might be able to narrow it down. First how many combo boxes do you have? Are you trying to select A,B,C in one combo box and then in another combo box have something else pop up? So for example if I select A in combo1 then in combo2 Apple pops up? The information you are trying to have pop up is it already in just one table or is the information in many tables?

I'll do my best to help

[simulationguy]

I appreciate any help. Hopefully I can explain it okay.

There are two combo boxes (two fields in the same record) , one table. One field in the table is a location and the combo box lets you select that location from another table. The second field is an area and the combo box lets you select the area from another table. But based on the selection of the Location, I want to
limit what areas you can pick. The table with the areas also has a field for which locations they belong too.
I set up a query that worked select .... WHERE tblArea.Locatio nID=tblEquipmen t.LocationID and did a requery when I click on the combo box for the Areas. Everything worked except it wiped out that field for the previous records.

I think I need to somehow make my second combo box unbounded and transfer the selection to my field in the table. Right now its bounded to that field.

[slenish]

Ok I think I am starting to understand what you want to do.

check out this link and see if its what you are trying to do. If it is and you need more help let me know. If its not I will try to find another solution

http://www.databasedev.co.uk/filter_combo_boxes.html

[simulationguy]

Thanks! That is exactly what I am trying to do

[slenish]

Your welcome glad I could help out :D

[simulationguy]

Crap, it didn't quite work as expected. The combo box works great but the problem is when it filters the records in the combo box then it also makes the selection in the other records go blank. I think its close just missing some little trick.

[simulationguy]

Here is a link to a page that describes the approach for a multirecord form if you ever need it. Its a hack but it seems to work

http://www.blueclaw-db.com/download/continuous_dependent_combo_box.htm