Vba code to show selected data from combobox

Question

I have an Access 2010 form which has a combobox listing three columns:

1. ID
2. first name
3. last name

The combo box is bound to a table containing this data. The ID column in the combo box is hidden, it only shows the first and last name.

When the user selects a row only the the first name is shown. in the property section, I chose:

• Column Count: 3
• Column widths:0;3,3
• Bound Column: 1

I made another text field and in the combobox I wrote the following vbcode:

text=combo.value

that shows in the text field the chosen ID.

I want to show in another field (text\combo?) the last name. How can I do that?

Answer 1

well I did it with recordset. if someone know a simpler solution, I will be glad to lrn. this is my code:

Dim dbs As ADODB.Connection
Dim id As String    

Set dbs = CurrentProject.AccessConnection 
Set rsRep = New ADODB.Recordset 
Set rsRep.ActiveConnection = dbs 

rsRep.Open "tblRep" 

id = Me.cbPrvFirstName
rsRep.MoveFirst
rsRep.Find "RepId=" & id

Me.txtPrvLastName = rsRep.Fields(2)
rsRep.Close      

Answer 2

You can pull in the value from another column within the combo box using the below code. By default combo.value will always give the value from the first column even if hidden.

try text=combo.column(x)

in your case to retrieve last name it would be text=combo.column(2)

where x is a NUMBER of the column you want to retrieve 0 being the first column

You could also try an alternative - This works like a VLookup in excel if you are familiar with that. (Not as simple as the first option ;) )

text=Dlookup("[last name]","tblRep","RepId=" & combo.value)