Microsoft Access Tips for Serious Users

Provided by Allen Browne, allenbrowne.com

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:
   • a list box named lstReports, with a label Reports;
   • a check box named chkPreview, with a label Preview;
   • a command button named cmdOpenReport, with caption Open Report.

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.