limit the list of a combo box based on the result of another

[steve goodrich]

Could someone please help me with this, I know the questions been asked
before. I've tried to follow tutorials on the internet, but I just can't
seem to crack it.
So I can get the concept of how it works, would someone be kind enough to
walk me through it step by step like I was a 5 year old!!

If I had a database for staff: name, dept etc and 2 of the fields were for
vehicles
make
model
when i select a vehicle in the make field then only the models relevant to
that make would show up in the model field

This is what I tried

I made 3 tables - "Staff" "Make" and "model"
The staff table has several fields eg name, dept,ext etc and the make and
model field
I used the lookup wizard for make and model on the staff table to populate
the combo boxes.
obviously when you click on the combo box you see all the entries in the
tables

I have only one field in both make and model tables
Do i only need one table with 2 fields?

make model
jeep wrangler
ford mondeo
ford fiesta

I really have no clue as to how to link the two together. How do I tell it
that the wrangler is made by jeep etc

Any help would be most appreciated

Steve

 

[Don]

Steve, you need to have a MakeID field in the models table linking back to
the Makes table. eg:
tblModels
ModelID ModelName MakeID
1 Wrangler Jeep
2 Modeo Ford
3 Fiesta Ford

tblMakes
MakeID Make
1 Ford
2 Jeep
3 Chevrolet


then your combobox sql would look something like this:
SELECT tblModels.ModelID, tblModels.ModelName, tblMakes.Make
FROM tblModels INNER JOIN tblMakes ON tblModels.MakeID = tblMakes.MakeID
ORDER BY tblModels.ModelName;

This will display Models and makes in a combobox (or reverse the columns for
Make and Models).
However, I'm not sure what your Staff table has to do with the other
tables.....?
More info.

 

[Al Campagna]

Steve,
On my website below, I have a sample file (A97 or A2003) called Syched Combos, that
demonstrates how to do that.
It's very simple...
(use the value of combo1 to filter the results of Combo2)
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[steve goodrich]

Don
Thanks for you reply. It probably makes perfect sense to someone who knows
access, but not to a five year old. you might as well have replied in
Chinese. I think I got the model id bit then you just totally lost me with

SELECT tblModels.ModelID, tblModels.ModelName, tblMakes.Make
FROM tblModels INNER JOIN tblMakes ON tblModels.MakeID = tblMakes.MakeID
ORDER BY tblModels.ModelName;

I maybe didn't explain it to well

Could I try to phrase it a different way.

Forget about the tables for a minute, Could you tell me how to achieve my
end result, which is

one form
fields would be
first name
surname
dept
ext no
make (drop down box)
model (drop down box)
when i select a make in the make box, all i want to see in the model box is
models that match that make
how many tables would i need

At the moment, the way i tried it is, i have 3 tables staff make and model,
not linked
I just use the make and model tables to populate the drop down boxes on my
staff table
hopes this makes sense
Steve

 

[Shannon Morgan]

Al - I have the same question, so I was glad to see your reply. But the file
I downloaded from your site shows only the form. I (and Steve) need to know
how to code our property sheets to get that 2nd combo box to synch to the 1st
combo box.

Can you help us further?

Shannon

 

[Al Campagna]

Shannon,
I don't understand...
The form has 2 synched combos on it. Combo1 filters the results of Combo2.
If you go into design mode for the form, you'll see all the combo box properties, the
RowSource query behind combo2 that show how combo2 is filtered by combo1, etc...etc...
All that you need to set up your own synched combos on your own form.
There is no "code" except a requery after the combo1 update. It's just a matter of
copying the combo box setup, and understanding the very basic queries behind combo1 and
combo2.
Just translate the setup for your own table/s, form, fields, and comboboxes.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."