Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

A weblog about Microsoft Access, SQL Azure, and SQL Server Tips and Code Samples
Browse by Tags · View All
Access Tips 45
Microsoft Access 45
VBA 41
database 33
Access 33
Code 32
Software development 31
Microsoft Access Software Development 26
Free Access Downloads 25
Software 25

Archive · View All
October 2010 4
September 2008 4
June 2009 4
January 2008 3
July 2009 3
June 2007 2
May 2009 2
March 2011 2
July 2010 2
August 2011 2

Patrick Wood's Blog

How to Keep a Listbox of Access Report Names Current

Apr 12 2009 7:57AM by Patrick Wood   

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
            End If
            ' Move to the next record
    End With

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

End Sub

Republished from Access Easy Tips [18 clicks].  Read the original version here [32134 clicks].

Patrick Wood
98 · 2% · 568


Your Comment

Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]