Combobox bound column value? when width is 0cm

[Guest]

I have a bound combobox with 5 columns. It is populated with a SQL statement.

Column 1 is bound but not visible (width of 0cm) – unique
Column 2 visible when the combobox is ‘closed’ – not unique
Columns 3 to 5 are only visible when the combobox is ‘open’

The information in column 2 must be read in context with columns 3, 4, and 5.

Next to the bound combobox I want to place unbound text boxes with a DLOOKUP
as the control source. These unbound test boxes will display the contents of
columns 3, 4, and 5.

I need to do my first DLOOKUP using column 1 of the combobox (the bound
column which is not visible but is unique). Column 2 (the visible column) is
not unique.

How do I get the value of the hidden bound column in the combobox?


Thanks for any help.
Seth

 

[Guest]

The bound column will just be the name of the control. That is, if the
control's name is Combo1, then the value of column 1 can just be referred to
as Combo1 in VBA.

To refer to this in the criteria of the DLookup, add its context (the
current form) and thus refer to it as [Forms]![Form1]![Combo1]

That is, the DLookup will look something like this:

Combo3 ControlSource is
=DLookup("[Field3]","[Table1]","[Field1] = " & [Forms]![Form1]![Combo1])

Combo4 ControlSource is
= DLookup("[Field4]","[Table1]","[Field1] = " & [Forms]![Form1]![Combo1])

You may need to put this in Combo1_AfterUpdate
Combo3.Requery
Combo4.Requery

 

[Dirk Goldgar]

I have a bound combobox with 5 columns. It is populated with a SQL
statement.

Column 1 is bound but not visible (width of 0cm) - unique
Column 2 visible when the combobox is 'closed' - not unique
Columns 3 to 5 are only visible when the combobox is 'open'

The information in column 2 must be read in context with columns 3,
4, and 5.

Next to the bound combobox I want to place unbound text boxes with a
DLOOKUP as the control source. These unbound test boxes will display
the contents of columns 3, 4, and 5.

I need to do my first DLOOKUP using column 1 of the combobox (the
bound column which is not visible but is unique). Column 2 (the
visible column) is not unique.

How do I get the value of the hidden bound column in the combobox?


Thanks for any help.
Seth

All of the columns can be referred to by way of the control's Column
property, which is a zero-based array, so column 1 is
YourComboName.Column(0), column 2 is YourComboName.Column(1), and so on.
Further, you don't need to use the Column property to get the value of
the bound column, because that column supplies the Value propert of the
combo box itself.

If I understand what you're saying you want to do, you don't need a
DLookup to get the values of the columns. You can just create text
boxes with ControlSource properties like these:

=[YourComboName].[Column](2)

=[YourComboName].[Column](3)

=[YourComboName].[Column](4)

In all of the above, of course, you would substitute the name of your
combo box for "YourComboName".