Access 2000 Form design/combo box dilemma

[BrentDA]

Having an issue creating combo boxes on forms. I have the combo boxes made,
and they show the lists (created in tables), but I only want them to show
certain ones from the list.

As an example, I have a table of car makes, and a table of car models for
all the makes. To be simple, let's use the 'Big 3' US automakers:
Daimler-Chrysler, GM and Ford.

I have those three in the car make table. And for the models table I have
the total list of all the models for all 3 makes of vehicles.

Here's what happens now... I created a form with 2 combo boxes, one for Make
and one for Model. I select one of the makes from the first drop-down list,
which is doing what it's supposed to do. Then I go down to the 'Model'
field, and it shows a list of all models for all 3 makes of vehicles.

Here's what I want it to do...When I choose a Make from the first pull-down,
I want it to show on the Model pull-down list only the models for the make I
chose in the first pull-down. How do I do that?

Thanks !

Brent

 

[Tom Wickerath]

Hi Brent,

How to synchronize two combo boxes on a form
http://support.microsoft.com/?id=289670

Basing one combo box on another
http://office.microsoft.com/en-us/access/HA011730581033.aspx

Limit content of combo/list boxes
http://www.mvps.org/access/forms/frm0028.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[mylissamurry]

I read your advice and really appreciate it. I have a situation
similar to BrentDA. Following the links you provided I now have two
combo boxes in my form. ComboBox 1 which is unbound, is used to
filter choices in ComboBox 2 which is bound. It works great; however,
when I navigate from record to record, my choice in ComboBox 1 remains
the same for all records (which in effect messes up what is in
ComboBox 2). In other words, whenever I change an item in ComboBox
1, it is changed for ALL the records. Did I set up ComboBox 1 wrong?

Any help would be appreciated. Thanks so much!

 

[John Spencer]

No, you didn't set it up wrong. It is unbound, so it is NOT going to change
as you move from record to record.

The solution is dependent on whether or not you are using a single form or
continuous forms.

You will need to use some vba in the form's current event to set the value of
the unbound combobox to correspond to the group the bound combobox's value
belongs to and then you may need to force a requery of the bound combobox.

Roughly something like:

Me.Combobox1 = DLookup("Group","SomeTable","SomeItem='" & combobox2 & "'")
Me.Combobox2.Requery

Your other option would be to change the source of Combobox 2 to something
like the following so it will always show the selected value no matter what is
chosen in the unbound combobox.

SELECT SomeField
FROM SomeTable
WHERE SomeGroupField = Forms!YourFormName!Combobox1
OR SomeField = Forms!YourFormName!Combobox2

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County