MS Access ComboBox.Column - use name instead of index ?

Question

i have ComboBox controls with multiple columns as a Row Source in an Access 2007 form. i'm currently getting the selected values out of each ComboBox item this way...

value = ComboBoxName.Column(i) 'where i is the index.

i would like to use the actual names of the columns as with Recordset's...

value = ComboBoxName.Recordset.Fields("columnname")

(please note that this does not work properly on the Recordset's of the ComboBox'es: it only works AFTER the first time the ComboBox is changed)

my questions:

• can i do something to "make this work" on a ComboBox's Recordset?:

value = ComboBoxName.Recordset.Fields("columnname")

• is there a method that directly gets the value of the selected record using the name ?

• does the ComboBox or it's Recordset have a method i can use to get a column index by specifying a column name ?

i would like to avoid writing a function and i don't feel comfortable specifying column indices which may change in the future.

Answer 1

There is no simple way to do this. You can create a recordset, or you can assign values to variable for each column name (FirstCol=0).

As an aside, why would the columns change? If you are using a select statement, the combo will fail if the table is changed.