DLookUp issue

[GLEberts]

With the help of someone I used the following:

=DLookUp("[FirstName]","[tblCustomerList]","[LastName] = '" & [cboCustomerList] & "'")

The above works great except that -
The problem that is happening - I can not have more then one customer with the same last name. When I click on the one that I want the other will appear on the form.

What I am trying to do is:
(1) Use a combo box on a form to autopopulate the customer information into it. The example above of course is for the First Name.
(2) It works fine except for the bug that I can not have more then one customer with the same last name.
(3) I have tryed many differrent combinations of the above with taking out [LastName] and using the RecordID instead which is "ContactID" - it returns an error.

If anyone could help set me straight I would appreciate it.
Thanks
Gary

[FishVal]

Hi, Gary.

You should use PK field to lookup for particular table record.
What about multicolumn Combobox?

Take a look at the following
Activate textbox on mouse over, Showing linked data

Regards,
Fish

[missinglinq]

There's no bug involved here; your code is doing exactly what you're telling it to do! It's looking in your table, finding the first record with the last name you picked with the combobox, and returning that customer's first name. In order to retrieve a record or part of a record based using DLookup, the third arguement has to involve a unique piece of data.

The error you get when you try to substitute your ContactId is , I suspect, Error 13, "Type mismatch."
Your ContactID is probably numeric datatype, and the syntax for a numric value is in the third argument is different than that for a string datatype.

The trick in retrieving records by a name, such as a customer name, is to retrieve it by first and last name. If you have a truly large database, where there are apt to be a number of "John Smiths," you need to add another piece of data to the equation, like a SSN.

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 new field named CompleteName, with the last name and first name in it, separated by a space.

Now, delete your current combobox, create another combobox based on the query with your new calculated field CompleteName as the bound field. And remember to delete the DLookUp statement from wherever you have it.

You don't really say whether you're simply retieving the entire customer record or whether you're retrieving parts of it and then assigning it into textboxes in your current form, but this will work either way.

If you have any problems, come back and post them.

Linq ;0)>

[GLEberts]

Linq,
I used your suggestion and it worked like a charm.
Thanks again.
Gary

[missinglinq]

Glad we could help!

Linq ;0)>