Mailing Labels Problem

Question

I’ve run into a problem while creating a report that prints mailing labels.

I’ve created a report that prints labels for all the people whose names are listed in a list box on a form. I want also for it to be possible for users to select specific names in the list and print labels only for those selected people.

The RowSource property of the list box on the form is defined by a query and the MultiSelect property is set to Simple so that multiple names can be selected in the list.

The report that prints labels has its RecordSource property set to the same query as the RowSource property of the list box on the form so that labels are printed for all listed names.

My question is this: What do I need to do so that the report prints labels for only selected names in the list?

Answer 1

Place a command button on the form to open the report.

In the following, I'll assume that

• The list box is named lstPersons, and that its bound column corresponds to the number field PersonID.
• The command button is named cmdOpenReport.
• The report is named rptPersons.

The On Click event procedure for the command button would look like this:

Private Sub cmdOpenReport_Click()
    Dim strWhere As String
    Dim varItem As Variable
    For Each varItem In Me.lstPersons.ItemsSelected
        strWhere = strWhere & ", " & Me.lstPersons.ItemData(varItem)
    Next varItem
    If strWhere <> "" Then
        strWhere = "PersonID In (" & Mid(strWhere, 3) & ")"
    End If
    DoCmd.OpenReport ReportName:="rptPersons", View:=acViewPreview, _
        WhereCondition:=strWhere
End Sub

If the user has selected at least one person in the list box, the report will print labels for the selected persons only.

If the user hasn't selected anything in the list box, the report will print labels for ALL persons.

---

Regards, Hans Vogelaar

Answer 2

Thanks for the fast and specific response. Your suggestiion looks like it will do exactly what I need. I'll try it out and report back.

Answer 3

Hans:

Your suggestion seems to be sending me in the right direction. However, there appears to be something missing.

The following statement (based on your suggestion) correctly opens my report with labels for the two people with the specified values for the PersonID field

DoCmd.OpenReport “TempLabel”, acViewPreview, , “PersonID = 6456 OR PersonID = 3560”

So I know the report is basically okay.

I’m running into a problem when I try to replace the specific values for PersonID with values derived from selected items in the SelectedList list box.

My code is:

Private Sub PreviewButton_Click()

  Dim WhereString As String

  Dim WhereItem As Variant

  For Each WhereItem In SelectedList.ItemsSelected

    WhereString = WhereString & “ OR MemberID = “ & Forms![Mailing Labels].SelectedList.Column(1, WhereItem)

  Next WhereItem

  ‘ Remove OR at the beginning of the string

..WhereString = Right(WhereString, Len(WhereString) – 4)

..DoCmd.OpenReport “acViewPreview, “””” & WhereString & “”””

End Sub

That code results in labels for all people. The where condition is not effective.

I’ll appreciate any suggestions you might care to give for correcting the code

Answer 4

Hans:

I've just noticed an error in the code I previously sent you. The DoCmd.Open Report statement that I'm using is:

..DoCmd.OpenReport “acViewPreview, ,“””” & WhereString & “”””

The extra comma (not shown previously) to indicate the absence of a filter is there.

Answer 5

The lines

..WhereString = Right(WhereString, Len(WhereString) – 4)

..DoCmd.OpenReport “acViewPreview, “””” & WhereString & “”””

should be

    WhereString = Right(WhereString, Len(WhereString) – 4)
    WhereString = "PersonID = " & WhereString
    DoCmd.OpenReport "TempLabel", acViewPreview, , WhereString

---

Regards, Hans Vogelaar