This example assumes you want to create a
report called "labels allcontacts" with criteria based on a list box
called "ContactCategoryTxt". You need to create the report first with a
query attached to it called "labelsqry". The SQL statement for the query
is "SELECT allcontacts.*, allcontacts.ContactCategory FROM allcontacts".
If you want to use the multiselect box only
for Query criteria (not for report), just remove the references to the
report in the code and run a query instead.
To use a multiselect List Box for Query
or Report criteria, use the following steps:
1. Create a popup "query-by-form" form with
a list box (in this example "ContactCategoryTxt"). Save it as "AllContactsLabels"
2. The List box should have the "Multi
Select" property set to "Simple".
3. Create a button to run the query or the
report (in this example the button is called "PreviewLabelsCmd")
4. Attach the following code to the button:
DoCmd.Close acReport, "labels allcontacts",
acSaveNo
Dim MyDB As Database
Dim qdf As QueryDef
Dim i As Integer, strSQL As String
Dim strWhere As String, strIN As String
Dim flgAll As Boolean
Set MyDB = CurrentDb()
strSQL = "SELECT allcontacts.*, allcontacts.ContactCategory FROM
allcontacts"
'or, for more limited fields: strSQL = "SELECT allcontacts.ContactCategory,
allcontacts.Firstname, allcontacts.LastName, allcontacts.Company FROM
allcontacts"
'create the IN string by looping through the listbox
For i = 0 To ContactCategoryLst.ListCount - 1
If ContactCategoryLst.Selected(i) Then
If ContactCategoryLst.Column(0, i) = "All" Then
flgAll = True
End If
strIN = strIN & "'" & ContactCategoryLst.Column(0, i) & "',"
End If
Next i
'create the WHERE string and removing the last comma of the IN string
strWhere = " WHERE [ContactCategory] in (" & Left(strIN, Len(strIN) - 1) &
") "
'optional AND
'AND ((allcontacts.contactdate) Between [Forms]![allcontactslabels]![ComboDatesFrom]
And [Forms]![allcontactslabels]![ComboDatesTo]);"
'IN case you added an "All" value to your list box, also put the following
code, to remove the WHERE condition
If Not flgAll Then
strSQL = strSQL & strWhere
End If
MyDB.QueryDefs.Delete "labelsqry"
Set qdf = MyDB.CreateQueryDef("labelsqry", strSQL)
DoCmd.OpenReport "labels allcontacts", acPreview
DoCmd.Maximize
|