Results 1 to 7 of 7
  1. #1
    jarena is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    20

    Combo Box Problem with Partial Primary Key Duplicates

    I created a form that will allow users to view and update records in a customer file. This customer file does not necessitate a customer number; the primary key = last name, first name, phone. I inserted a combo box that allows a user to search/find a customer and then click on the customer to populate the form fields. This works fine except when I have multiple customers with the same last name. For example, I have two customers, Mark Jones and Sarah Jones, and when I choose Sarah Jones, the form fields are populated with Mark Jones' record. I also have 3 Smiths in the file and when I chose the second or third Smith, the first Smith record populates the form. When there are multiple customers with the same last name, no matter which one you choose, the first record in the group with the same last name fills the form fields. Is there a property sheet value I need to change or is there another way to fix this problem?

  2. #2
    Join Date
    Jan 2021
    Location
    Missouri Ozarks, USA
    Posts
    19
    The composite key is making your life difficult. It sounds like your combo box is bound to the last name field. If you had an ID or other index field, even if it's not used as a key, it would make it easier to manage customer names. Note - this is advice from a novice :-)

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Use an autonumber pk and not real data and your problem will go away. You'll also avoid other problems down the road. With your current design, sooner or later you'll run into a situation where a query won't be updatable because of it.

    EDIT - I also meant to ditch the composite key but probably didn't make that clear.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    jarena is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    20
    Thanks for the advice Canyonraven and Micron. I'll try the autonumber PK suggestion when I go into the office Monday; I will let you know how it works out. Micron, thanks again for your help, you and Ajax gave me good advice prior to me starting this position 3 weeks ago.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm thinking you might not be used to autonumbers, so some reading will help
    - http://www.utteraccess.com/wiki/Autonumbers
    - http://access.mvps.org/access/general/gen0025.htm
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    jarena is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    20
    The autonumber primary key solution works - thanks for the advice. I experimented with the AN PK and as long as I keep the AN in the combo box, everything works well, but when I remove the AN from the combo box, the CB stops working. I also removed the PK designation from the AN and changed it back to my composite key (there were some benefits to dong this), and even though the AN is no longer the PK, as long as I leave the AN in the combo box, everything works.

    I do have a follow up question; is there a way to keep the AN in a combo box but not display it?

  7. #7
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Set its column width to 0. Usually you place the autonumber ID first thne use 0";1';1;... for your column widths (and have combo bound to column 1).
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 07-20-2018, 04:05 PM
  2. Replies: 1
    Last Post: 05-05-2013, 12:13 PM
  3. primary key autonumbers problem
    By jinz in forum Access
    Replies: 6
    Last Post: 11-21-2012, 06:08 PM
  4. Replies: 2
    Last Post: 04-05-2012, 03:11 PM
  5. Replies: 3
    Last Post: 05-11-2011, 02:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums