Multiselect

[yolly]

I have a form(bound to a query)that has a multiselect listbox(gets
it's values from a table called states). I have a field named states
in the table that the query is based on. I would like to populate
this field with several selections from the listbox. My first
question is it okay to have it bound to a select query and not the
table? I'm assuming I have to use code, but I have no Idea where to
start. How do I populate the table? I'm using Access 97.

Thanks In Advance

 

[BSman]

Basically, once the user has selected the states of interest from your listbox, you need to filter your form by creating a filter that looks something like this:

[State} IN ('NY','NJ','AZ')

On the on update property of your listbox, put something like the following code:

me.filteron = true
' strFilter = (build the string as shown in the example above)
me.filter = strFilter
me.requery

 

[yolly]

Do I need to declare something? Because how does this "strFilter = (build the string as shown in the example above)" get populate with other values.

 

[BSman]

dim strFilter as string

Using a list box to build your filter statement is a little messy (combo box that you keep selecting is easier), but here goes.

You basically have to determine which value(s) have been selected and then use them to build the string.


dim intX as integer
for intX = 0 to (number of items in list minus 1)
if me.ListBox.Selected(intX) = True then
strFilter = strFilter & "," & (put the value in here)
end if
next intX

From that you can build your filter correctly. Be sure to enclose the list with () and put single quotes around text values and # around date values.

 

[hkaing79]

a slight modification to BSman's code:

dim i as variant
dim ctl as control

ctl=me.listbox

for each i in ctl.ItemsSelected
strFilter = strFilter & "," & (put value here)
next i