By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,829 Members | 673 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,829 IT Pros & Developers. It's quick & easy.

how to access index of combobox field?

BeemerBiker
P: 87
I have a combo box field "Grouping" in "MyTable" and set the design to
Expand|Select|Wrap|Line Numbers
  1. Row Source: SELECT Categories.Grouping, Categories.index FROM Categories; 
  2. BoundColumn:   1
  3. Column Count:  2
  4. Column Widths: 1",0"
  5.  
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
Feb 27 '09 #1
Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
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
Feb 27 '09 #2

BeemerBiker
P: 87
Thanks Stewart, that is exactly what I am looking for.
Feb 27 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.