Provided by Allen Browne.
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.
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
If you don't like the undocumented approach, or would like to experiment with call back functions, here is the other alternative.
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
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 firstname.lastname@example.org 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
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|