Two multi-select listboxes

  • Thread starter Thread starter carljonas
  • Start date Start date
C

carljonas

Dear honoured Expertise,
I´m struggling with the syntax below. My setup is a form with two
multi-select list boxes. A button click sends the details to a label
print out. I can get them to work separately but when I try to combine
them into a single query I run into troubles. The line "qdf.SQL =
strSQL" fails me. The script frame work is from the net and adapted
for my purpose. Perhaps someone can explain why the line "strCriteria
= Left(strCriteria, Len(strCriteria) - 3)" is needed? Any advice would
be most grateful.

Private Sub Kommandoknapp32_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strCriteria2 As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryUtskrift")

If Me!SelectCollector.ItemsSelected.Count > 0 Then
For Each varItem In Me!SelectCollector.ItemsSelected
strCriteria = strCriteria & "Utskrift_etikett_små_stor.Collector = " &
Chr(34) & Me!SelectSamling.ItemData(varItem) & Chr(34) & "OR "

Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
strCriteria = "Utskrift_etikett_små_stor.Collector Like '*'"

End If

If Me!SelectSamling.ItemsSelected.Count > 0 Then
For Each varItem In Me!SelectSamling.ItemsSelected
strCriteria2 = strCriteria &
"Utskrift_etikett_små_stor.Donator = " & Chr(34) & Me!
SelectSamling.ItemData(varItem) & Chr(34) & "OR "

Next varItem
strCriteria2 = Left(strCriteria2, Len(strCriteria2) - 3)
Else
strCriteria2 = "Utskrift_etikett_små_stor_Donator Like '*'"
End If

strSQL = "Select * FROM Utskrift_etikett_små_stor " & "WHERE " &
strCriteria & " AND " & strCriteria2 & "; "

qdf.SQL = strSQL

DoCmd.OpenQuery "qryUtskrift"

Set db = Nothing
Set qdf = Nothing

End Sub

Thanks in advance // Jonas
 
You're referencing Me!SelectSamling instead of Me!SelectCollector for
strcriteria
You should also add ( & ) when concatenating the data
ie

strSQL = "Select * FROM Utskrift_etikett_små_stor " & "WHERE (" &
strCriteria & ") AND ( " & strCriteria2 & "); "

Pieter

Dear honoured Expertise,
I´m struggling with the syntax below. My setup is a form with two
multi-select list boxes. A button click sends the details to a label
print out. I can get them to work separately but when I try to combine
them into a single query I run into troubles. The line "qdf.SQL =
strSQL" fails me. The script frame work is from the net and adapted
for my purpose. Perhaps someone can explain why the line "strCriteria
= Left(strCriteria, Len(strCriteria) - 3)" is needed? Any advice would
be most grateful.

Private Sub Kommandoknapp32_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strCriteria2 As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryUtskrift")

If Me!SelectCollector.ItemsSelected.Count > 0 Then
For Each varItem In Me!SelectCollector.ItemsSelected
strCriteria = strCriteria & "Utskrift_etikett_små_stor.Collector = " &
Chr(34) & Me!SelectSamling.ItemData(varItem) & Chr(34) & "OR "

Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
strCriteria = "Utskrift_etikett_små_stor.Collector Like '*'"

End If

If Me!SelectSamling.ItemsSelected.Count > 0 Then
For Each varItem In Me!SelectSamling.ItemsSelected
strCriteria2 = strCriteria &
"Utskrift_etikett_små_stor.Donator = " & Chr(34) & Me!
SelectSamling.ItemData(varItem) & Chr(34) & "OR "

Next varItem
strCriteria2 = Left(strCriteria2, Len(strCriteria2) - 3)
Else
strCriteria2 = "Utskrift_etikett_små_stor_Donator Like '*'"
End If

strSQL = "Select * FROM Utskrift_etikett_små_stor " & "WHERE " &
strCriteria & " AND " & strCriteria2 & "; "

qdf.SQL = strSQL

DoCmd.OpenQuery "qryUtskrift"

Set db = Nothing
Set qdf = Nothing

End Sub

Thanks in advance // Jonas
 
Dear Pieter,
Many thanks for your help and for pointing out my mistakes. As far as
I can find out your suggestion to add (&) when concatenating the data
doesn´t seem to change the outcome. Perhaps including the brackets
doesn´t make any difference to the code in this circumstance? Here´s
my final code:

Private Sub MultiSelect()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strCriteria2 As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryUtskrift")

If Me!SelectCollector.ItemsSelected.Count > 0 Then
For Each varItem In Me!SelectCollector.ItemsSelected
strCriteria = strCriteria &
"Utskrift_etikett_små_stor.Collector = " & Chr(34) & Me!
SelectCollector.ItemData(varItem) & Chr(34) & "OR "

Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
strCriteria = "Utskrift_etikett_små_stor.Collector Like '*'"

End If

If Me!SelectSamling.ItemsSelected.Count > 0 Then
For Each varItem In Me!SelectSamling.ItemsSelected
strCriteria2 = strCriteria2 &
"Utskrift_etikett_små_stor.Donator = " & Chr(34) & Me!
SelectSamling.ItemData(varItem) & Chr(34) & "OR "

