search record with a combo box of names

[sanal]

I have a combo box on a form to search records related to customers by their name. The records have two persons with the same name example Satheesh. When I select Satheesh in the combobox the rcord related to the first Satheesh is shown on the form. How can I show the record of the second satheesh also on the form by selecting the second Satheesh in the combo box. If there is any better way other than the combo box. Thanks in advance for the reply.

[isladogs]

I use a combo box and a listbox
The combo is used to select the last name e.g. Satheesh
I use unique values here so you don't get duplicates

This then shows a list of all customers matching that name
Add additional columns such as first name or company as appropriate
The first hidden column is the unique ID field

Select the required person from the list to open the second form filtered by the customer ID field

[sanal]

I use a combo box and a listbox
The combo is used to select the last name e.g. Satheesh
I use unique values here so you don't get duplicates

This then shows a list of all customers matching that name
Add additional columns such as first name or company as appropriate
The first hidden column is the unique ID field

Select the required person from the list to open the second form filtered by the customer ID field

It is not clear how to use a combo box with list box. If you give an example form that will be helped.
Moreover my customer name field contain similar names ie. not unique.
I know that it is possible to search record with VBA with loop statement. But lam not good in using code. If you help me to write a code for searching the record with a text box and a search button that shows all the records related to the name Satheesh one by one. Thank you for your helping mind.

[isladogs]

Its late here in the UK. I'll upload something for you tomorrow.

[Missinglinq]

The problem is that the Combobox Wizard is retrieving the Record using the FindFirst method on the Recordset, based only on the last name.

If, for example, you have the names

Smith, Adam
Smith, Barbara
Smith, Charles

and you select Barbara Smith, from the Combobox, or Charles Smith, the Record for Adam Smith will always be retrieved, because of the Records belonging 'Smiths,' Adam Smith's will always be the first one 'found,' by using FindFirst.

If your Form is already based on a Query, go into the Query Design View. If it isn't, you need to go into Design View for Queries and create a Query with the pertinent data from your Table.

Now go to a blank field in the Query Grid and type this in:

CombinedFields: [LastName] & " " & [FirstName]

replacing the Field names with your actual names.

Now go into Design View for your Form, change the RecordSource for your Form to the Query (if it is currently set to the Table) delete your old Combobox and create a new one. This time, when the Wizard comes up and asks for the Table or Query to retrieve the data from, select your Query. Next choose CombinedFields as the Field for your Combobox to use to retrieve the Record.

This said, it should be noted that few name combinations are truly unique, at least in English speaking countries, and rather than retrieving using a last name, or even first and last name combination, you really should be retrieving based on a unique value, as suggested by ridders, even if this value is not displayed in the Combobox.

Linq ;0)>

[isladogs]

Attached is a simple example showing what I mean

The startup form contains a combo box and a listbox filtered to show all contacts with that last name
I've deliberately used several people with the last name JONES including two with the same first name SUSAN.



Up to you which fields are shown in the listbox

The second form frmContactDetails is a cut down & modified version of a form which originally came from the MS Contacts template database
NOTE: Some features have been disabled for this example