Queries based on multi select list boxes

[silverback]

Further to my posting about a table with a unique name, I don't think I'd need to do this if there's a way to solve this problem.

A form has a multi select list box which displays years. The user selects one or more years from this.
There's another multi select list box on the form and its contents are delivered by a query which has, as a criterion,
Year is "IN SELECT * from table"
Is there a way to set the query criterion so it uses not the contents of the table, but the selection(s) on the form itself?

In case I've confused : if the year selector was a combo box which only allowed one year to be selected, the criterion would be
Year = [Forms]![formname]![ChosenYear].
Can this be expanded to cover selection of more than one year?

Thanks
Silverback

[HansV]

See this old post.

[silverback]

Thanks, Hans.
We've built the WHERE clause and then assigned the whole SQL clause to the Row Source of the list box. It's working.
Silverback

[silverback]

See this old post.

Hans
I'm stuck seeing how to use this technique with a query - which doesn't have a where clause parameter on the DoCmd.OpenQuery interface.
Currently, I use the technique (together with other code you've given me in the past) to write the selections into a table; the selection criterion in the query is then IN (SELECT * from tablename). It all works fine -except my customer has a multi user environment. This means that the choices made by user1 can be overwritten by choices made by user2 if they happen to use this form at the same time.
I can't see how to dynamically create the list of employee names for the query's selection criterion.
What must I do to get a secure solution for a multi user environment, please?
Thanks
Silverback

[HansV]

See this post. The attachment to that post is missing, but hopefully the code is clear enough.

[silverback]

Hans
Thanks for that - very interesting technique. I've got it working, finally.
One of the things that took some time was the comparison : If lbx.ItemData(itm) = varValue Then
I couldn't get any true results and finally tracked it down to lbx.ItemData(itm) returning a string. This is puzzling as the bound column is the EmployeeID which is an autonum.
Q1 : Why does lbx.ItemData(itm) return "2" rather than 2?
Q2 I've put a CInt cast on, like this - If CInt(lbx.ItemData(itm)) = varValue Then - and it's working. Is CInt the right thing to have used?
Thanks
Silverback

[HansV]

The items in a list box are always strings, regardless of the underlying data type.
An AutoNumber field is of type Long, so even if you have fewer than 32,767 records (the limit for an Integer), it's better to use CLng instead of CInt.