How to use a List Box to limit records printed in a Report

How to use a List Box to limit records printed in a Report

As well as wanting to only print a single record in a Report, you might also want to be able to print several. Whilst it is possible to navigate to each record in the Form, and then print the report individually, there is an easier way in Access, by using a List Box that has its Multi-Select property set to either Simple or Extended.

Create a new form, and add a List Box to this Form, lstSelect, resizing it so that it displays a large number of rows. Change the Multi Select to either Simple or Extended. Set the RowSource of this List Box to be based on the Table/Query that you want to be printed, and ensure that the Bound Column is a unique value for the RowSource.

Next, add a Command Button to this Form, cmdPrintSelection. In the OnClick event for this control, type the following:

Private Sub cmdPrint_Click()
    Dim varSelected As Variant
    Dim strSQL As String
    For Each varSelected In Me!lstSelect.ItemsSelected
        strSQL = strSQL & Me!lstSelect.ItemData(varSelected) & ","
    Next varSelected
    If strSQL <> "" Then
        strSQL = "[Centre] IN (" & Left(strSQL, Len(strSQL) - 1) & ")"
        DoCmd.OpenReport "rptEntry", acViewNormal, , strSQL
    End If
End Sub

This will now print the limited records out. If there is no data selected from the List Box, no Report will be produced - you can obviously change this to print the whole Report if you want.

Top

Last modified at 06/06/2006 14:54:05