listbox column property does not work in query criteria

[Jeff]

I have a form with 2 listboxes, (let's call them LB1 and LB2) and the
contents of LB2 depend on the selection made in LB1. Both listboxes are
multi column and LB2 must be filtered based on 2 column values of the
selection made in LB1.

One of the columns I use from LB1 as a filter criteria is the bound column,
so I can simply enter [LB1].[Value] in the query grid for the LB2 filter
criteria, and this works fine.

However, if I try to use [LB1].Column(1) as a filter criteria in the query
grid for LB2, I get and error message that says "Undefined function
'lb1.column' in expression" and the query will not work.

If I open my form, make a selection in LB1, and then go to the VBE immediate
window and type debug.print [forms]![myform]![lb1].column(1), it returns the
correct value, so I know my reference is correct, but for some reason it
just won't work in the query grid even though [lb1].[value] works fine in
the same query.

Am I doing something wrong, or does the listbox column property simply not
work in the query grid?

Jeff

 

[Duane Hookom]

Thanks Jeff.

-- <TOZ> --
Duane Hookom
MS Access MVP

Found the Microsoft Knowledgebase solution to this problem!

http://support.microsoft.com/default.aspx?scid=kb;en-us;209496

Your Eval solution was correct except you forgot to enclose the reference
inside quotes. This works in the query grid:

Eval("[forms]![myform]![lb1].column(1)")

Thanks!

Jeff

I create a hidden text box on the form with a Control Source of:
=[lb1].column(1)
You can then reference this text box in your criteria. I have also heard
that the following would work:
Eval([forms]![myform]![lb1].column(1))

~(TOZ)~
Duane Hookom
MS Access MVP

I have a form with 2 listboxes, (let's call them LB1 and LB2) and the
contents of LB2 depend on the selection made in LB1. Both listboxes are
multi column and LB2 must be filtered based on 2 column values of the
selection made in LB1.

One of the columns I use from LB1 as a filter criteria is the bound column,
so I can simply enter [LB1].[Value] in the query grid for the LB2 filter
criteria, and this works fine.

However, if I try to use [LB1].Column(1) as a filter criteria in the query
grid for LB2, I get and error message that says "Undefined function
'lb1.column' in expression" and the query will not work.

If I open my form, make a selection in LB1, and then go to the VBE immediate
window and type debug.print [forms]![myform]![lb1].column(1), it

returns
the

correct value, so I know my reference is correct, but for some reason it
just won't work in the query grid even though [lb1].[value] works fine in
the same query.

Am I doing something wrong, or does the listbox column property simply not
work in the query grid?

Jeff