You can do this with a List box but not a Combo box.
A multi-select list box as June7 noted.
I use this on a Form:
Code:
SELECT [VendorNumber] AS VendID, Last([VendorName]) AS Name FROM PO1_PurchaseOrderEntryHeader GROUP BY [VendorNumber] ORDER BY Last([VendorName]) UNION select " ALL" AS VendID, "ALL VENDORS" AS Name from PO1_PurchaseOrderEntryHeader;
Then an Event Procedure on the Form to Export the select Vendors in a query using the IN clause.
Code:
Private Sub cmdRunReport_Click()
On Error GoTo Err_cmdRunReport_Click
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 * FROM PO1_PurchaseOrderEntryHeader"
'create the IN string by looping thru the listbox
For i = 0 To VendorSelector.ListCount - 1
If VendorSelector.Selected(i) Then
If VendorSelector.Column(0, i) = " All" Then
flgAll = True
End If
strIN = strIN & "'" & VendorSelector.Column(0, i) & "',"
End If
Next i
'create the WHERE string, stripping off the last comma of the IN string
strWhere = " WHERE [VendorNumber]in (" & left(strIN, Len(strIN) - 1) & ")"
'if "All" was selected, don't add the WHERE condition
If Not flgAll Then
strSQL = strSQL & strWhere
End If
MyDB.QueryDefs.Delete "PO1"
Set qdf = MyDB.CreateQueryDef("PO1", strSQL)
DoCmd.RunMacro "Process"
Exit_cmdRunReport_Click:
Exit Sub
Err_cmdRunReport_Click:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on the next line
ElseIf Err.Number = 5 Then
MsgBox "You must make at least one selection"
Resume Exit_cmdRunReport_Click
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_cmdRunReport_Click
End If
End Sub