Results 1 to 7 of 7
  1. #1
    Join Date
    May 2014
    Location
    Columbia, SC
    Posts
    5

    Smile Unanswered: Combo box returning wrong results in Access 2010

    I have an unbound combo box that I set up in a form using the combo box wizard in Access 2010. I selected the option that says, “Find a record on my form based on the value I selected in my combo box.” The problem is that the combo box will not always display the correct record. It seems to only display the first record of a group of similarly named records in a table. I have found similar posts, but no solution.

    Example:
    Code:
        Last Name  First Name   SSN
        Smith      Alex         123-45-6789
        Smith      Jane         234-56-7891
        Smith      Mary         345-67-8910
    If I select Smith Jane, the record for Smith Alex will display. I know that this has something to do with the bound column property, but I am unsure what to do to fix the issue.

    What I have tried:
    I tried setting the primary key as the first field, but then I could only search by the primary key, which is unrealistic for this database. Users will be searching based on last name. How do I make the combo box select whichever record I select from the drop down list?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    look at the code behind the combo box
    it will use either a macro or a fragment of VBA

    there's not enough information to provide a solution...
    if you are getting the wrong rows, then its down to you specify the wrong column to look for

    do you expect to search by a combination of lastname and forename or SSN
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The problem is that the Combobox Wizard is retrieving the Record using the FindFirst method on the Recordset, based only on the last name, and the first 'Smith' entry will always be 'Alex,' in this example.

    If your Form is already based on a Query, go into the Query Design View. If it isn't, you need to go into Design View for Queries and create a Query with the pertinent data from your Table.

    Now go to a blank field in the Query Grid and type this in:

    CombinedFields: [LastName] & " " & [FirstName]

    replacing the Field names with your actual names.

    Now go into Design View for your Form, change the RecordSource for your Form to the Query (if it is currently set to the Table) delete your old Combobox and create a new one. This time, when the Wizard comes up and asks for the Table or Query to retrieve the data from, select your Query. Next choose CombinedFields as the Field for your Combobox to use to retrieve the Record.

    Extending this example to include the SSN field would be even better; no matter how unusual a name is, duplicates are almost always out there!

    Linq ;0)>
    Last edited by Missinglinq; 06-11-14 at 12:42.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Everyone be aware that this is cross-posted at

    Combo box returning wrong results in Access 2010

    As I said elsewhere, if you are going to cross-post, mention that fact in your all of the identical posts. This helps to prevent the volunteers who give of their time from wasting that time by working at cross purposes; giving responses that have already been given elsewhere, even giving responses to a problem that has already been solved elsewhere.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Missinglinq View Post
    Everyone be aware that this is cross posted
    OK I'm out
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    May 2014
    Location
    Columbia, SC
    Posts
    5

    SOLVED (for now)

    Thank you for all of your help. I scrapped the combo box and started the wizard again. Then, I set the SSN as the first column, (which is the primary key) allowed it to be hidden and the combo seems to work fine.

  7. #7
    Join Date
    May 2014
    Location
    Columbia, SC
    Posts
    5

    Cross-posted

    Sorry. I only cross-posted (which I wasn't aware was an issue) because some forums get quicker responses than others. Also, some responses are more helpful than others. I never post when there is already a working solution available. I will mention that it is cross posted next time.

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
  •