Multi Select List Not Working

  • Thread starter Thread starter djf
  • Start date Start date
D

djf

The following code works perfectly for a multi select list. I've used it for
several years on many applications. I'm encountering a strange error however
on this code that takes criteria from three different lists and enters the
data into a query. The first two list's criteria work fine but the third list
has a strange quirk.

Here's what it is doing. Let's say the third list has choices, 1,2,3,4,5,6.
If I choose 3,4,5 in the list and run the code, it returns 1,4,5. If I choose
2,4,5,6 the code returns 1,4,5,6. It always returns the first value in my
list which in this case is 1 and replaces my first choice with 1 and then
returns the rest of my choices correctly.

Dim Q, R, S, T As QueryDef, DB As Database
Dim Criteria, Criteria2, Criteria3 As String
Dim ctl, ctl2, ctl3 As Control
Dim Itm, Itm2, Itm3 As Variant

' Build a list of the selections.
Set ctl = Me![lst1]
Set ctl2 = Me![lst2]
Set ctl3 = Me![lst3]

'work center list box selection

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm

If Len(Criteria) = 0 Then
Itm = MsgBox("You must select one or more" & _
" Work Centers!", 0, "No Selection Made")
Exit Sub
End If

'area list box selection

For Each Itm2 In ctl2.ItemsSelected
If Len(Criteria2) = 0 Then
Criteria2 = Chr(34) & ctl2.ItemData(Itm2) & Chr(34)
Else
Criteria2 = Criteria2 & "," & Chr(34) & ctl2.ItemData(Itm2) _
& Chr(34)
End If
Next Itm2

If Len(Criteria2) = 0 Then
Itm2 = MsgBox("You must select one or more" & _
" Areas!", 0, "No Selection Made")
Exit Sub
End If

'location list box selection

For Each Itm3 In ctl3.ItemsSelected
If Len(Criteria3) = 0 Then
Criteria3 = Chr(34) & ctl3.ItemData(Itm2) & Chr(34)
Else
Criteria3 = Criteria3 & "," & Chr(34) & ctl3.ItemData(Itm3) _
& Chr(34)
End If
Next Itm3

If Len(Criteria3) = 0 Then
Itm3 = MsgBox("You must select one or more" & _
" Locations!", 0, "No Selection Made")
Exit Sub
End If





' Modify the Query.
Set DB = CurrentDb()
Set Q = DB.QueryDefs("qsSampleBL2")
Set R = DB.QueryDefs("qsSampleList1")
Set S = DB.QueryDefs("qsSampleList2")
Set T = DB.QueryDefs("qsSampleList1A")

Q.SQL = "Select * From qsSampleBL1 Where [WORK_CENTER_ID] In(" & Criteria
& _
")and [WORK_AREA_ID] In(" & Criteria2 & ") and [LOCATION_ID] In(" &
Criteria3 & ");"

R.SQL = "Select * From tblSampleList1 Where [WORK_CENTER_ID] In(" &
Criteria & _
");"

S.SQL = "Select * From tblSampleList1 Where [WORK_AREA_ID] In(" &
Criteria2 & _
");"

T.SQL = "Select * From tblSampleList1 Where [Location_ID] In(" &
Criteria3 & ");"
 
Look at your code
For Each Itm3 In ctl3.ItemsSelected
If Len(Criteria3) = 0 Then
Criteria3 = Chr(34) & ctl3.ItemData(Itm2) & Chr(34)
Else
Criteria3 = Criteria3 & "," & Chr(34) & ctl3.ItemData(Itm3) _
& Chr(34)
End If
Next Itm3
Click to expand...

It's got Criteria3 = Chr(34) & ctl3.ItemData(Itm2) & Chr(34).

That should be Criteria3 = Chr(34) & ctl3.ItemData(Itm3) & Chr(34)

(Itm3, not Itm2)

I'll also point out that

Dim Q, R, S, T As QueryDef, DB As Database
Dim Criteria, Criteria2, Criteria3 As String
Dim ctl, ctl2, ctl3 As Control
Dim Itm, Itm2, Itm3 As Variant

