Multi Select List Box

[Lyncroft]

I've 2 multi-select list boxes. Once the user has made his selections from the boxes how can I get a message to appear saying what he's chosen (simillar to the QBF).

For Each varItem In Me.lstConsultation.ItemsSelected
sConsultation = sConsultation & ",'" & Me.lstConsultation.ItemData(varItem) & "'"
Next varItem
sConsultation = Mid(sConsultation, 2)
sConsultation = " Consultation in (" & sConsultation & ")"

For Each varItem In Me.lstCountry.ItemsSelected
sCountry = sCountry & ",'" & Me.lstCountry.ItemData(varItem) & "'"
Next varItem
sCountry = Mid(sCountry, 2)
sCountry = " [Country] in (" & sCountry & ")"

If Me.lstConsultation.ItemsSelected.Count > 0 And _
Me.lstCountry.ItemsSelected.Count > 0 Then
sCriteria = sConsultation & " AND " & sCountry
Else
sCriteria = IIf(Me.lstConsultation.ItemsSelected.Count > 0, sConsultation, sCountry)
End If

MsgBox "Select * from qryAll " & (" where "


SQL = " SELECT * " & _
" FROM qryAll " & _
" WHERE " & sCriteria

Set db = CurrentDbREQUIRED CODE FOR MESSAGE Required code

 

[rich.barry]

What field do you plan to put in the message?
Is it one of those displayed in the listbox?

It may be simpler to add the field to the query upon which the list box is based.....if you don't want to display it then set the column width to 0, then use the Column property in VBA to return the data for the message.

i.e.

For intCurrentRow = 0 To ctlSource.Listcount - 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & ctlSource.Column(datacolumn,intCurrentRow) & ";"
End If
Next intCurrentRow

where datacolumn is the stuff being added to your message (strItems)