Can I make form details based on two listbox fields?

[Max Moor]

Hi All,

I often set up forms with a listbox of records and a bunch of textboxes
for details. The form's query has the listbox as a criteria for some index
value, and I requery the form when a listbox click event occurs (selection of
a new record). I think this is fairly standard?

Now I have a case where the records in the listbox are only unique
across two different fields. An index and sub index, if you will. In the
form's query, for the index, I referenced the first, bound, column as I
normally would:

[Forms]![frmMyForm]![lstMyListbox]

For the criteria for the sub-index, I tried:

[Forms]![frmMyForm]![lstMyListbox].Column(1)

but Access says it is an unrecognized function. Can I not reference
any but the bound column? Does anyone have any thought how I might make this
work?

Regards,
Max

 

[Klatuu]

You can't reference a combo or list box column in a query.
They way I get around it is creating a hidden text box on my form and keep
in in sync with current column value using the text box's control source.
Now, be aware, if you change the selection in the list box manually, no
problem, but if you change the seletion in the list box programmatically, you
will have to do a Me.ReCalc to update the control source property of the text
box. (I still have scars on my head from all the scratching figuring that one
out)

 

[Max Moor]

You can't reference a combo or list box column in a query.
They way I get around it is creating a hidden text box on my form and
keep in in sync with current column value using the text box's control
source. Now, be aware, if you change the selection in the list box
manually, no problem, but if you change the seletion in the list box
programmatically, you will have to do a Me.ReCalc to update the control
source property of the text box. (I still have scars on my head from all
the scratching figuring that one out)

Thank you Dave! The textbox did the trick.

- Max