Normally to return a set of rows based on a value list you'd use the IN
operator. Unfortunately this does not accept a parameter as its argument,
however. A number of ways of simulating it have been produced, which you'll
find at:
http://support.microsoft.com/kb/100131/en-us
If you use the Inparam and GetToken functions for instance the WHERE clause
of the query would be like this:
WHERE InParam([FA_NO],[Enter FA numbers as a list separated by commas:]) =
TRUE
However, you might like to consider an alternative approach which does not
involve the use of any parameters in the query. For this create an unbound
dialogue form and add a list box, lstFANumbers say, to it which lists all FA
numbers in order. Set the list box's MultiSelect property to either Simple
or Extended as preferred.
Add a command button to the form to open your report or form (or two
button's, one for each) based on the query (now with no parameters). The
code for this, to open a form called frmRooms, would be:
Dim varItem As Variant
Dim strFANumbersList As String
Dim strCriteria As String
Dim ctrl As Control
Set ctrl = Me.lstFANumbers
' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strFANumbersList = strFANumbersList & "," & ctrl.ItemData(varItem)
Next varItem
' remove leading comma
strFANumbersList = Mid(strFANumbersList, 2)
strCriteria = "FA_NO In(" & strFANumbersList & ")"
DoCmd.OpenReport "rptRooms", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No FA Numbers Selected", vbInformation, "Warning"
End If
The above assumes that the FA_NO column is a number data type. If it’s a
text data type amend the code as follows to wrap each value in quotes:
For Each varItem In ctrl.ItemsSelected
strFANumbersList = strFANumbersList & ",""" & ctrl.ItemData(varItem)
& """"
Next varItem
If you also want a button on the form to clear all the selections in the
list box use the following code for its Click event procedure:
Dim n As Integer
For n = 0 To Me.lstFANumbers.ListCount - 1
Me.lstFANumbers.Selected(n) = False
Next n
Ken Sheridan
Stafford, England
I would like to know how to enter several parameter values to produce several
reports/forms instead of just one. Example: to produce a form, the user gets
prompted to "Enter Fixed Asset Number that needs a Form 140." How can I set
it up to generate several forms instead of one.
