refer to a particular column in a listbox in a query

[Steve]

Greetings:

I have an unbound combo box whose rowsource is based on a query. The SQL
statement is reproduced below:

SELECT tlkpProtocolSubtypes.chrProtocolSubType
FROM tlkpProtocolSubtypes
WHERE
(((tlkpProtocolSubtypes.intTestLkpID)=[Forms]![frmMaster]![fsubOpenEPS]![lbxTest].[column(4)]))
ORDER BY tlkpProtocolSubtypes.SortOrder;

The problem I am having is the needed information from lbxTest for the where
clause is in column 4 of lbxTest. I can not seem to figure out how to refer
to lbxTest.column(4) in my query using the query builder. Thanks for any
help.

 

[Jeff Boyce]

Re-check Access HELP for the correct syntax for referring to a control on a
subform. I'm not sure your syntax will work as written...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John W. Vinson]

Greetings:

I have an unbound combo box whose rowsource is based on a query. The SQL
statement is reproduced below:

SELECT tlkpProtocolSubtypes.chrProtocolSubType
FROM tlkpProtocolSubtypes
WHERE
(((tlkpProtocolSubtypes.intTestLkpID)=[Forms]![frmMaster]![fsubOpenEPS]![lbxTest].[column(4)]))
ORDER BY tlkpProtocolSubtypes.SortOrder;

The problem I am having is the needed information from lbxTest for the where
clause is in column 4 of lbxTest. I can not seem to figure out how to refer
to lbxTest.column(4) in my query using the query builder. Thanks for any
help.

Use lbxTest.Column(3). The columns in the listbox are numbered 0, 1, 2, and 3
respectively.

 

[Steve]

Hi. Thank-you for your response. Let me clarify. First I was incorrect when I
said column 4, it is actually column 5 (so the reference to column(4) is
correct). Second, the reference to the subform is correct. If I redo the
query so that the information is in column(0) and restate the where clause as
follows:

WHERE
(((tlkpProtocolSubtypes.intTestLkpID)=[Forms]![frmMaster]![fsubOpenEPS]![lbxTest]))

without specifying the column, this works. Let me also reiterate that I am
using the query builder tool. When I make similar references in VBA I don't
have this problem. One issue is the proper way to bracket the expression. If
I use
[lbxTest.Column(4)] it gives me a bracket error. If I use
[lbxTest].[column(4)] it asks me to supply the parameter's value as if it
doesn't recognize it. Thanks for any help.

--
Steve

Greetings:

I have an unbound combo box whose rowsource is based on a query. The SQL
statement is reproduced below:

SELECT tlkpProtocolSubtypes.chrProtocolSubType
FROM tlkpProtocolSubtypes
WHERE
(((tlkpProtocolSubtypes.intTestLkpID)=[Forms]![frmMaster]![fsubOpenEPS]![lbxTest].[column(4)]))
ORDER BY tlkpProtocolSubtypes.SortOrder;

The problem I am having is the needed information from lbxTest for the where
clause is in column 4 of lbxTest. I can not seem to figure out how to refer
to lbxTest.column(4) in my query using the query builder. Thanks for any
help.

Use lbxTest.Column(3). The columns in the listbox are numbered 0, 1, 2, and 3
respectively.

 

[Dirk Goldgar]

Hi. Thank-you for your response. Let me clarify. First I was incorrect
when I
said column 4, it is actually column 5 (so the reference to column(4) is
correct). Second, the reference to the subform is correct. If I redo the
query so that the information is in column(0) and restate the where clause
as
follows:

WHERE
(((tlkpProtocolSubtypes.intTestLkpID)=[Forms]![frmMaster]![fsubOpenEPS]![lbxTest]))

without specifying the column, this works. Let me also reiterate that I am
using the query builder tool. When I make similar references in VBA I
don't
have this problem. One issue is the proper way to bracket the expression.
If
I use
[lbxTest.Column(4)] it gives me a bracket error. If I use
[lbxTest].[column(4)] it asks me to supply the parameter's value as if it
doesn't recognize it. Thanks for any help.

Interesting problem. One solution would be to put a (hidden) text box on
the form, with a controlsource that picks up the value of that column of the
combo; e.g.,

=[fsubOpenEPS].[Form]![lbxTest].[Column](0)

.... for a text box on the main form, or

=[lbxTest].[Column](0)

.... for a text box on the subform. Then your query could just refer to that
text box for its criterion.

Alternatively, without making any change to the form at all, this ought to
work for your query criterion:

WHERE tlkpProtocolSubtypes.intTestLkpID =
Eval("Forms!frmMaster!fsubOpenEPS!lbxTest.column(4)")

But that does seem rather kludgey. Maybe there's a better way.