Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2006
    Posts
    126

    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)
    PGT

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The combo should have the following properties:
    Rowsource: SELECT ItemKeyID, ItemName FROM qItemAlpha;
    Column count: 2
    Columns width: 0;1 inch
    Bound column: 1

    Now you can capture the Id (ItemKeyID) corrsponding the the user's selection:
    Code:
    Private Sub Combo_AfterUpdate()
    
        Id = Me.Combo.Value
    
        ' Do whatever you want with the selected Id.
    
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Aug 2006
    Posts
    126
    Sinndho,
    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.
    Pete
    PGT

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Pete

    Would you please post your latest version as the GLCProblem.zip for 16/7/2010 does not have qItemAlpha? Also what form should I be using?

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    <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>
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Pootle

    No I just get the odd one or two a day, not a deluge.

  7. #7
    Join Date
    Aug 2006
    Posts
    126
    Poppa Smurf
    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.
    Pete T.
    Attached Files Attached Files
    PGT

  8. #8
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Pete

    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
    Attached Files Attached Files

  9. #9
    Join Date
    Aug 2006
    Posts
    126
    Poppa,
    Just tried to open the zip and it seems that not all of it uploaded. Could you try to upload it again?
    thnx
    Pete
    PGT

  10. #10
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Pete

    An uploaded copy of the database that I sent to your other email address.
    Attached Files Attached Files

  11. #11
    Join Date
    Aug 2006
    Posts
    126
    Poppa,
    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.
    Pete
    PGT

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •