how to access index of combobox field?

[BeemerBiker]

I have a combo box field "Grouping" in "MyTable" and set the design to

Row Source: SELECT Categories.Grouping, Categories.index FROM Categories; 
BoundColumn:   1
Column Count:  2
Column Widths: 1",0"
 

How do I issue a select query so I can just get the index, and not the actual text value?

I know a join would work where the join would be on Categories.Grouping=MyTable.Grouping and I could pick up Categories.index. However, since there are already 2 columns in MyTable at that single field, I thought I could avoid the join

thanks for looking

[Stewart Ross]

Hi. It is much simpler to do than you imagine; use the Columns property of your combo box to refer to the second (hidden) column. Columns are numbered from 0, with the 0 column being the leftmost, column(1) the next, and so on, regardless of whether or not they are visible to users.

In this case, the index value can be referred to in another textbox or within a VBA function as

[Grouping].Column(1)

Using hidden columns in combo or listboxes to store lookup values based on the main row displayed can be very useful, and I'd advise that you look at the combo rowsource query as the place where you should start. You can make the combo itself do the lookup for you by including suitable fields in your combo rowsource query, then use the Columns property to display the additional data about the item chosen.

For example, a 'select user' combo can have the user's department and department ID in its columns, and these in turn can be shown on a userform by using unbound textboxes whose control source propery is set to the column concerned. Such a property setting is like this, assuming that the value to display is in the fourth column of a combo called [User ID]:

=[User ID].Column(3)

-Stewart

[BeemerBiker]

Thanks Stewart, that is exactly what I am looking for.