Multi-column combo box

[RayN]

I have a form with an unbound combo box that I am using
to select criteria for two fields in a select query. The
criteria are in the format [Forms]![Billing Form]!
[Billing Name] for the bound column and [Forms]![Billing
Form]![Billing Name].[Column(2)] for the second criteria.

As far as I can tell, the syntax is correct. However,
when I run the query. I get a dialog box asking for the
[Forms]...[Column(2)] value. Why does the query capture
the bound value but not see the value in Column 2 of the
combo box. I want to avoid using two combo boxes to
select the two criteria.

 

[Lynn Trapp]

Try changing it to [Column(1)]. The first column is Column(0).

 

[Sandra Daigle]

Hi Ray,

Jet - AFAIK the database engine can not see the properties of an open form
so you can't refer to a column of a combo box. Another way to do this is to
create a hidden control on your form and set the controlsource of this
hidden control to the column of the combo and then refer to that control in
your query. Here's what to put in the controlsource of the hidden control:

=[Billing Name].[column](2)

Note that column(2) is actually going to be the 3rd column in the combo's
underlying query. The property is indexed starting with 0.

 

[Guest]

Hi Sandra,

Thanks for the quick reply. The hidden control
worked...to a point. The database has clients whose name
is associated with multiple companies. When I used the
combo box as described in my first message, only the
first company name appears in the new hidden control
regardless of which combination of name and company I
select from the first combo. As a result the query
returns the wrong result.

How can I force the value from the appropriate record of
the combo box's to appear in the new hidden control.

-----Original Message-----
Hi Ray,

Jet - AFAIK the database engine can not see the properties of an open form
so you can't refer to a column of a combo box. Another way to do this is to
create a hidden control on your form and set the controlsource of this
hidden control to the column of the combo and then refer to that control in
your query. Here's what to put in the controlsource of the hidden control:

=[Billing Name].[column](2)

Note that column(2) is actually going to be the 3rd column in the combo's
underlying query. The property is indexed starting with 0.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

I have a form with an unbound combo box that I am using
to select criteria for two fields in a select query. The
criteria are in the format [Forms]![Billing Form]!
[Billing Name] for the bound column and [Forms]! [Billing
Form]![Billing Name].[Column(2)] for the second criteria.

As far as I can tell, the syntax is correct. However,
when I run the query. I get a dialog box asking for the
[Forms]...[Column(2)] value. Why does the query capture
the bound value but not see the value in Column 2 of the
combo box. I want to avoid using two combo boxes to
select the two criteria.

.

 

[Sandra Daigle]

Hi Ray,

Is it the first company associated with the selected client that shows up?
If the hidden control's controlsource is set as described, its value should
be the value associated with the current row. If you unhide the hidden
control what do you see?

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi Sandra,

Thanks for the quick reply. The hidden control
worked...to a point. The database has clients whose name
is associated with multiple companies. When I used the
combo box as described in my first message, only the
first company name appears in the new hidden control
regardless of which combination of name and company I
select from the first combo. As a result the query
returns the wrong result.

How can I force the value from the appropriate record of
the combo box's to appear in the new hidden control.

-----Original Message-----
Hi Ray,

Jet - AFAIK the database engine can not see the properties of an
open form so you can't refer to a column of a combo box. Another way
to do this is to create a hidden control on your form and set the
controlsource of this hidden control to the column of the combo and
then refer to that control in your query. Here's what to put in the
controlsource of the hidden control:

=[Billing Name].[column](2)

Note that column(2) is actually going to be the 3rd column in the
combo's underlying query. The property is indexed starting with 0.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

I have a form with an unbound combo box that I am using
to select criteria for two fields in a select query. The
criteria are in the format [Forms]![Billing Form]!
[Billing Name] for the bound column and [Forms]! [Billing
Form]![Billing Name].[Column(2)] for the second criteria.

As far as I can tell, the syntax is correct. However,
when I run the query. I get a dialog box asking for the
[Forms]...[Column(2)] value. Why does the query capture
the bound value but not see the value in Column 2 of the
combo box. I want to avoid using two combo boxes to
select the two criteria.

.

 

[RayN]

Hi Sandra,

You are correct. The first company that is associated
with the selected client shows up. If I unhide the hidden
control, I see this first value regardless of which
client / company combination I select. Why is this
happening?

