In our last article we learned how to add all the Report Names in the Database to a Listbox by first adding the Report Names to a Table. But what happens if we delete or rename a Report? The names are still in our table and will also be in our Listbox. We do not want to click on the names of Reports in our Listbox if that Report does not exist. We need to delete the names of any Reports that are not currently in our database.

We can take care of that by first placing the following code in a Standard Module.

Public Sub ReportNamesDelete()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim rpt As AccessObject
    Dim booFound As Boolean

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

    With rst
        ' Make sure there are records in the recordset
        Do While .EOF = False
            ' Set booFound as False for a Default setting
            booFound = False
            ' This is the fast way to check every name
            For Each rpt In CurrentProject.AllReports
                If !ReportName = rpt.Name Then
                    booFound = True
                    ' This Report is there so exit this loop
                    ' and move on to the next Record
                    Exit For
                End If
            Next rpt
            ' If the Report was not found
            ' booFound remains false
            If booFound = False Then
                .Delete
            End If
            ' Move to the next record
            .MoveNext
        Loop
    End With
    rst.Close

    ' Release Memory
    Set rst = Nothing
    Set db = Nothing

End Sub

The first part of the code is like that we used in our last article. But then we get to this line of code:

Do While .EOF = False

This one line of code does two things. First, it checks for the existence of records before moving through the recordset. If we try to move through an empty recordset Access will raise an error. Our recordset has already been opened and if the current record position is after the last record in a Recordset (positioned at EOF) then there are no records in the table. We can’t delete Report Names if the table does not have any so our code skips the record processing, closes the recordset, and moves on to the end of the procedure.

The second thing this line of code does, assuming there are records found, is to begin to loop through every record in the table. The loop enables us to find and delete any Report Names not in the database.

Before we begin to process the records we set a boolean variable: booFound = False. If the Report Name is not found, booFound will be false and the code will delete the missing Report Name.

Next the code begins to get all the existing Report Names by starting another loop using the CurrentProject.AllReports collection like we did in our last article. If the Report Name in the table matches one in the list of existing Reports (!ReportName = rpt.Name), then our boolean variable, booFound is set to True so it will not be deleted. We also no longer need to loop through the rext of the Report Names in the Database so we exit the For Each loop with this code: Exit For.

However, if no match is found, booFound remains False and we have a Report Name in the Table that needs to be deleted. The following code does the job:

If booFound = False Then
    .Delete
End If

To avoid getting stuck in an endless loop, we must not forget to add .MoveNext to the code.

After cleaning up, releasing memory, and adding error handling code, we need to do one more thing. To make sure the Listbox query has the right records to draw from the table, we must call this procedure, ReportNamesDelete and the procedure from our last article ReportNamesToTable when the Form opens. Here is an example using the Form Open Event:

Private Sub Form_Open(Cancel As Integer)

    ' This provides an informative and professional
    ' appearance to our Form Title Bar
    Me.Caption = "  Report Date Manager              " & _
    "      Today is " & Format(Date, "dddd"", ""m/d/yyyy")

    ' We call these two Procedures to get the
    ' current Report Names in our table
    ReportNamesDelete
    ReportNamesToTable

End Sub