As Hans says, the best solution is to have a multi-select list box in which any number of items can be selected. You'll find an example as SearchDemo.zip in my public databases folder at:
https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169
Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.
If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.
This little demo file uses a simple database of stocks held by stores as an example. The option to 'Select Criteria for Report' opens a form with a multi-select list box and an option group which determines whether stores which hold all or any of the
selected items are returned. The code to execute the search after selecting items in the list box and either the 'all' or 'any' option is as follows:
Const REPORTCANCELLED = 2501
Const MESSAGETEXT = "No matching items to report."
Dim varItem As Variant
Dim strStockIDList As String
Dim strCriteria As String
Dim ctrl As Control
Set ctrl = Me.lstStock
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strStockIDList = strStockIDList & "," & ctrl.ItemData(varItem)
Next varItem
' remove leading comma
strStockIDList = Mid(strStockIDList, 2)
If optAnyOrAll = 1 Then 'any item stocked
strCriteria = "StoreID IN(SELECT StoreID " & _
"FROM StoreStock " & _
"WHERE StockID IN(" & strStockIDList & "))"
Else 'all items stocked
strCriteria = "StoreID IN(SELECT StoreID " & _
"FROM StoreStock " & _
"WHERE StockID IN(" & strStockIDList & ") " & _
"GROUP BY StoreID " & _
"HAVING COUNT(*) = " & ctrl.ItemsSelected.Count & ")"
End If
On Error Resume Next
DoCmd.OpenReport "rptStoreStocks", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Select Case Err.Number
Case 0
' no error
Case REPORTCANCELLED
' anticipated error
MsgBox MESSAGETEXT, vbInformation, "Warning"
Case Else
' unknown Error
MsgBox Err.Description, vbExclamation, "Error"
End Select
Else
MsgBox "No items selected", vbInformation, "Warning"
End If
In my case the button opens a report, but could equally well open a form or assign the value of the strCriteria value to the Filter property of a bound form in which the unbound list box is located.
Another way of restricting a result set by multiple values is to restrict a query as the RecordSource of a form or report. When restricting a query by an arbitrary set of literal values the IN operator would normally be used in the query, but this does
not accept a parameter as its argument, only a set of literal values. Microsoft has published InParam and GetToken functions to simulate the use of the IN operator with parameters at:
http://support.microsoft.com/kb/100131/en-us
You'll find illustrations of their use, amongst other methods, in the Multiselect.zip demo in my same OneDrive folder. Note that the use of these functions is more reliable than the other method at the above link, using the Instr function, as the latter
can return specious substring matches.
Ken Sheridan, Stafford, England
