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.
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.