Concatenate problem - how do I reference another field?

[AccessIdiot]

As usual, this seems like a really simple problem but it is stumping me. I am using Access 2007.

I have a form with a combo box that is fed by a look up table. The look up table has an auto id and a text field. The text is what you see in the combo box on the form but it's one of those bound to column 1 (auto id), column count is 2, column widths are 0" and 3" kind of things (my experience with Access 2003 is that this was a common thing to do. Is there a better way to do it now?).

//values in look up table lut_Island which feeds cbo_Island on the form.
//The value chosen in cbo_Island gets stored in [IslandName] in the main table.
1  Apple Island
2  Banana Island
3  Coconut Island
// etc.
 

Anyway, on my form there is also a text box. In this text box I want to concatenate some text with what was chosen in the combo box. So the code on my text box looks like this:

Private Sub txtField_AfterUpdate()
 txtField = [IslandName] & " Platform " & txtField
End Sub

So when the user types a number into txtField what I WANT it to show after they exit the field is "Banana Island Platform 1". Instead what it is displaying is "2 Platform 1" because 2 is the auto id code for Banana Island.

I hope I have explained this well.

What is the code I have to use to get the text value of the combo box to display and not the code value?

[jimatqsi]

If [IslandName] is the name of the combo box, try
[IslandName]!Column(1)

This references the 2nd column of the combo box.

Jim

[AccessIdiot]

[IslandName] is the name of the field in the main table that the value of cboIslandName gets stored in, but I'll give it a try with the combo box name. Thanks!

That worked, I just needed to change it to column 2, not 1. Thanks!

cboIslandName.Column(2)