Having choices is nice. We all like choices–even 2 year olds! We are going to give you a choice. You can choose from two different ways to fill a Listbox with a list of all the Reports in your Access Database. You can choose an slightly easier undocumented way to fill the listbox with a query or you can choose the other method using a User Defined Function. “Undocumented” means Microsoft has released no information indicating this is an approved method. When we are finished with this series on how to build a Report Date Dialog Form it should look something like this:
The Listbox of Report names will appear the same with either method you choose.
The first method is an undocumented way to fill a listbox with the names of your Reports. It has been a popular choice for a number of years probably because it is a little easier to use, sort, and filter.
The uncodumented method queries the hidden System Table “MSysObjects” which stores the names of just about everything in your database, along with a number of other fields of system data. Normally you cannot see the MSysObjects table because it is hidden. To see it, as well as the rest of the hidden objects in your database use the Menu ToolBar and click “Tools” and then “Options”. When the Options Dialog appears click the “View” tab and you will see the “System objects” checkbox.
Check the “Systems objects” checkbox and you will be able to see all of the System Tables. If you check the “Hidden objects” checkbox just above it you will also be able to see any objects set with the “Hidden” property.
Now you will be able to see the MSysObjects table when you use the Query grid, which is the next step. Open the new Query Wizard and select “Design View” and then click “OK”. You can now select the MSysObjects table, click “Add” and then “Close”. Scroll through the list of fields in the table until you see the “Name” field and add it to the Query grid. Next, scroll down some more and add the “Type” field to the grid.
We need to add a criteria so we do not have all of the numerous objects in the MSysObjects table listed. In the Criteria row under the Type field enter -32764. That will limit the list to all the Reports in the database. For those who want the SQL it is: “SELECT MSysObjects.Name, MSysObjects.Type FROM MSysObjects WHERE (((MSysObjects.Type)=-32764));”
Save the query and enter its name in the Listbox RowSource and your listbox will be filled with every Report in your Database. It will also work the same for all of your other Databases.
Some people do not like using undocumented programming methods such as the one just outlined. They are concerned that the next version of Acccess will not have the undocumented method or even a Microsoft Update might disable the method. They may be right. So we will now look at the method that is not undocumented.
This method uses the CurrentProject.AllReports Collection to list the names. That is fairly easy. But how do we get the Report names formatted properly and placed in our listbox? It turns out that will not be very hard either. Let us look at the code and then we will explain it.
'--------------------------------------------------------------------------------------- ' Procedure : GetReportList ' DateTime : 2/23/2009 ' Author : Patrick Wood ' Purpose : Get a List of all Reports to use in a combobox or listbox value list '--------------------------------------------------------------------------------------- ' Public Function GetReportList() As String Dim rpt As AccessObject Dim strRptList As String For Each rpt In CurrentProject.AllReports If Len(strRptList & vbNullString) > 0 Then strRptList = strRptList & ";" & rpt.Name Else 'This is the first report strRptList = rpt.Name End If Next rpt GetReportList = strRptList End Function
What this code is doing is looping through the CurrentProject.AllReports Collection, getting all of the Report names and putting them together in a long list of Report names using semicolons ; as separators. No space is left between the Report names and the semicolons.
The easiest way we have found to produce the list is to make sure we do not have any extra starting or trailing semicolons from the start so there is no need for code to trim any extra semicolons. The following line of code uses the Len Function, along with vbNullString, to get the length of the variable value: If Len(strRptList & vbNullString) > 0 Then
The first time the code loops through the collection of Reports, strRptList will not be holding a value so it will have no length. Adding VbNullString means that the length will at least be 0 and is used to ensure that there is not an error caused by a null or zero length string. If the value is not greater that 0 this is the first time through the loop. So the code goes to the line that reads: strRptList = rpt.Name.
This way the strRptList variable will only contain the Report name. All the other loops through the code will go through this line: strRptList = strRptList & “;” & rpt.Name which will add the semicolon between each Report name.
When the code is finished our Report list is ready to use in the listbox: rptPeopleAllAlphabeticalLandscape;rptPeople1PerPageP;rptAnnivAllSortByMonthP;rptAnniversariesAllByMonthP;rptAnniversariesAllP;rptBdaysAllByMonthP;rptBdaysAllP;rptBdaysAllSortByMonthP
That is just perfect to use in our listbox RowSource when we set the Row Source Type to “Value List”.
Now we need to use only one line of code to fill the listbox with our string of Report names. We just use our Listbox Name to set the RowSource property to the name of our Function, GetReportList, in the Load Event of the Form. Our code looks like this:
Private Sub Form_Load() Me!lstReports.RowSource = GetReportList End Sub
Now our Listbox and Form is ready to use. Which method you choose is up to you. The first method does have the advantage of being able to be sorted by Name or filtered, for example, by a Category. The second method is less likely to be broken by the next Version or Upgrade of Access.
In our next article in the series we will show you how to store your Report Names in a Table so you can query, sort, and filter them.
You can download the completed and free Report Date Dialog Form in the US or UK Version at our Gaining Access website.
Get the free Code Tracker™ Beta Add-in to record and then view how your procedures are being used.
More Free Downloads:
ScopeSight for Access Reports Demo: get the exact data you want on your Access Reports.
Free Church Management Software now with new Contributions management.
Get the Access and Outlook Appointment Manager to manage all of your Outlook Calendar Appointments and Access dated information.
Patrick (Pat) Wood