Creating Using Form Custom Property

Introduction

Normally, parameter controls are provided to users for entering data filter criteria when generating MS Access reports. The parameter control fields can be directly referenced in the criteria row of the report’s source query to filter data based on the user’s input. A sample image of such a parameter control form is shown below.

Right-click to open Large Image in New Tab/Window.

The Report Parameter Control Form gives users the flexibility to specify a date range before opening one of the two available report options. When the user clicks the Preview command button, the report opens, containing the filtered data according to the Date From and Date To values entered in the parameter controls.

To store these date range values, a small table is created with just two fields and a single record, which serves as the record source for the form. The purpose of this table is to preserve the last-used report parameters, allowing users to recall the previous reporting period the next time the form is open.

This parameter table can also be referenced in the report source query, either by linking it to the main data table or by using its field values directly in the criteria row to filter data.

To ensure the table always contains only one record, the following form property settings must be configured:

• Allow Additions = No
• Allow Deletion = No

Multiuser Environment.

This method works fine when the Database is a single-user one.

However, when the database is shared over a network, this method introduces a major issue. Multiple users may open the same Report Parameter Form simultaneously (especially when a single shared front-end is used across the network).

In such cases, all users are referencing the same parameter table as the form’s record source. As a result:

• When one user modifies the parameter values (for example, changing the FromDate or ToDate),

• The same record is being edited by other users concurrently.

This situation can lead to record edit-lock errors, or worse, the parameter values may overlap or get overwritten. Consequently, the reports printed by different users may be using incorrect or mixed-up filter criteria.

Even though each user technically opens a separate instance of the form on their own machine, they’re all connected to the same back-end table, which causes this conflict.

We are now focusing on this specific issue — how to safely provide the Report Parameter Control to multiple users without conflicts.

You’ve probably guessed the right solution by now: avoid using a shared Parameter Table to store report criteria values. Instead, use two unbound text boxes on the Form (for example, FromDate and ToDate).

This approach ensures that each user works within their own isolated instance of the Parameter Form, preventing any possibility of overlapping or clashing parameter values.

The only minor drawback with this method is that the last used parameter values cannot be automatically preserved for display the next time the user opens the form.

At least one set of parameter values must be available when the form is opened the next time. If these controls are left empty and the Preview option is run without entering any values, the Report will be generated without data.

As a result, all the controls in the Report that contain expressions for summary or calculated values will display #Error, since the underlying dataset is empty.

To prevent this, we need a mechanism to retain or restore the last-used parameter values each time the form is opened — ensuring that the Report always has a valid date range or criteria to work with.

I have already published an article earlier on how to open a Report without triggering this #Error condition when the Report Source Table or Query happens to be empty.

You can [click here to read that article] for the complete method and example.

We can store the values entered in the Unbound Text Box controls of the Parameter Form within Custom Properties that we create directly on the Form.

These Custom Properties act like hidden variables attached to the Form itself. They can only be created, modified, or read through VBA, and are not visible in the usual Property Sheet that we work with in Design View.

Click here to learn more about Custom Properties and the method we used earlier to open a Form with the last edited record automatically set as the current record when the Form loads.

The Custom Property

To manage user data directly on the Form—without using a Table as its Record Source—follow the procedure below:

1. Create two Custom Properties on the Form named DateFrom and DateTo, with the Date/Time data type and initial values.

   • This step needs to be done only once.

   • You’ll need a small VBA program in a Standard Module to create these Custom Properties.

   • The program will require the Form name as a reference, but it’s not necessary to open the Form in Design View to create the properties.

2. When the Parameter Form is closed after normal use, the values entered in the unbound text boxes are automatically saved into the Custom Properties during the Form_Close event.

3. The next time the Form is opened, these saved values are loaded back into the unbound text boxes from the Custom Properties, restoring the user’s last-used parameter values.

The Design Task of Custom Property

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

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

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

7. Click anywhere inside the pasted VBA code and press F5 to run it. This will create two Custom Properties on the Form—DateFrom and DateTo—with the Date/Time data type and an initial value set to the current system date.

   Wondering how to confirm whether these properties were actually created? Simply run the program again. This time, the program will display a message indicating that the Property names already exist on the Form, confirming their successful creation.

   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

8. Open the RptParameter Form in Design 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:

    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.

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 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 satisfied with it. The reason is that it preserves only one set of values, so if multiple users work with the Form at the same time, only one of their values is saved.

“As a user, I would like the last value I entered in the Report Parameter fields to appear the next time I open the Form, rather than being overwritten by a value set by someone else.”

Is this possible? Yes, it is, and we will explore how to achieve this next week.

• Office Assistant and MsgBox Menus
• Color and Picture in MsgBox
• Microsoft Excel Power in Access
• Unsecured Database and Users-Log
• MS-Access Report and Page Total