Next varItem
strCriteria2 = Left(strCriteria2, Len(strCriteria2) - 3)
Else
strCriteria2 = "Utskrift_etikett_små_stor.Donator Like '*'"
End If


' Build the new SQL statement incorporating the string

strSQL = "Select * FROM Utskrift_etikett_små_stor " & "WHERE " &
strCriteria & " AND " & strCriteria2 & "; "
' strSQL = "Select * FROM Utskrift_etikett_små_stor " & "WHERE ("
& strCriteria & ") AND (" & strCriteria2 & "); " ' From Pieter Wijnen

qdf.SQL = strSQL
DoCmd.OpenReport "Etikett_Sma_Nya", acViewPreview

Set db = Nothing
Set qdf = Nothing

End Sub

Kind regards // Jonas
 
Forgive my ignorance, but you've commented out the modified line?
Also I'd rather have used the In Operator instead of the OR's


For Each varItem In Me.SelectCollector.ItemsSelected
strCriteria = strCriteria & ",'" &
Me.SelectCollector.ItemData(varItem) & "'"
Next
If Me.SelectCollector.ItemsSelected.Count > 0 Then
strCriteria = "Collector In (" & VBA.Mid(strCriteria,2) & ")"
End If

For Each varItem In Me.SelectSamling.ItemsSelected
strCriteria2 = strCriteria2 & ",'" &
Me.SelectSamling.ItemData(varItem) & "'"
Next
If Me.SelectSamling.ItemsSelected.Count > 0 Then
strCriteria2 = "Donator In (" & VBA.Mid(strCriteria2,2) & ")"
End If

If VBA.Len(strCriteria) > 0 And VBA.Len(strCriteria2) > 0 Then
strSQL = strCriteria & " AND " & strCriteria2
ElseIf VBA.Len(strCriteria) > 0 Then
strSQL = strCriteria
else
strSQL = strCriteria2
end If

If VBA.Len(strSQL) = 0 Then
DoCmd.OpenReport "Etikett_Sma_Nya", acViewPreview
Else
DoCmd.OpenReport "Etikett_Sma_Nya", acViewPreview,
WhereCondition:=strSQL
End if

HtH

Pieter

Dear Pieter,
Many thanks for your help and for pointing out my mistakes. As far as
I can find out your suggestion to add (&) when concatenating the data
doesn´t seem to change the outcome. Perhaps including the brackets
doesn´t make any difference to the code in this circumstance? Here´s
my final code:

Private Sub MultiSelect()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strCriteria2 As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryUtskrift")

If Me!SelectCollector.ItemsSelected.Count > 0 Then
For Each varItem In Me!SelectCollector.ItemsSelected
strCriteria = strCriteria &
"Utskrift_etikett_små_stor.Collector = " & Chr(34) & Me!
SelectCollector.ItemData(varItem) & Chr(34) & "OR "

Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
strCriteria = "Utskrift_etikett_små_stor.Collector Like '*'"

End If

If Me!SelectSamling.ItemsSelected.Count > 0 Then
For Each varItem In Me!SelectSamling.ItemsSelected
strCriteria2 = strCriteria2 &
"Utskrift_etikett_små_stor.Donator = " & Chr(34) & Me!
SelectSamling.ItemData(varItem) & Chr(34) & "OR "

Next varItem
strCriteria2 = Left(strCriteria2, Len(strCriteria2) - 3)
Else
strCriteria2 = "Utskrift_etikett_små_stor.Donator Like '*'"
End If


' Build the new SQL statement incorporating the string

strSQL = "Select * FROM Utskrift_etikett_små_stor " & "WHERE " &
strCriteria & " AND " & strCriteria2 & "; "
' strSQL = "Select * FROM Utskrift_etikett_små_stor " & "WHERE ("
& strCriteria & ") AND (" & strCriteria2 & "); " ' From Pieter Wijnen

qdf.SQL = strSQL
DoCmd.OpenReport "Etikett_Sma_Nya", acViewPreview

Set db = Nothing
Set qdf = Nothing

End Sub

Kind regards // Jonas
 
Hi Pieter,
thanks once again for your splendid advice. I´m aware I did comment
out the modified line. It was just that I tried both code without any
seemingly difference in outcome. In my final version I applied your
code. This is on a high level for me, eg. I don´t understand the
meaning and use of the line with "VBA.Mid(strCriteria,2)". Anyhow the
code works perfectly.

My best // Jonas
 
The Mid Function takes out a portion of the string, in this case leaving out
the first character, which is a superfluous comma.
as we want everything but the leading comma in this case, we leave out the
third criteria for the mid function.
to learn more about VBA functions, either open any module or the immediate
window & press F1.
If you want help on a specific function, place the cursor in the word
(function) & help will open for the function (or other keyword - Recordset
for instance)
Pressing Shift + F2 will bring you to the library definition.

Pieter

Hi Pieter,
thanks once again for your splendid advice. I´m aware I did comment
out the modified line. It was just that I tried both code without any
seemingly difference in outcome. In my final version I applied your
code. This is on a high level for me, eg. I don´t understand the
meaning and use of the line with "VBA.Mid(strCriteria,2)". Anyhow the
code works perfectly.

My best // Jonas
 
Back
Top