Mysterious Behaviors of the Combo Box

Hi,
I am having some problems getting combo boxes to work right. I'm
trying to get a combo box to select all the distinct "invoice numbers"
in a table by using the following SQL statement:
"SELECT DISTINCT TblStock.[Invoice Number] FROM TblStock;"

I tested this statement by creating a new Query, and it worked fine.
But when I put this statement into the "row source" of my combo box,
it doesn't work. When I click on it, it displays nothing. What's
going on with this? I don't understand why it wouldn't work.

Another question I have is how would I pick one of several fields that
is selected by a combo box. For example, the current selection of my
combo box includes two values from two columns of the table ("1" from
the product ID column and "123-432" from the Invoice number colum),
how would I pick which one of these values I want to access? When I
do Me!CbxCombo1, a value of "1" is returned. What would I do if I
want to return the value "123-432" from the second column?

Thanks for your help.

Check the properties of your combobox and make sure that under the data tab
that "Row Source Type" is "Table/Query".

As to you second question on how to access differente columns. THe value
returned by a combobox that displays multiple columns will depend on the
bound column. A combobox's control source is usually bound to a foreign
key or a lookup table.

The bound column for a combobox is the value that is tied to that
combobox, applicable to comboboxes with multiple columns. You can set
which column is the bound column under properties. It is usually a primary
key that is bound since a multiple column combobox can display several
fields from one table.

To have the combobox return a different column, change the bound column to
another number.

You can also refer to the other columns in VBA;

'Refers to the Bound Column
me.cboValue

'Refers to the third column. Not the Second Column.
me.cboValue.Column(2)

'Refers to the first column.
me.cboValue.Column(0)

Hope this helps!

Use 'column' to refer to columns in your combo box.

s = me.cbo.column(0)

actually, you may refer to rows in the same way:

s = me.cbo.column(i,j)
[color=blue]
> it doesn't work. When I click on it, it displays nothing.[/color]

Probably the column widths are wrong, but it could be
the row source type, or the number of columns, or many
of the other properties.

(david)



"J. Yuan" <jwyuan@mail.com> wrote in message
news:e5b532b4.0408021057.5e1debba@posting.google.c om...[color=blue]
> Hi,
> I am having some problems getting combo boxes to work right. I'm
> trying to get a combo box to select all the distinct "invoice numbers"
> in a table by using the following SQL statement:
> "SELECT DISTINCT TblStock.[Invoice Number] FROM TblStock;"
>
> I tested this statement by creating a new Query, and it worked fine.
> But when I put this statement into the "row source" of my combo box,
> it doesn't work. When I click on it, it displays nothing. What's
> going on with this? I don't understand why it wouldn't work.
>
> Another question I have is how would I pick one of several fields that
> is selected by a combo box. For example, the current selection of my
> combo box includes two values from two columns of the table ("1" from
> the product ID column and "123-432" from the Invoice number colum),
> how would I pick which one of these values I want to access? When I
> do Me!CbxCombo1, a value of "1" is returned. What would I do if I
> want to return the value "123-432" from the second column?
>
> Thanks for your help.[/color]