CODEWITHSUNDEEP
ms-accessvbareportms-access-2013
Simon M
Simon M

Reputation: 163

Display multiple specific records in a report

Let's say I have a single table called "Customers". It contains 2 fields:

Name
Address

I want users to be able to select multiple records by selecting their names. For example something like having a list box containing all the names of the records in the database. I want users to be able to select multiple items like:

Dave Richardson
Bob Smith
Sophie Parker

And then only display records with these names in the report.

Upvotes: 1

Views: 138

Answers (2)

HansUp
HansUp

Reputation: 97101

You can use the WhereCondition option of the DoCmd.OpenReport Method to filter your report as needed.

Const cstrReport As String = "YourReportNameHere"
Dim custNames As String
Dim lItem As Variant
Dim strWhereCondition As String

With Me.yourListBoxName
    For Each lItem In .ItemsSelected
        custNames = custNames & ",'" & Replace(.ItemData(lItem), "'", "''") & "'"
    Next
End With

If Len(custNames) > 0 Then
    custNames = Mid(custNames, 2)
    strWhereCondition = "[Name] IN (" & custNames & ")"
End If

DoCmd.OpenReport ReportName:=cstrReport, View:=acViewPreview, _
    WhereCondition:=strWhereCondition

Note this approach has features in common with PaulFrancis' answer. In fact, I copied his code and modified it. The key difference is that this approach does not require you to revise a saved query in order to filter the report.

Upvotes: 3

PaulFrancis
PaulFrancis

Reputation: 5809

The setup I would I have is, a Form with a SubForm and a ListBox, the Listbox will have the names of all your customers. So the RowSource would be,

SELECT 
    customerNameFieldName
FROM
    yourTableName;

The Multi Select property will be setup to Extended. Then a button will have the following code that will generate the SQL for the SubForm's recordsource.

Private Sub buttonName_Click()
    Dim lItem As Varaint, strSQL As String
    Dim custNames As String, whereStr As String
    Dim dbObj As dao.Database
    Dim tmpQryDef As QueryDef

    Set dbObj = CurrentDb()

    For Each lItem In Me.yourListBoxName.ItemsSelected
        custNames = custNames & "'" & Me.yourListBoxName.ItemData(lItem) & "', "
    Next

    Id Len(custNames) <> 0 Then
        custNames = Left(custNames, Len(custNames) - 2)

        whereStr = "WHERE customerNameFieldName IN (" & custNames & ")"
    End If

    strSQL = "SELECT someFields FROM someTable " & whereStr

    Set tmpQryDef = dbObj.QueryDefs("Summary 3 Q1")
    tmpQryDef.SQL = strSQL

    DoCmd.OpenReport "yourReportName", acViewNormal

    Set dbObj = Nothing
    Set tmpQryDef = Nothing 
End Sub

So now the SubForm will have the RecordSource based on all the information you have selected in the ListBox.

Upvotes: 2

Related Questions