I have created two tables: [b

[genericjanedoe]

I have created two tables:

Programme Table
Prog ID Prog Name
1 KS3
2 KS4

Sublists Table
Prog ID Sublist ID Sublist Name
1 1 General
1 2 A2
1 3 A3
2 1 General
2 2 B2
2 3 B3
2 4 B4
2 5 B5

In a separate databse, I created a form with a couple of drop down fields. In one drop down field on the form, I created a list from "Programme ID" data, and asked to show "Programme Name" in the description field. The field that appears in the form now scrolls down to show KS3 and KS4. In a second drop down field, I created a list using "Sublist ID" data, and asked to show "Sublist Name" in the description field. I also asked to define the field contents so that the "Sublists ID" data was displayed where the "Programme ID" value in the Sublists Table matched the "Programme ID" value from the Programmes Table in the current database.

However, I have noticed that when I try to tap in KS4, for instance in to the Programme drop down field, and then go to the Sublist drop down field and select "B2", the minute I tab out of that field, the Sublist field reverts to "A2". Obviously the database is only reading the values from "Sublists ID" and is not referring to the corresponding "Programmes ID" in order to gather the correct data.

Does anyone know how I can get the second field to show the correct data from both tables combined?

 

[paulbent]

You need to join sublists to the data entry table on both Prog ID and Sublist ID

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[genericjanedoe]

Doesn't work unfortunately - I'd already done that. I also tried to join sublists to programmes, and programmes to the data table, but that had no effect either. And of course I cannot join all of them together because that creates a circular join.

Ironically, I think I found the answer whilst preparing my initial question to the forum: just make the Sublist ID numbers unique. It appears to be the only way I can enter the info without the sublist field reverting. So simple I could kick myself!

Thanks anyway