Combo Box

[gsbatch1]

RESOLVED Combo Box

I have a combo box that lists Employee Last names. I have some duplicate last names. The combo box selection will populate a record for that employee so it can be updated. The problem is, when I have duplicate last names after selecting any duplicate last name it reverts the record to the first last name in the list.

I have 4 Martinez, all have a unique first name. No matter which Martinez I select, it shows only the first Martinez record.

I have no idea what information you will need from me,

 

[missinglinq]

The problem is that the combobox is pulling based on the last name field only. The command the combobox wizard uses for retrieving a record is

rs.FindFirst

which does just that; finds the FIRST record in the recordset with the last name of the selected row. If you have, for instance

Allen James
Allen Kevin
Allen Leon

and you select

Allen Leon

Access will retrieve Allen James, because this is the first Allen that Access comes to. The fix for this involves using a calculated field in a query. If the form is based directly on a table, you need to make a simple query including all the fields of the table, and then change the RecordSource for the form from the table to the query. If the form is already based on a query, more's the better. All form really should be based on queries, for situations just like this.

Now open the query in Design View, and in a blank Field box enter this code:

CompleteName: [LastName] & " " & [FirstName]

Save and Exit Design View

Run you query now to make sure you have it right. You should now have a field named CompleteName, with the last name and first name in it, separated by a space.

Now, delete your current combobox, create another combobx based on your calculated CompleteName field that you just added to the query. Not only will Access take you to the correct Allen, if you keep entereing the name (typing Allen L for instance will take you to Allen Leon) when you click on Allen Leon it’ll take you to his record.

Linq ;0)>