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
strFind = "ReportName = """ & strRptName & """"
If .NoMatch Then
!ReportName = strRptName
' Release Memory
Set rst = Nothing
Set db = Nothing
MsgBox "Error " & Err.Number & " (" & Err.Description & ")" & _
" In procedure ReportNamesToTable"
Looking at the code, after adding our variables we create an instance of (instantiate)
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
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
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.
!ReportName = strRptName
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)
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.
Republished from Access Easy Tips [18 clicks].
Read the original version here [32134 clicks].