Multiple Select Listbox in query by form

Question

There are so many examples of this question online, but I really can't figure out where to put in all of the code people are discussing, and how to customize it for my situation.

I have a form with multiple parameters, including a multiple select listbox.  I would like to use this form for a query.

My listbox:  ListAgeProgramStQuery on the form: frmStatusQueries

I understand that the issue is that the listbox doesn't store a concatenated value that can be read by the query, so that somewhere, you need to concatenate that value.  Do I do it:

• somewhere on the listbox? Where
• create a separate textbox that does the concatenation? Where on this textbox
• Within the query?  Where, the query has a number of parameters?

Answer 1

A multi-select list box has an ItemsSelected collection, which is a collection of variants which identify the selected rows.  A delimited list of the items selected is built by looping through the collection.  This can be done in the list box's AfterUpdate event procedure, which will progressively build the delimited list, but is more commonly done independently of the control, e.g. in a 'Confirm' button's  Click event procedure.

In a query a comma delimited list like this would normally be the operand for the IN operator.  However, the IN operator does not accept a parameter as its argument, only a literal string.  Microsoft have provided a method for simulating the use of the IN operator with a parameter, however, by means of InParam and GetToken functions (the former calls the latter).

The parameter in this case is a hidden text box control in the form, to which the delimited list is assigned by the code I referred to in my first paragraph.  You'll find an example of a list box used in this way in DatabaseBasics.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 the link (NB, not the link location) and paste it into your browser's address bar.

In this little demo file, if you go to the section on 'Retrieving data from the database' you'll find a form in which there is a list box of contacts, one or more of whom can be selected.  In the demo the query's result table is output in a report, so the code is in the Open Report button's Click event procedure:

Private Sub cmdOpenReport1_Click()

    Dim varItem As Variant

    Dim strContactIDList As String

    Dim ctrl As Control

    Set ctrl = Me.lstContacts

    If ctrl.ItemsSelected.Count > 0 Then

        ' if at least one item has ben selected in the list box

        ' loop through the control's ItemsSelected collection

        ' and build a value list of ContactID values

        For Each varItem In ctrl.ItemsSelected

            strContactIDList = strContactIDList & "," & ctrl.ItemData(varItem)

        Next varItem

        ' remove leading comma

        strContactIDList = Mid(strContactIDList, 2)

        ' assign value list to hidden text box control

        Me.txtContactIDList = strContactIDList

        DoCmd.OpenReport "rptContacts_1", View:=acViewPreview

    Else

        MsgBox "No contacts selected", vbInformation, "Warning"

    End If

End Sub

The report's query is as follows:

SELECT [FirstName] & " " & [LastName] AS FullName, Address, City, Region,

Country, Employer, LastName, FirstName, Contacts.ContactID

FROM (Countries INNER JOIN Regions ON Countries.CountryID = Regions.CountryID)

INNER JOIN (Employers INNER JOIN ((Cities INNER JOIN Contacts

ON Cities.CityID = Contacts.CityID) INNER JOIN ContactEmployers

ON Contacts.ContactID = ContactEmployers.ContactID)

ON Employers.EmployerID = ContactEmployers.EmployerID)

ON Regions.RegionID = Cities.RegionID

WHERE InParam(Contacts.[ContactID],[Forms]![frmReportDialogue]![txtContactIDList])=TRUE;

The InParam and Get Token functions can be found in the basInParam module:

' The functions in this module are published by Microsoft at:

' http://support.microsoft.com/kb/100131/en-us

' They are used in this demo database to simulte the use of the

' IN operator in a query, but by accepting a value list as a parameter

' rather than a literal value list as the IN operator requires.

' The parameter in this case is a hidden control in the frmReportDialogue form.

Option Compare Database

Option Explicit

Function GetToken(stLn, stDelim)

    Dim iDelim As Integer, stToken As String

    iDelim = InStr(1, stLn, stDelim)

    If (iDelim <> 0) Then

        stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))

        stLn = Mid$(stLn, iDelim + 1)

    Else

        stToken = LTrim$(RTrim$(Mid$(stLn, 1)))

        stLn = ""

    End If

    GetToken = stToken

End Function

'============================================================

' The InParam() function is the heart of this article. When

' the query runs, this function causes a query parameter

' dialog box to appear so you can enter a list of values.

' The values you enter are interpreted as if you

' had entered them within the parentheses of the In() operator.

'============================================================

Function InParam(Fld, Param)

    Dim stToken As String

    'The following two lines are optional, making queries

    'case-insensitive

    Fld = UCase(Fld)

    Param = UCase(Param)

    If IsNull(Fld) Then Fld = ""

        Do While (Len(Param) > 0)

        stToken = GetToken(Param, ",")

        If stToken = LTrim$(RTrim$(Fld)) Then

            InParam = -1

            Exit Function

        Else

            InParam = 0

        End If

    Loop

End Function

Simply copy and paste the module into your database.

While my demo outputs the data as a report, it would be a trivial amendment to the code to output it as abound form, or even to requery the current form if the list box is in the bound form itself.

Answer 2

Thank you so much for such a comprehensive reply.  I will see if I can make it work.  Thanks!