Combobox always retrieves the first client when multiple clients have same last name

[darnnnel]

I have a combobox for retrieving patient records. If I have several patients with the same last name, the combobox always retrieves the first patient with this last name, not necessarily the one selected. for instance, if I have

Smith Daniel
Smith John
Smith Ruth

Access always retieves Smith Daniel, even if I select Smith John or Smith Ruth. What am I doing wrong?

Note: Split from original thread and edited for clarity and patient confidentiality... Linq ;0)>

[Rabbit]

However you set it up, it's retrieving by last name only. You have to either tell it to retrieve by last AND first name or by some client ID.

[missinglinq]

When you use the Combobox Wizard to build a dropdown box to retrieve records, it uses the FindFirst method to do so, and that's exactly what it does, finds the first occurrence of, in this, a given last name! First and last name show in the cbo, but Access only uses the last name to retrieve the record!

The workaround to this is to create a calculated field in your underlying query that joins first and last name, then base you retrieving combobox on this new field.

In Design View for you query, go to a blank field and enter this:

WholeName: [Last Name] & " " & [First Name]

Check the Show box and run your query. You should now have a field named WholeName that contains just that, LastName FirstName!

Now go into your form's Design View, delete you old combobox and create a new one to retrieve your records, only this time use only the WholeName field in the combobox.

When you open your new combobox it'll look the same as before

LastName FirstName

but when you click on Smith Ruth you'll get Smith Ruth's record not Smith Daniel's!

Linq ;0)>

[darnnnel]

Thank guys it worked!

[missinglinq]

Glad we could help!

Linq ;0)>