Microsoft Access Tips for Serious Users

Provided by Allen Browne.


List Box of Available Reports

In some applications you may wish to offer the user the choice to print any saved report. It would be handy to have Access fill the names of all reports into a list box for the user to select and print. Here are two solutions.

Method 1: Query the Hidden System Table (undocumented)

This very simple approach queries the hidden system table Access itself uses to keep track of your reports. The danger of an undocumented approach is that there is no guarantee it will work with future versions. Since Microsoft has announced that the current version (4) is the last version of JET they will release, that problem seems rather academic.

So, all you need do is copy this SQL statement and paste it into the RowSource of your list box:

    SELECT [Name] FROM MsysObjects
    WHERE (([Type] = -32764) AND ([Name] Not Like "~*") AND ([Name] Not Like "MSys*"))
    ORDER BY [Name];

When an object is deleted but before the mdb is compacted, it is marked for deletion and assigned a name starting with "~". The query skips those names.

Should you need to query other object types, the values for MSysObjects.Type are:

Table 1
Query 5
Form -32768
Report -32764
Module -32761

Method 2: Callback Function

If you don't like the undocumented approach, or would like to experiment with call back functions, here is the other alternative.

  1. Create a form with:

  2. Set the command button's OnClick property to [Event Procedure]. Click the "..." button beside this property to open the code window, and enter the following:
    Private Sub cmdOpenReport_Click()
        ' Purpose:  Opens the report selected in the list box.
        On Error GoTo cmdOpenReport_ClickErr
        If Not IsNull(Me.lstReports) Then
            DoCmd.OpenReport Me.lstReports, IIf(Me.chkPreview.Value, acViewPreview, acViewNormal)
        End If
        Exit Sub
    
    cmdOpenReport_ClickErr:
        Select Case Err.Number
        Case 2501   ' Cancelled by user, or by NoData event.
            MsgBox "Report cancelled, or no matching data.", vbInformation, "Information"
        Case Else
            MsgBox "Error " & Err & ": " & Error$, vbInformation, "cmdOpenReport_Click()"
        End Select
        Resume Next
    End Sub

  3. Set the list box's RowSourceType property to EnumReports. Leave the RowSource property blank.

  4. Create a new module and copy the function below into this module:
    Function EnumReports(fld As Control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant
        ' Purpose:  Supplies the name of all saved reports to a list box.
        ' Usage:    Set the list box's RowSourceType property to:? EnumReports
        '               leaving its RowSource property blank.
        ' Notes:    All arguments are provided to the function automatically.
        ' Author:   Allen Browne        allen@allenbrowne.com    Feb.'97.
    
        Dim db As Database, dox As Documents, i As Integer
        Static sRptName(255) As String              ' Array to store report names.
        Static iRptCount As Integer                 ' Number of saved reports.
    
        ' Respond to the supplied value of "code".
        Select Case code
            Case acLBInitialize                     ' Called once when form opens.
                Set db = CurrentDb()
                Set dox = db.Containers!Reports.Documents
                iRptCount = dox.Count               ' Remember number of reports.
                For i = 0 To iRptCount - 1
                    sRptName(i) = dox(i).Name       ' Load report names into array.
                Next
                EnumReports = True
            Case acLBOpen
                EnumReports = Timer                 ' Return a unique identifier.
            Case acLBGetRowCount                    ' Number of rows
                EnumReports = iRptCount
            Case acLBGetColumnCount                 ' 1 column
                EnumReports = 1
            Case acLBGetColumnWidth                 ' 2 inches
                EnumReports = 2 * 1440
            Case acLBGetValue                       ' The report name from the array.
                EnumReports = sRptName(row)
            Case acLBEnd
                Erase sRptName                      ' Deallocate array.
                iRptCount = 0
        End Select
    End Function

How the callback function works

The RowSourceType property of a list box can be used to fill the box programmatically. The five arguments for the function are provided automatically: Access calls the function repeatedly using these arguments to indicate what information it is expecting.

During the initialization stage, this function uses DAO (Data Access Objects) to retrieve and store the names of all reports into an static array. (Note: it is necessary to use the Containers!Reports.Documents collection, as the Reports object refers only open reports.)

The command button simply executes an OpenReport action. If the check box is checked, the report is opened in Preview mode, else it is printed directly.


Home Index of tips Top