Save unbound combobox column value in Access 2007

I have a combobox that has two columns. Column 0 contains the index number and is bound, column 1 contains the description and is displayed. I need to be able to reference both values, but I cannot seem to reference the unbound column.

I would expect something like this to work:
[Forms]![MyForm]![MyCombobox]![Column](1)
But Access does not recognize the Column property. Anyone have a thougt?
LVL 1
JP_TechGroupAsked:
Who is Participating?
 
harfangCommented:
The Column() property is mistaken for a function by the Jet expression evaluator. You cannot use it in a query. Instead, you can use =MyCombobox.Column(1) in a text box, and refer to that text box in your query. Better yet, refer to the column directly, meaning outside of the quotes, while building your query. For example:

DoCmd.RunSql "INSERT INTO Table1(Field1) VALUES('" & MyCombobox.Column(1) & "')"

(°v°)
0
 
roeibCommented:
my friend, you do not need the column for your needs.
the idea is that :

[Forms]![MyForm]![MyCombobox].SelectedItem.Value will give you the Index and the Text is your description

hope this is helping you out
0
 
JP_TechGroupAuthor Commented:
Hmmm, value returns the index, but text does not return anything.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
JP_TechGroupAuthor Commented:
Perhaps I should add that I am trying to update a table with this data using docmd.runsql
with this reference as part of the query.

If I create a text box and bind it to the combobox using .text as you suggested, it works fine... if I use it in the query, it returns nothing.
0
 
Helen FeddemaCommented:
It may be helpful to assign the value to a variable, so you can display it in the Immediate window for debugging purposes.  The syntax for referencing a combo box column (on the current form) is:
0
 
Helen FeddemaCommented:

Me![txtLastNameFirst] = Me![cboSelect].Column(1)
or
   strLastNameFirst = Me![cboSelect].Column(1)

Open in new window

0
 
Helen FeddemaCommented:
Then you can use the variable in your SQL code.
0
 
JP_TechGroupAuthor Commented:
I used both solutions. Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.