doesn't do what you probably think it does. That declares T as a QueryDef,
but not Q, R or S: they're all Variants. It declares Criteria3 as a String,
but not Criteria or Criteria2: they're both Variants. VBA doesn't let you
"short circuit" declarations. You need:

Dim Q As QueryDef, R As QueryDef, S As QueryDef
Dim T As QueryDef, DB As Database
Dim Criteria As String, Criteria2 As String, Criteria3 As String
Dim ctl As Control, ctl2 As Control, ctl3 As Control
Dim Itm As Variant, Itm2 As Variant, Itm3 As Variant

(okay, so the last change really isn't necessary, since your declaration has
Itm and Itm2 as Variants!)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


djf said:
The following code works perfectly for a multi select list. I've used it
for
several years on many applications. I'm encountering a strange error
however
on this code that takes criteria from three different lists and enters the
data into a query. The first two list's criteria work fine but the third
list
has a strange quirk.

Here's what it is doing. Let's say the third list has choices,
1,2,3,4,5,6.
If I choose 3,4,5 in the list and run the code, it returns 1,4,5. If I
choose
2,4,5,6 the code returns 1,4,5,6. It always returns the first value in my
list which in this case is 1 and replaces my first choice with 1 and then
returns the rest of my choices correctly.

Dim Q, R, S, T As QueryDef, DB As Database
Dim Criteria, Criteria2, Criteria3 As String
Dim ctl, ctl2, ctl3 As Control
Dim Itm, Itm2, Itm3 As Variant

' Build a list of the selections.
Set ctl = Me![lst1]
Set ctl2 = Me![lst2]
Set ctl3 = Me![lst3]

'work center list box selection

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm

If Len(Criteria) = 0 Then
Itm = MsgBox("You must select one or more" & _
" Work Centers!", 0, "No Selection Made")
Exit Sub
End If

'area list box selection

For Each Itm2 In ctl2.ItemsSelected
If Len(Criteria2) = 0 Then
Criteria2 = Chr(34) & ctl2.ItemData(Itm2) & Chr(34)
Else
Criteria2 = Criteria2 & "," & Chr(34) & ctl2.ItemData(Itm2) _
& Chr(34)
End If
Next Itm2

If Len(Criteria2) = 0 Then
Itm2 = MsgBox("You must select one or more" & _
" Areas!", 0, "No Selection Made")
Exit Sub
End If

'location list box selection

For Each Itm3 In ctl3.ItemsSelected
If Len(Criteria3) = 0 Then
Criteria3 = Chr(34) & ctl3.ItemData(Itm2) & Chr(34)
Else
Criteria3 = Criteria3 & "," & Chr(34) & ctl3.ItemData(Itm3) _
& Chr(34)
End If
Next Itm3

If Len(Criteria3) = 0 Then
Itm3 = MsgBox("You must select one or more" & _
" Locations!", 0, "No Selection Made")
Exit Sub
End If





' Modify the Query.
Set DB = CurrentDb()
Set Q = DB.QueryDefs("qsSampleBL2")
Set R = DB.QueryDefs("qsSampleList1")
Set S = DB.QueryDefs("qsSampleList2")
Set T = DB.QueryDefs("qsSampleList1A")

Q.SQL = "Select * From qsSampleBL1 Where [WORK_CENTER_ID] In(" &
Criteria
& _
")and [WORK_AREA_ID] In(" & Criteria2 & ") and [LOCATION_ID] In(" &
Criteria3 & ");"

R.SQL = "Select * From tblSampleList1 Where [WORK_CENTER_ID] In(" &
Criteria & _
");"

S.SQL = "Select * From tblSampleList1 Where [WORK_AREA_ID] In(" &
Criteria2 & _
");"

T.SQL = "Select * From tblSampleList1 Where [Location_ID] In(" &
Criteria3 & ");"
Click to expand...
 
Back
Top