I have three tables: T0, T1, and T2.
T0, Primary Key = CustomerNumber
T1, Primary Key = LocationNumber
Foreign Key = Customer Number
T2, Primary Key = PartNumber
Foreign Key = LocationNumber
There are One to Many relationships between T1 and T2/T2 and T3.
I have built a form that contains a sub form and a sub-sub form.
F1 is a subform in F0,
F2 is a sub form in F1.
I have a combobox that is used on my main form, F0, to lookup customer numbers form table T0. When a customer number is selected from this combobox, the appropriate record from T0 is retrieved and displayed on form F0. Similarly, the appropriate record(s)s are displayed from table T1 in subform F1; the appropriate record(s) from T2 are displayed in sub-sub form F2.
How do I position a combobox on F0 that will enable me to conduct a lookup function on PartNumber (my primary key in the T2 table)? Intuitively, I think Ill need to create a combobox that contains the following columns of data: CustomerNumber, LocationNumber, and PartNumber. Then, Ill need to write some lookup code that looks up and retrieves these records from the three fields (in a sequence order of CustomerNumber, LocationNumber, and PartNumber). Does this sound accurate/possible?
It seems that I should be able to display the appropriate record in forms F0 (Customer Number), sub F1 (LocationNumber), and sub F2 since
1) the part number is a primary key in T2,
2) there is only one location number associated with this part number in T1, and
3) there is only one customer number associated with this location number in T0