Large Access VBA list box

[mojo]

Hello everyone. I'm trying to figure out a problem for my work. I'm working in an Access form (DAO) and there is a field called "Target Group" in the database. There are about 40 target groups and the user needs to be able to select any combination/number of them to query the database with. Also, more target groups may be added to the database in the future. The database will be maintained by a person who does not know how to program after I leave. I would like to do this with list boxes since the person who works there after me can add new target groups without using VBA code, but I have no idea how to approach this because it seems too complex for a listbox. Any ideas of how to allow this kind of complex query with such a large number of choices?

[Ravi Kiran]

You can write a sql with a where clause. with OR operator. Something like; ( VB code)
Sqlstr = "Select * from " & TableName & _
" Where " & " [Targer Group] = '" & selecteditem1 & "' Or [Target Group]= ' & selecteditem2 & "' ".. etc

I would try like this:
List all the Allowed Target Groups in a list box with multi selection. This group can change with changing needs. This data comes from a different (lookup) table.
After user selects one or more from this list,
copy all the selected to a temp array and build a sqlstring by looping thru all the selected items.
There is a limit on this ORed WHERE clause for JetDB, upto 40 items it seems(MSDN). So if you have more than 40 selections, may be you should try multiple recordsets or something.

For maintaining target groups is like changing records of that lookup table. You need to provide a simple Add/edit/update form for that table.

[Lothar Haensler]

instead of ORing 40 elements, you might want to consider using the IN clause as in:
select * from table where field in (value1, value2, value3...)