Using the words “New” and “Improved” in advertising, it is claimed, increases sales. We have a New (to some) and Improved way to fill a listbox full of Report Names. We will use a Recordset to fill a Table with all the names of the Reports in your Database. This will allow you use a query as the RowSource of your Listbox so you will be able to filter and sort your Reports to make it easier to find the Report you want. This method is more stable and with a little additional work you will also be able to sort and filter your Reports by Categories or Department, etc.

First we create a new Table and name it “tblReportsList”. After adding an AutoNumber field we add a Text field named “ReportName”. It would probably be a good idea to add a Category, Department, or other field in case you want to add the capability to sort or filter your Reports by Category later on. Now we can examine the code.

Public Sub ReportNamesToTable()
On Error GoTo Err_ReportNamesToTable

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim rpt As AccessObject
    Dim strRptName As String
    Dim strFind As String

    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblReportsList", dbOpenDynaset)

	' Loop through all the Report Names in the Database
    For Each rpt In CurrentProject.AllReports
        strRptName = rpt.Name ' Get the Name

        With rst
            strFind = "ReportName = """ & strRptName & """"
            .FindFirst strFind
            If .NoMatch Then
                .AddNew
                !ReportName = strRptName
                .Update
            End If
        End With
    Next rpt
    rst.Close

Exit_ReportNamesToTable:
    ' Release Memory
    Set rst = Nothing
    Set db = Nothing
    Exit Sub

Err_ReportNamesToTable:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ")" & _
    " In procedure ReportNamesToTable"
    Resume Exit_ReportNamesToTable

End Sub

Looking at the code, after adding our variables we create an instance of (instantiate) the CurrentDb: Set db = CurrentDb.

We need to do this so we can open a Recordset with this line of code:

Set rst = db.OpenRecordset(“tblReportsList”, dbOpenDynaset)

We are using our table as the source for our Recordset. Using dbOpenDynaset as the Type of Recordset allows us add records to the table.

Next, the code uses a For Each loop to get all the Report Names in the CurrentProject.AllReports Collection. strRptName = rpt.Name gets the name of the Report. Now we can begin working with the Recordset. Using With rst keeps us from having to type rst numerous times.

The next line of code is crucial because if you do not get the quotes just right, you can’t add all your Report names to the table and you will have an error every time you open your form and all of your Reports will not be listed.

We are using the FindFirst Method to see if the Report Name is already listed in our table. The syntax to use FindFirst with a Recordset is Field Name = Value where ReportName is our field name and the names of the Reports are our values. We are storing all of that information in our strFind variable. But it only takes one little ‘ to cause your code to crash. Some people like to use quotation marks like this:

strFind = “ReportName = ‘” & strRptName & “‘”

That will work just fine until you have a value with a single ‘ in it. For example, if your reports are named after businesses and one of your Report names is “rptJack’sSeafood” then an error will occur and you can’t load all of your Report Names. That is not good if your supervisor wants that Report yesterday!

Why does this happen? Access thinks a lone single quote is an error. It works just fine if you use two quotes like ‘this item’. But if you use just one Access looks for another quote and can’t find it. When that happens, Access raises an error. You can usually avoid that problem by doubling up on your quotes as we did in our code:

strFind = “ReportName = “”" & strRptName & “”"”

Access now knows that you meant to leave that single quote there so there is no error and the boss is not upset because you are able to print the Report he wants.

Whenever you use the FindFirst Method you need to check if you have a match using NoMatch as we did in our code: If .NoMatch Then.

If there is not a match the Report Name is not in the table so our next few lines of code add the name to the table.

.AddNew
!ReportName = strRptName
.Update

Then the remaining lines of code close out the loop and exit the sub, releasing the memory held by our variables.

To use this sub just add it to a Standard Module and run if from the Form’s Open Event. Because it is a Public Sub instead of a Function you do not have use “Call”:

Private Sub Form_Open(Cancel As Integer)

    ReportNamesToTable

End Sub

This was a very simple example of using a recordset because we only wanted to add new records to the table. Our next post in this series will show how to delete the records of Report Names that no longer exist in the database.