Multiple multiselect listboxes

[SnowPatrol]

Hi, my problems are numerous!
I have a form with a number of multiselect listboxes. Their sources are individual tables such as YEARS, PARISHES, CLIENTS. The underlying data is stored in a separate table. I want users to be able to select none, one or some values from each listbox and run a report.
As simple listboxes with one selection permitted this worked fine. I know that my problem is in concantenating the values.

I have tried attaching some code to the OnClick function of a cmd button on the form. I found this code referred to a number of times here and have tried to adapt it but no luck. I have now reduced my form to one listbox to test stuff out- this is the code-

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Form!testform
Set ctl = frm!lboparish
strSQL = "Select * from StJamesAgric where [Parish]="
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [Parish]="
Next varItem

'Trim the end of strSQL
strSQL=left$(strSQL,len(strSQL)-300)

The first error I get is "can't find field 'testform'"

My form is called testform
The listbox is called lboparish
The table with all the data is called StJamesAgric

What am I doing wrong???? And also, if this code EVER works, do I need to repeat it for each listbox or do I insert refernces to each list box within just one subcommand?

Any help greatly appreciated.

Stella

 

[RuralGuy]

The reason you are getting the error is the collection is the Forms collection, not the Form collection so the line should read: Set frm = Forms!testform. Post back when you get to another error you have trouble getting by.

 

[SnowPatrol]

Hi RB, I sorted the initial problem with some other code. (see below)
The multiselection now works..However (there is always a however...) my fears for what would become of me when I attempted to introduce a second listbox were well-founded!

I adapted the code

Private Sub cmdOK_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")
If Me!lstRegions.ItemsSelected.Count > 0 Then
For Each varItem In Me!lstRegions.ItemsSelected
strCriteria = strCriteria & "tblData.Region = " & Chr(34) _
& Me!lstRegions.ItemData(varItem) & Chr(34) & "OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
strCriteria = "tblData.Region Like '*'"
End If
strSQL = "SELECT * FROM tblData " & _
"WHERE " & strCriteria & ";"
qdf.SQL = strSQL
DoCmd.OpenQuery "qryMultiSelect"
Set db = Nothing
Set qdf = Nothing
End Sub

But, was not sure how to adapt it to accept a second or third listbox.
I added another "If.../End If" argument but that didn't work!

Many thanks

Stella

 

[RuralGuy]

Probably something like:
Warning <<<AIR CODE>>>

If Me!LB1.ItemsSelected.Count + _
   Me!LB2.ItemsSelected.Count + _
   Me!LB3.ItemsSelected.Count  > 0 Then
   If Me!LB1.ItemsSelected.Count > 0 Then
      For Each varItem In Me!LB1.ItemsSelected
      ...Put them together...
      Next varItem
   End If
   If Me!LB2.ItemsSelected.Count > 0 Then
      For Each varItem In Me!LB2.ItemsSelected
      ...Put them together...
      Next varItem
   End If
   If Me!LB3.ItemsSelected.Count > 0 Then
      For Each varItem In Me!LB3.ItemsSelected
      ...Put them together...
      Next varItem
   End If
   strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
   strCriteria = "tblData.Region Like '*'"
End If

 

[SnowPatrol]

Hi RG- forgive me for being dumb- I am new here....is air code like air guitar! Kind of made up?!

Not that I have any problem trying out made up code (nothing works anyway!)

I just thought this must be a really common piece of code. Perhaps i am going about it the wrong way?????

Stella

 

[RuralGuy]

Hi Stella,
Air Code is UNTESTED code. It may not even get by the compiler. I would have to have created a form with three ListBoxes on it to test this code. Since I didn't do that, I thought I'd warn you that it was an Air Guitar...ahum, I mean Air Code.