-----Original Message-----
Hi Ray,

Is it the first company associated with the selected client that shows up?
If the hidden control's controlsource is set as described, its value should
be the value associated with the current row. If you unhide the hidden
control what do you see?

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi Sandra,

Thanks for the quick reply. The hidden control
worked...to a point. The database has clients whose name
is associated with multiple companies. When I used the
combo box as described in my first message, only the
first company name appears in the new hidden control
regardless of which combination of name and company I
select from the first combo. As a result the query
returns the wrong result.

How can I force the value from the appropriate record of
the combo box's to appear in the new hidden control.

-----Original Message-----
Hi Ray,

Jet - AFAIK the database engine can not see the properties of an
open form so you can't refer to a column of a combo box. Another way
to do this is to create a hidden control on your form and set the
controlsource of this hidden control to the column of the combo and
then refer to that control in your query. Here's what to put in the
controlsource of the hidden control:

=[Billing Name].[column](2)

Note that column(2) is actually going to be the 3rd column in the
combo's underlying query. The property is indexed starting with 0.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


RayN wrote:
I have a form with an unbound combo box that I am using
to select criteria for two fields in a select query. The
criteria are in the format [Forms]![Billing Form]!
[Billing Name] for the bound column and [Forms]! [Billing
Form]![Billing Name].[Column(2)] for the second criteria.

As far as I can tell, the syntax is correct. However,
when I run the query. I get a dialog box asking for the
[Forms]...[Column(2)] value. Why does the query capture
the bound value but not see the value in Column 2 of the
combo box. I want to avoid using two combo boxes to
select the two criteria.


.

.

 

[Sandra Daigle]

Hi Ray,

Thinking this through a bit more I believe that the real problem here is
that the bound column of the combo needs to be a column that returns a
unique value from your rowsource. You really need to be using two combos for
lookup - I know it seems like you should be able to do this in one combo but
you really are trying to get two discrete values - client name and Company
name. The problem is that the client name isn't unique in your rowsource
query (since it can be associated with multiple companies). So when you
select one, regardless of which one you 'pick' from the list, the bound
column is going to point back to the first row with that client's name
(hence you get the first company as well). If Company is unique in this
query you might be able to change the bound column to the Company field and
get it to work that way. Otherwise I think you really need to use
synchronized combos (second combo's rowsource is filtered by the value of
the first combo).

I apologize for not seeing this sooner!

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Hi Sandra,

You are correct. The first company that is associated
with the selected client shows up. If I unhide the hidden
control, I see this first value regardless of which
client / company combination I select. Why is this
happening?

-----Original Message-----
Hi Ray,

Is it the first company associated with the selected client that shows
up? If the hidden control's controlsource is set as described, its value
should be the value associated with the current row. If you unhide the
hidden control what do you see?

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi Sandra,

Thanks for the quick reply. The hidden control
worked...to a point. The database has clients whose name
is associated with multiple companies. When I used the
combo box as described in my first message, only the
first company name appears in the new hidden control
regardless of which combination of name and company I
select from the first combo. As a result the query
returns the wrong result.

How can I force the value from the appropriate record of
the combo box's to appear in the new hidden control.
-----Original Message-----
Hi Ray,

Jet - AFAIK the database engine can not see the properties of an
open form so you can't refer to a column of a combo box. Another way
to do this is to create a hidden control on your form and set the
controlsource of this hidden control to the column of the combo and
then refer to that control in your query. Here's what to put in the
controlsource of the hidden control:

=[Billing Name].[column](2)

Note that column(2) is actually going to be the 3rd column in the
combo's underlying query. The property is indexed starting with 0.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


RayN wrote:
I have a form with an unbound combo box that I am using
to select criteria for two fields in a select query. The
criteria are in the format [Forms]![Billing Form]!
[Billing Name] for the bound column and [Forms]! [Billing
Form]![Billing Name].[Column(2)] for the second criteria.

As far as I can tell, the syntax is correct. However,
when I run the query. I get a dialog box asking for the
[Forms]...[Column(2)] value. Why does the query capture
the bound value but not see the value in Column 2 of the
combo box. I want to avoid using two combo boxes to
select the two criteria.


.

.