Results 1 to 3 of 3

Thread: Query Issue

  1. #1
    Join Date
    Mar 2009
    Posts
    16

    Unanswered: Query Issue

    Hi,

    I have a combo box using a datasource from a query the query is first name and a last name. When I have two names with the same surname, the combo box always enters another name with a lower alphabetical name with the same surname.

    Example

    I have

    Des Galah
    Coby Galah

    I selct Des on the combo boxs but it comes up with coby after selecting. This is only happening to ppl with the same surname.

    I also have another combo box that queries just the surname which is working ok

    Please help

    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is because the value returned by the combo is the one of the first column.

    I suppose that the rowsource of the combo is something like

    Code:
    SELECT Name, Surname FROM Mytable ORDER BY Name, Surname;
    When you select a row in the combo, only the first column (ie Name) is returned, so you get the first line where Name equals what you selected, in accordance to the sort order.

    The best practice to solve this problem is to add a key to your table, so it looks like this:


    Key+-Name-+-Surname
    1 | Des | Galah
    2 | Coby | Galah
    3------------------ etc...

    Now you base your combo on a select statement including the key, like this:

    Code:
    SELECT Key, Name, Surname FROM Mytable ORDER BY Name, Surname;
    Then you base your query on the value returned by the combo that now is Key, key being a unique value in the Mytable table.

    Another solution would be to retrieve the values from the different columns of the combo using the syntax:
    Code:
    name = Combo.Column(0).Value
    Surname = Combo.Column(1).Value
    On this issue, see: The Access Web - Welcome

    Have a nice day!

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I suspect faulty table design. The combo box should be bound to the person's ID, not their surname.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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