databasedev.co.uk - database solutions and downloads for microsoft access

Generating a Row Source from Microsoft Access system table MsysObjects

If you need to populate a listbox or combo box with the names of the database objects contained within your database you can query the Microsoft Access system table - MsysObjects.

For a complete list of details regarding this please see the following article: Returning database object names at The Access Web.

The following details using this procedure to open up reports detailed within a list box, which brings back a list of all reports contained within the database.

The image below shows the listbox that is populated using a row source generated by SQL:

The form showing the listbox with a list of reports contained within the database reports objects.

The row source for this listbox (named "lstReports") is as follows:

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~")
AND (MSysObjects.Type)= -32764 ORDER BY MSysObjects.Name;

The example also allows the user to choose a report from the list and double-click to open the associated report. This uses the following function:

Function ShowReports(strReportName As String, frm As Form) As Integer
On Error Resume Next

Dim strWhereCond As String
strWhereCond = "MyField = " & frm!MyField

DoCmd.OpenReport strReportName, acViewPreview, , strWhereCond

        If Err > 0 Then
        'OpenReport failed
            MyFunction = False
        Else
            MyFunction = True
        End If
    Exit Function
End Function

The function is executed from the double-click event procedure of the ListBox using:

Private Sub lstReports_DblClick(Cancel As Integer)
    ShowReports lstReports.Value, Forms!frmSelectObject
End Sub

This will open the report in Print Preview as defined in the Function.

The example database showing the listbox and also a combo box is available to download from the Microsoft Access Forms page or the Microsoft Access Downloads page.