Introduction
Normally, 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 for 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 to 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 ones:
- Allow Additions = No
- Allow Deletion = No
Multiuser Environment.
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 with this method. Different Users may open the same Parameter Screen at the same time (assuming that a single Front-End is shared on a Network) 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 the 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 with 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 value.
There is only a minor drawback in this method; you cannot store the last used Report Parameter Value so that it will be displayed next time when you open the Form.
At least one set of values 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 on 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, in 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 used earlier to open the Form with the last edited record as current on the Form.
The Custom Property
We have to go through the following procedure to manage the User data on the Form itself without the use of a Table as a 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.
This is required only once. A small VBA Program is required in the Standard Module to create the Custom Properties on the Form. In the Program, the Parameter Form Name is required for reference. Not necessary to keep the Form in Design View to create the Custom Properties.
- When the Parameter Form is closed after normal use, the values set on the Unbound TextBoxes are Saved in 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.
The Design Task of Custom Property
- 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 to-Date.
- Close and save the Form with the name RptParameter.
- Display the VBA Editing Window (Alt+F11), and copy and paste the following VBA Code into the Standard Module. If necessary, create a new Module (Insert - -> Module).
VBA Code to Create Custom Property
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.
How do you know whether these Properties are created or not? Try running the Program again and this will tell you that these Property names already exist on the Form.
VBA Code to Delete Property if needed.
If you want to Delete these Properties from the Form then Run the following Code:
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 Design 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:
Storing the Text Box Values in Properties
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
Retrieving the Values from Custom Properties.
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
Perform a Demo Run.
- Open the RptParameter Form in Normal View and enter some Date Range values into fromDate and toDate Unbound Text Boxes.
Close the form and open it again in Normal View. The date values you entered earlier will appear in both 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 in 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.
'This method works fine when the Database is a Single User one.' Great! Thank you for this post. The information provided on this computer software is perfect for such custom property.
ReplyDeleteThanks.
ReplyDeleteThe explanation on the limitation of different solution is great.
I have try out the method and it work for me.
This is also a good example to understand the costume properties.