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.

re: Mysterious Behaviors of the Combo Box

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!

re: Mysterious Behaviors of the Combo Box

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]