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:

Allow Additions = No

 

Allow Deletion = No

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:

1. 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.

    

2. 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.

 

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

 

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

 

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

 

3. Change the Name Property Value to fromDate.

 

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

 

5. Close and save the Form with the name RptParameter.

 

6. 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

    

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

    

   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

 

8. Open the RptParameter Form in Desgin View.

 

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

 

10. 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

     

11. 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 have 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 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.