Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Location
    SK, Canada
    Posts
    9

    Unanswered: Lookup Form Duplicates?

    I have made a record 'lookup' form for a client listing.

    The LASTNAME field is a combo box that lists the last names. When you select the right one, the rest of the fields fill with the information.

    One problem is that if there are duplicate names in the list (relations, companies with same names, the field simply post all the information from the first record in the sequence.

    The PK in the client table is generated by an simple ID autonumber (recID), so each record is unique?

    I tried to correct the problem, but then what occurred is that anytime the 'enter' was used to move to the next record, it would duplicate the record rather than MOVE to the next record! Not good...

    Is this a problem with the controls on the 'combobox', form control or does the LASTNAME field have to have some changes (duplicates, YES is allowed on the table, etc)...

    Thx

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Your asking us to identify which brand of flour you used by tasting a nibble of the bread you baked last week.

    First, if the table with the client names uses an Autonumber key to uniquely identify each record, does your combo box ALSO use that key to select the matching record?

    Along the same lines, the subordinate table that contains the data - does that use the LASTNAME filed, or the KEY filed?

    If your using a Form/Subform to perform the return, then it creates a new record with the matching key in the subform by design. For example, if your form was an address book entry, and the sub form was phone numbers, each phone number you add you would want to be keys to the parent record.

    These are just of few of the unknows here. Perhaps you should attach a sample.

    Good luck

  3. #3
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Hi

    Right been in this situation........ I use the same system for looking up achievement dates for qualifications.

    To overcome the same surname problem i set the combo to pick up the surname and first name (initial as well) that way it can give the right information and stops problems of 26 jones ( i have 231 of them so you see ive been there) - futher more it speeds up te selection process considerably

    hope its a help for you

    reggards
    gareth

  4. #4
    Join Date
    Apr 2004
    Location
    SK, Canada
    Posts
    9
    Fair enough...

    Essentially all I want is a findform screen. I have used a combo box to simply drop down last names and first names from a qryLookup based on the main client table.

    If the right client record/name is found in the existing list, then it is selected and the rest of the fields 'repopulate' with the information corresponding to that client record...

    It seems to be okay, except when it has duplicate names, and then it doesn't put up the different information.

    Is it easier to simply make a separate 'findform' with a 'get form' button that brings up a individual record form...

    In essense, I simply what a record search function that won't alter the existing database...

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    If the table storing the names uses an Autonumber Primary Key, then the table with the additional information should link back using the key number, not the lastname. Chances are, this is how it is already setup. If not, it should be. That way, 37 Smiths will be 37 different key values.

    Now, in the combo box, setup the underlying query to show:
    KeyFiled, LastName, FirstName
    I personally use: [LastName] & ", " & [FirstName] as one field becasue it's fancier

    Set the combo box bound column to 1 and the width of the first column 0". This will make the value of the combo box the Key but hide the key from the user.

    Finally, make sure the code that is finding the matching record for you is matching up the key value, not the lastname.

    Then you're all set.

    Have fun

Posting Permissions

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