Help with multiple Select List box

[fhurman]

Hello, I apologize in advance for my ignorance. I set up a list box to allow
multiple selected items. I would like to use these selctions as a parameter
in a query and I was able to find some similar posts on this site. I was
thinking of using the language in
http://www.mvps.org/access/forms/frm0007.htm but basically have the results
of this language populate a text box or something that I can then reference
in a query. I tried the following code modified to my situation just as a
test.

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Form!Form1

Set ctl = frm!List0

For Each varItem In ctl.ItemsSelected
Text2 = strSQL & ctl.ItemData(varItem) & " OR "
Next varItem
End Sub

My dumb question is what is "Set frm"? I can't seem to figure that one out.
Also, do yuo think the rest of the code will work? I was going to try some
trial and error but got hung up already on the Set frm language.

Thanks for your help,
Mark

 

[Douglas J. Steele]

"Set frm" instantiates a variable named frm so that you can refer to frm
instead of having to refer to Form!Form1 everywhere. Similarly, "set ctl"
instantiates a variable named ctl so that you can refer to ctl instead of
Form!Form1!List0.

The advantage of using the variables is that it's much simpler to use the
same code elsewhere: you only have to change those two lines to refer to a
different control on a different form. (Of course, I suspect your Where
clause would be different, so it's not really that big a saving!)

That being said, your change to the code is incorrect. If you look at the
original code on the webpage, strSQL would become something like

Select * from Employees where [EmpID]=123 OR [EmpID]=234 OR [EmpID]=345

Your code, on the other hand, is only going to result in

123 OR 234 OR 345

which isn't valid.

 

[Stefan Hoffmann]

hi,

Hello, I apologize in advance for my ignorance.

Okay, done.

I would like to use these selctions as a parameter
in a query and I was able to find some similar posts on this site.

What kind of query and how do you like to use it?

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Form!Form1

Set ctl = frm!List0

For Each varItem In ctl.ItemsSelected
Text2 = strSQL & ctl.ItemData(varItem) & " OR "
Next varItem
End Sub

My dumb question is what is "Set frm"?

frm is an object type variable. VBA as language requires a mandatory Set
when assigning an object to a variable.
In opposite a value type. Here VBA defines an optional Let variable =
valueType.

Also, do yuo think the rest of the code will work? I was going to try some
trial and error but got hung up already on the Set frm language.

What kind of error do you get?

Basically you could simplify it:

Dim Conditional As String
Dim Item As Variant

For Each Item In frm.List0.ItemsSelected
Conditional = Conditional & " OR " & frm.List0.ItemData(Item)
Next Item
Conditional = Mid(Conditional , 5)

MsgBox Conditional


mfG
--> stefan <--