Unanswered: Creating a link table between two tables.
I have a table update problem. The db is plagiarized from a working db, so Iím stumped by the problem Iím having, which is described in the next paragraph. Iíve attached a copy of the db with sensitive data stripped.
The intent is to update the table tPersonBadgeCrossLink with the CounselorID = CounselorKeyID, the BadgeID = BadgeKeyID(not the BadgeName), and authorized Year. tMeritBadgeName table should never be changed in this process, its only for getting the correct Key from the BadgeName. Whatís happening is that an unwanted new entry is being created in the tMeritBadgeName table (i.e. with a new AutoNumber [BadgeKeyID]). And that created entry in tPersonBadgeCrossLink shows the unwanted new [BadgeKeyID] rather than the underlying correct BadgeKeyID, along with the CounselorKeyiD and Year.
I assume there is something wrong with selecting of the MeritBadgeName to determine the KeyID. Or something wrong with the join properties in the query. But after trying any number of combinations of properties and joins, I canít get it to work correctly.
The intent is for the user togo into the fCounselorUpdate form, and when a given Counselor record is displayed the user goes to the subformMBYr and selects a MeritBadgeName from the combobox, types an authorized Year in the second field and then repeats until all the personís Badges are entered. If you donít pay attention to whatís really happening to the underlying tables, it appears to work, but really doesnít.
Reports (that are still being designed) wonít work with the extraneous data in the MeritBadgeName table.
I've just done a study of the sequel version of the cross-link query, comparing it to the sequel version of the link query in the db that it was plagiarized from and they are identical. So my feeling is that the problem is in how the subform is selecting the underlying record.
I'll do more study on that. Anyone else ???