This is a follow up question to a solution that was provided to me from Harfang (copied below for reference).
I have two field listed on the main form F0. These field names are Cust and CustNum. I can easily scroll from record to record by using the record navigation buttons at the bottom of the page. Alternatively, I can use the Find feature to find a specific record. However, I was hoping to create a combo box to further enhance navigation to a specific record on form F0.
My combo box is named cmbLookUp and it has two columns of data: Cust and CustNum. I have the following After Update event associated with the combo box.
CustNum = cmbLookUp.Column.0
Cust = cmbLookUp.Column.1
The combo and the code appear to be working fine. However, I keep getting an error message after 1) selecting a cust/cust number via the combo box, and 2) attempting to click of the tab for F2. The error message reads: The changes you requested to the table were not allowed b/c they would create duplicate values in an index, primary key, or relationship.
One thing I do notice is that the record number (at the bottom of the screen beside the nav arrows) does not change from record #1 even after I select, for example, record number 1000 with the combo box. Is this the root of my problem? If yes, how do I instruct Access to synch up the record number at the bottom of the page with the record that I just selected via the combo box?
Thanks
Let's see if I got this right.
You have a main form F0, displaying table T0. On that form, sub-form F1 displays T1, which in turn has a sub-sub-form F2 bound to table T2.
Instead, you want F0 to have a tabbed control: first tab: sub-form F1 (in a control called subF1); second tab sub-form F2 (control subF2), but F2 should still be linked to the current record in F1. So far so good?
The current link fields between F0 and subF1 can be kept, but the link fields between F1 and subF2 become a problem. You have to smuggle the key outside to the main form. For example, let's say that you had these links for subF2:
Link Child Field: FK1
Link Master Field: ID1
Meaning the primary key of T1 is ID1, used as foreign key in T2 as FK1. With the tabbed form, you are going to use a new text box. Since the form control showing F1 is called subF1, you will use this Control Source:
= subF1.Form.ID1
Now you can see the current ID1 from the sub-form on the main form. Let's call this text box txtID1. Now the link fields for F2 become:
Link Child Field: PK1
Link Master Field: txtID1
What happens now? When you select a record in the main form F0, F1 will synchronize just as it did before. Every time the current record changes, Access will recalculate txtID1 to show the current ID. Since this text box is a master field for subF2, F2 will also be synchronized in turn.
I hope this was clear. If not, feel free to ask.
Summary:
main form F0, table T0
control subF1, form F1, table T1, key field ID1
text box txtID1, control source: = subF1.Form.ID1
control subF2, form F2, table T2, foreign key FK1, master: txtID1, child: FK1
(°v°)
