Dates | Beginning Access 2002 VBA (Programmer to Programmer)

In Access 2002, Microsoft has introduced two new date properties: DateModified and DateCreated (we previewed them in Chapter 5 - Using Access Objects ). Now we are going to add these two dates and a DatePrinted field (based on the Now() function) to the Sales Summary report.

While we are adding these dates to our report, it is a convenient time to reflect on how we can use the AllReports collection and Report object (actually it was a variable of type AccessObject , that we used to represent a " Report Object ") that we previewed earlier (Chapter 5). Previously we looped through the AllReports collection extracting the Name property of each report in the collection. We then used this property to add to a list of report names in a listbox. Now we will explore how we can use these two new properties, DataModified and DateCreated , of a report.

Placing a DatePrinted value on a report is as simple as setting the control source of a text box to the Now function, however, the DateModified and DateCreated dates pose a challenge: they are properties of a Report Object ( AccessObject) that resides in the AllReports collection. The approach that we will take is to develop a custom function ( ReportHistory ) that will return all the dates and property values in a string and set it as the control source of a textbox.

Note: we use a function and not a subroutine because a function returns a value and a subroutine doesn't.

The ReportHistory function will be passed one string parameter - the name of the report - and it will return a string that will include the three dates that we want ( DatePrinted , DateModified , and DateCreated ), the printer's port, and device names from the Printers collection. We will then create a textbox ( txtReportHistory ) on the report and set its control source to the ReportHistory function.

In a real-world situation, ReportHistory function would be useful on many reports , so to enable us to re use the function we will write it generically, that is, there will be no hard-coding of names (this is referred to as 'loose coupling').

The information returned by ReportHistory will be useful for users of reports, because it will tell them when the report was run, the port and device it was run on, and the date when the report (not the data) was last modified and first created.

Try It Out-Writing the ReportHistory Function

In Modules : Create a new module.

Enter the following code and save the module as Chapter 9 Code .

   Function ReportHistory(ByVal sRpt As String) As String     Dim acObj As AccessObject     Dim sRptIn As String     Dim sDatePrinted As String     Dim sDateModified As String     Dim sDateCreated As String     Dim sPort As String     Dim sDevice As String     Dim sBuild As String     sRptIn = sRpt     sPort = "Port name: " & Application.Printers(0).Port     sDevice = "Device name: " & Application.Printers(0).DeviceName     sBuild = ""     For Each acObj In CurrentProject.AllReports     With acObj     If acObj.Name = sRptIn Then     sDatePrinted = "Date printed: " & Now()     sDateModified = "Date modified: " &.DateModified     sDateCreated = "Date created: " & .DateCreated     Exit For     End If     End With     Next acObj     sBuild = sDatePrinted & ", " & sPort &", " & sDevice & ", " _     & sDateCreated & ", " & sDateModified & "."     ReportHistory = sBuild     End Function

How It Works

We create a function header that takes one String argument (the report name) and returns a String :

   Function ReportHistory(sRpt As String) As String

Then we declare an AccessObject - the AllReports collection contains AccessObject s that represent reports. Next we declare seven String variables to hold respective strings:

   Dim sRptIn As String     Dim sDatePrinted As String     Dim sDateModified As String     Dim sDateCreated As String     Dim sPort As String     Dim sDevice As String     Dim sBuild As String

Next we assign values to four String variables: sRptIn is assigned the value passed in by the parameter ( sRpt ); sPort and sDevice are assigned the respective property of the Printer object. We prefix each of these strings with a descriptor (for example, Port name: ) and then access the respective property by using its qualified name: for example, the Port property is addressed through the Application object (the Access application), which has a collection of Printers , and the first Printer object in the collection has an index value of , and this printer has a Port property (in this case we have only one printer). Finally we assign "" to sBuild to assure ourselves that it holds no value:

   sRptIn = sRpt     sPort = "Port name: " & Application.Printers(0).Port     sDevice = "Device name: " & Application.Printers(0).DeviceName     sBuild = ""

Having done that, we commence a For Each Next block and with each AccessObject in the AllReports collection we progress through the collection:

   For Each acObj In CurrentProject.AllReports     With acObj

We progress through the collection until the If statement is True . We use an If statement to see if the AccessObject represents the report that we are looking for ( Sales Summary which is the value held in sRptIn ). If the AccessObject does match the string value in sRptIn , then we have located our report in the AllReports collection and we can assign respective string variables to hold the Now function and the two properties of the AccessObject ( DateModified and DateCreated ). The Exit For is used to exit the For Each loop as we have the report we want: after that we close with End If and End With :

   If acObj.Name = sRptIn Then     sDatePrinted = "Date printed: " & Now()     sDateModified = "Date modified: " & .DateModified     sDateCreated = "Date created: " & .DateCreated     Exit For     End If     End With

If, on the other hand, the AccessObject doesn't match the string value in sRptIn it moves onto the Next AccessObject in the AllReports collection. (We could add some error code in an Else statement to handle the situation where the parameter passed was invalid - but we won't for brevity.)

   Next acObj

Finally we build our sBuild string and assign it to the functions return, and then end the function:

   sBuild = sDatePrinted &", " & sPort & ", " & sDevice & ", " _     & sDateCreated & ", " &sDateModified & "."     ReportHistory = sBuild     End Function

Testing the ReportHistory Function in the Immediate Window

We don't know for sure if our code has error-free syntax or is logical until we test it, and the best place for that is in the Immediate Window.

Try It Out-Testing the ReportHistory Function in the Immediate Window

From the code module window, open the Immediate Window ( View Immediate Window )
Enter the following line of code into the Immediate Window:
```
 ?ReportHistory("Sales Summary") 
```

The Immediate window should look similar to the following:

Now we have our function written and tested so we can add it to the Sales Summary report - so open up the report in design mode, if it is not already open, and let's finish building our report.

Try It Out-Adding Dates to the Sales Summary report

In the Page Footer section, add a textbox from the toolbox, change the name of the textbox to txtReportHistory , change the label for the textbox to read Report History , and then add the following as the control source in txtReportHistory :
```
 =ReportHistory([Name]) 
```
The Page Footer should now look like this:
Switch to preview mode and you should see the following:

How It Works

We set the control source of txtReportHistory to the string value returned by ReportHistory function. The function needs to be passed a string parameter that represents the name of the report. We do this by passing the [Name] property of the report to the ReportHistory function.

As we said before we wanted to keep this function loosely coupled (that is, not tied to any one report) and by using the generic [Name] property of the report we can achieve our design objective.