Combobox holding surnames from Access

[chakotha]

A pointer here would be great.

I have a form in which I want to display data contained in an Employees table in Access.

On loading, I have populated a Combobox on the form with people's surnames selected from the Employees table. When I select one of these surnames I want to get the whole row from the Employees table associated with that person and fill in the relevant employee details on the form.

I think I need something like:

[VB]cmd.CommandText = "SELECT * FROM Employees WHERE EmployeeID=" & EmployeeID[/VB]

but I only have the surname on the Combobox to go with and if there are multiples of identical surnames in the table I'll only ever retrieve the first entry with that surname. Is there a way to 'hide' the ID in the Combobox maybe???

[ARPRINCE]

What I usually do in this scenario is I don't bind the combo box with my recordset. When I retreive data,I read the records one at a time, build a string that has the name and the record ID, then add it to my dropdown.

So if you have the following data for example:

ID | NAME
---+------
100 APPLE
101 BANANA
102 ORANGE
103 GRAPES


Build a string where you have the name of the fruit and then on the 18th character, your record ID starts (just make sure that the ID is not visible on the combo box so you may have to play around on the number of characters where your ID starts).

123456789012345678
APPLE            100
BANANA           101
ORANGE           102
GRAPES           103

So basically, the user only see the names of the fruit since the ID is invisible to them.

Now, when a user clicks on a selection within the combo box, on the "ComboBox1_SelectedIndexChanged", extract the ID from the combobox text and use that to get your data.

Now in your case, you would still have problems if you are just showing the lastnames in your combobox. Let us say you have 2 persons with last name CLINTON. Even with a hidden ID, you would still never know which one among the two is for BILL or HILLARY. You would only find out after clicking on one of them to display the information. So I would go for adding the first name initial.

CLINTON B 105
CLINTON H 106


Hope this helps.

[Edneeis]

Try searching here or in the help files for ValueMember.

[chakotha]

Thanks for replies! I got it working using ValueMember. Here is the code to populate the combobox:

Do Until Not nextResult
    While reader.Read()
        tempEmployeeID = reader.Item(0).ToString()
        EmployeeName = reader.Item(1).ToString() + ", " + reader.Item(2).ToString()

        cmbSurname.DisplayMember = EmployeeName
        cmbSurname.Items.Add(EmployeeName)
        cmbSurname.ValueMember = tempEmployeeID

        End While
        nextResult = reader.NextResult()
Loop 

I am not sure what the

cmbSurname.DisplayMember = EmployeeName

does as it alone didn't put anything in the combobox.

cmbSurname.Items.Add(EmployeeName) did though.