Programming & Security

Posted: 29 Nov 2009 10:11 AM PST

Parameter Controls are provided to the Users for entering data Filter criteria values for preparing MS-Access Reports. A reference to the parameter control fields can be set in the criteria row of the Report Source Query directly to filter the data. A sample image of such a Parameter Control is given below.

The above Report Parameter Control gives flexibility to the User to set a Date Range in the fields provided on the screen before opening one of two Report Options provided. When the User Clicks on the Preview Command Button the Report will be opened with data filtered using the Parameter Control Date Range values set as Criteria on the Source Query.

To record the Date Range values (Date From and Date To) a small Table with two fields is created with a single record and used as Record Source to the above Form. The idea behind the use of a Table is to preserve the last used Report Parameter Values in the Table. Next time when we open the Form we will know for which period we have prepared the Report earlier. The parameter table can also be used in the Query to link with the Data Table or use its value as criteria in the criteria Row of the Report Source Query. The following two Form Property Values must be set as shown below to prevent adding new Records to the Table and not to delete the existing one:

This method works fine when the Database is a Single User one.

But, when the database is shared on a Network there is a problem to this method. Different Users may open the same Parameter Screen at the same time to prepare their version of the same Report. They will attempt to change the parameter values at the same time. This action will end up with a record edit lock error or the values set can cross over and the Report printed can go wrong too. Even though the Users can open different instances of the same Form on their machines the Record Source Table is same.

We are focusing on this particular aspect to see how we can safely provide the above Parameter Control to the Users to work safely without clashing each other.

Perhaps, you have the right solution to this problem by now. Yes, do not use the Parameter Table to store the Report criteria values; instead create two Unbound Text Boxes on the Form, as you have rightly guessed. This will ensure that all Users will work independently on their own instances of the Report Parameter Form and no chance of clashing with each other's values.

There is only a minor draw back in this method; you cannot store the last used Report Parameter Value anywhere so that it will be displayed next time when you open the Form.

At least one set of value is required when you open the Form next time. If these controls remain empty and if you run the Preview option without setting the parameter values then the Report will be empty and will end up showing #Error in all controls with expressions created for Summary Values.

I have already published an Article on this topic earlier as how to open the Report without this Error condition when the Report Source Query or Table is empty. Click here to find out.

We can save the values, from the Unbound Text Box controls, on the Parameter Form itself in Custom Properties, which we can create on the Form. Managing data in Custom Properties can be done only through VBA and these Property Names and their Values are not visible on the Property Sheets that we normally work with.

Click here to find out more details on Custom Properties and a method that we have used earlier to open the Form with last edited record as current on the Form.

We have to go through the following procedure to manage the User data on the Form itself without the use of a Table as Record Source:

Create two Custom Properties on the Form with the names DateFrom and DateTo with the Data Type Date/Time and with an initial Value.

Name

When the Parameter Form is closed after normal use the values set on the Unbound Text Boxes are Saved into the Custom Properties during the Form Close Event.

The saved values are loaded back into the Unbound Text Boxes from the Custom Properties when the Report Parameter Form is open next time.

To try out this method, open a new Form and create two Unbound Text Boxes.

Click on the first Text Box and display its Property Sheet (View - -> Properties).

Change the Name Property Value to fromDate.

Change the Name Property Value of the second Text Box to toDate.

Close and save the Form with the name RptParameter.

Display the VBA Editing Window (Alt+F11), copy and paste the following VBA Code into the Standard Module. If necessary, create a new Module (Insert - -> Module).


Public Function CreateCustomProperty()
Dim cdb As Database, doc As Document
Dim prp As Property

Set cdb = CurrentDb
Set doc = cdb.Containers("Forms").Documents("RptParameter")

Set prp = doc.CreateProperty("DateFrom", dbDate, Date)
doc.Properties.Append prp

Set prp = doc.CreateProperty("DateTo", dbDate, Date)
doc.Properties.Append prp

doc.Properties.Refresh

Set prp = Nothing
Set doc = Nothing
Set cdb = Nothing

End Function

Click somewhere within the pasted VBA Code and press F5 to Run the Code and create two Custom Properties with the Names DateFrom and DateTo with the Data Type Date/Time and with the initial value of System Date.

Delete


Public Function DeleteCustomProperty()
Dim cdb As Database, doc As Document
Dim prp As Property

Set cdb = CurrentDb
Set doc = cdb.Containers("Forms").Documents("RptParameter")

doc.Properties.Delete "DateFrom"
doc.Properties.Delete "DateTo"
doc.Properties.Refresh

Set prp = Nothing
Set doc = Nothing
Set cdb = Nothing

End Function

Open the RptParameter Form in Desgin View.

Display the VBA Code Module of the Form (View - -> Code).

Copy and Paste the following two Sub-Routines into the Form Module and save the Form:


Private Sub Form_Close()
Dim cdb As Database, doc As Document

Set cdb = CurrentDb
Set doc = cdb.Containers("Forms").Documents("RptParameter")

doc.Properties("DateFrom").Value = Me![fromDate]
doc.Properties("DateTo").Value = Me![toDate]

Set cdb = Nothing
Set doc = Nothing

End Sub




Private Sub Form_Load()
Dim cdb As Database, doc As Document

DoCmd.Restore

Set cdb = CurrentDb
Set doc = cdb.Containers("Forms").Documents("RptParameter")

Me![fromDate] = doc.Properties("DateFrom").Value
Me![toDate] = doc.Properties("DateTo").Value

Set cdb = Nothing
Set doc = Nothing

End Sub

Open the RptParameter Form in Normal View and enter some Date Range values into fromDate and toDate Unbound Text Boxes.

Even after implementing this method I am not fully happy with it. Because, it will preserve only one of the Values, set by different Users working with the Form at the same time.

What I would like to see as a User is that the last value that I set on the Report Parameter Field is appearing on the Form again when I open the Form next time, not the value set by someone else. Is it possible? Yes, it is possible. We will see how to do this next week.