Relationship/Comb Box Problem

[Guest]

I have three tables that have a 1 to many relationship between them. Table
1’s PK is the FK in table 2. Table 2’s PK is the FK in table 3. So I have a
1 to many from table 1 to table 2 and a 1 to many from table 2 to table 3 and
my relationship window shows it. I then created a 4th table as a lookup
table for the department name in the first table. There are 22 items in this
table and it has a PK. I used the form wizard to create a form with all 3
tables, looks correct, I then changed the department text box (1st table) to
a combo box and in the row source in the Properties window created a query
using the lookup table as the source. When I look at the form it appears to
be correct and the combo box drops down and shows the department names, but
the 2nd and 3rd table information does not change when I select a different
department. My thinking was that this is why you create the PKs and FKs. Is
there help for me, PLEASE!!

Thanks
Wm.

 

[John W. Vinson]

When I look at the form it appears to
be correct and the combo box drops down and shows the department names, but
the 2nd and 3rd table information does not change when I select a different
department. My thinking was that this is why you create the PKs and FKs.

Um?

No, that's not why you create relationships.

Selecting a value from the Department combo box changes the value of the
DepartmentID field in Table1. That's all that it does, and that's all that
it's intended to do. What do you *expect* it to do?

Note also that it is emphatically *not* necessary to create a Lookup field in
order to create a Combo Box; most developers dislike the Lookup Wizard and
avoid using it at all. See
http://www.mvps.org/access/lookupfields.htm
for a critique.


John W. Vinson [MVP]

 

[Guest]

John, Hi. I read that before I started this project. That's why I made the
lookup a table and did not use the lookup wizard in the field. What I want
is, when I change departments, the data in the other 2 tables change,
reflecting the department that I selected. Can you help me?

Thanks
Wm.

 

[John W. Vinson]

John, Hi. I read that before I started this project. That's why I made the
lookup a table and did not use the lookup wizard in the field. What I want
is, when I change departments, the data in the other 2 tables change,
reflecting the department that I selected. Can you help me?

I'm still lost.

Let's say you have 312 records in your table, representing 7 different
departments.

You're looking at one record that happens to be for the Accounting department.

You select a value from the combo box for Research.

What do you want to happen?

Am I misunderstanding the structure of your tables?

John W. Vinson [MVP]

 

[Guest]

John, Hi. I’m thinking I’m looking at a database of music. At the top I
have a drop down box and in it are the names of all the artist, the 2nd
window is all the albums of that artist and the 3rd window is all the songs.
So when I select an artist, the 2nd window populates with all their albums
and the 3rd populates with the songs of the first album. Click on album 2
and the 3rd window populates with that albums songs