Home  |  About  |   Search   

What's New
Table Of Contents
Credits
Netiquette
10 Commandments 
Bugs
Tables
Queries
Forms
Reports
Modules
APIs
Strings
Date/Time
General
Downloads
Resources
Search
Feedback
mvps.org

RunCommand Constants

Terms of Use


 

Reports: Use Multiselect listbox to limit records in report

Author(s)
Dev Ashish

(Q)    How can I have my report print only the records that I select in a multi-select listbox?

(A)    One of the ways would be to enumerate the ItemsSelected collection and modify the report's underlying Query to reflect the selections.

    For example, I have a listbox (multiselect of course) on a   form along with a text field txtCriteria. The bound column in listbox is an   Autonumber field. Then I have a button which creates the Criteria, sets the txtCriteria on form to this value (purely for viewing, you can directly pass it to the query), changes the SQL of QueryDef, and opens the query itself. The code looks something like this

'************ Code Start **********
Private Sub btnTestQuery_Click()
Dim vItm As Variant
Dim stWhat As String
Dim stCriteria As String
Dim stSQL As String
Dim loqd As QueryDef

    stWhat = "":    stCriteria = ","
    For Each vItm In Me!mslbxTest.ItemsSelected
        stWhat = stWhat & Me!mslbxTest.ItemData(vItm)
        stWhat = stWhat & stCriteria
    Next vItm
    Me!txtCriteria = CStr(left$(stWhat, Len(stWhat) - Len(stCriteria)))
    Set loqd = CurrentDb.QueryDefs("qryMultiSelTest")
    stSQL = "SELECT EmployeeID, LastName, FirstName, TitleOfCourtesy, "
    stSQL = stSQL & "Title FROM Employees WHERE EmployeeID"
    stSQL = stSQL & " IN (" & Me!txtCriteria & ")"
    loqd.SQL = stSQL
    loqd.Close
    DoCmd.OpenQuery "qryMultiSelTest"
End Sub
'************ Code End  **********

1998-2009, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer