Unanswered: Selecting Record without adding new one
I haven’t had any luck getting the form I have to work. I thought I might have to go to some sort of cascading combobox, but the more I read up on them the less it looks like they’re the right solution.
I’m trying to get the user to select an item from a table by the ItemName, (he has no knowledge of the autonumbered ItemKeyID for that name). However it is the ItemKeyID that I need to capture from that selection for use in a table that links the item to a person.
In a sub-form I currently have a combobox that’s has its rowsource = SELECT ItemName FROM qItemAlpha, where the table tItem has an autonumber field ItemKeyID. Unfortunately when I make that selection, the tItem gets a new record added, rather than using the existing one and that bogus ItemKeyID is used in the linking table.
My current thought is that I need to change the combo to a textbox, and open an unbound popup form when that textbox is entered. The unbound would have the combobox for selecting the ItemName. When the user selects the ItemName, a procedure would pass the ItemKeyID associated with that name back to the subform, which would then display the associated ItemName, and the ItemKeyID (hidden from the user).
I hope someone can show me how to accomplish this or point me to a code sample. (For anyone interested in the actual mdb, I posted it in a previous thread 7/16/2010)
Thanks for the reply. I tried the same code you suggested. It was slightly different from what I was already trying.
However, it produced the same erroneous result. I even changed the "boundcolumn" to 0
to see if that was the problem - same result. The cbo is set to "limit to list" and yet picking a value from the displayed list results in adding a new entry (and KeyID) to the underlying table, and using that new KeyID for the join table.
<OT>Poppa - have you not found yourself deluged with spam email? You know you can receive emails via the forum without exposing the address to the internets? Not a problem with me BTW, just thought you might have problems.</OT>
I tried to make this 2nd post simpler and caused confusion by over-simplifying the naming of the fields.
I'll upload a new copy of the GLC db. The problem I'm having is when opening the fCounselorUpdate form, I go to the subformMBYr; then I select one of the Badges from the cbo MeritBadgeName. The intent is to pick up the BadgeKeyID for that selection and populate the join table with that BadgeKeyID, the CounselorKeyID, and the Year that is typed into the subform. Rather than picking up that BadgeKeyID, a new entry is being created in the table tMeritBadgeName, and that new BadgeKeyID is being used in the joining table. The table tMeritBadgeName should never have additions made to it during this process. In the uploaded file, I've left in the erroneous entries (BadgeKeyIDs in the 600 range) from my last trial using Sinndho's mods
Hope this explains the problem a little better.
Thanks for your time.
A new version of your database has been uploaded based on the fowwing.
Correct me if I am wrong, you want to populate the table tPersonBadgeCrossLink with the data using the form fCounselorUpdate
to select the Counselor and using the subform to select the bage and entering the year.
Assuming the above is correct I have made the following changes to your form and subform.
1. On the form fCounselorUpdate - added a field to store the Counselor Key Id. Set the visible property of this field to No. This will be used in the subfoirm see below.
2. On the subform changed the record source to the table tPersonBadgeCrossLink.
3. Changed the control source of the dropdown to store the badge name.
4. Added a field to store the Counselor Key ID see point 1
5. Used the After Update event of the badge drop dwon to update store the badge id and counselor id.
I selected a Counselor, then selected the badge, entered the year, clicked on return to main menu. The correct data was stored in the table tPersonBadgeCrossLink
The zip you sent directly to me worked, neither of the ones sent to the forum would open. (¿¿Who knows why?? they were the same).
Anyhow, I got called out of town and didn't get back to this until today. Your analysis was right on. The key was changing the recordsource for the subform adding the CounselorID and AfterUpdate eventproceedure to the cbo. I just spent the last hour testing it with real data and it looks OK.
Thanks again from your time.