Save User Specific Parameter values

Introduction

Last week, we created a Report Parameter Control Form for concurrent users on a network, ensuring that each user’s report filter parameters do not conflict with others while generating different versions of the same report.

To achieve this, we avoided using a parameter table as the record source for the parameter form. Instead, we added two unbound text boxes for entering the parameter values, which are then used to set the filter conditions in the report’s source query.

Additionally, we saw how to save these parameter values in the form’s Custom Properties when the form is closed, preserving them for future use.

The sample image of the Report Parameter Form: RptParameter is given below for reference:

Last week, we used the parameter control text boxes FromDate and ToDate to enter date-range values as criteria for filtering data in the report’s source query. The custom property names we created were slightly modified versions of these text box names, such as DateFrom and DateTo.

One question we raised was: Is it possible to save and retrieve the report parameter values entered by each user in different instances of the same form without overwriting someone else’s values?

In last week’s example, there was only one set of custom properties created for all users and all instances of the form. Consequently, the last user on the network who closes their form instance will overwrite the custom property values of earlier users. These overwritten values will then be loaded back into the text boxes the next time the form is opened.

User-based Custom Property Name Format.

The solution is straightforward: create custom properties on the form for each user and save the values entered by that user when their instance of the parameter form is closed.

To uniquely identify each user’s values, combine the user name with the text box name when naming the custom property. This ensures that each user has a distinct custom property, preventing one user’s input from overwriting another’s.

For example:

• User-Name: JSmith
• Parameter TextBox Name1: fromDate
• Parameter TextBox Name2: toDate
• Custom Property Name1: JSmithDateFrom
• Custom Property Name2: JSmithDateTo

 We can retrieve the user name of the current form instance using the CurrentUser() function.

When the User-Name prefixes the Custom Property Names, we can easily save and retrieve individual users' parameter values and use them on their instance of the same Form as well.

Another point to remember is that new users may be added to the same Workgroup, and this method should work for them also when they start using the RptParameter Form.

We must write the VBA Routines in such a way that whenever a new user starts working with the RptParameter Form, the User must be identified as a newcomer and the Custom Properties for that User must be created automatically.

The Custom Property Creation Code.

1. The modified Custom Properties creation Program is given below. Copy and paste the code into a Standard Module of your Database.

   Public Function CreateCustom_Property(ByVal frmName As String, ByVal usrName As String)
   '-------------------------------------------------------------------------
   ' Author : a.p.r. pillai
   ' Date   : November-2009
   ' All Rights Reserved by www.msaccesstips.com
   '-------------------------------------------------------------------------
   Dim cdb As Database, doc As Document
   Dim prp As Property, getPrpValue
   Dim fld1 As String, fld2 As String
   
   On Error Resume Next
   
   fld1 = usrName & "DateFrom"
   fld2 = usrName & "DateTo"
   
   Set cdb = CurrentDb
   Set doc = cdb.Containers("Forms").Documents(frmName)
   
   'check whether the Property of the current user exists
   getPrpValue = doc.Properties(fld1).Value
   
   If Err = 3270 Then 
   ' referenced Property doesn't exist
       Err.Clear
   'create Property for new User
       Set prp = doc.CreateProperty(fld1, dbDate, Date)
       doc.Properties.Append prp
       Set prp = doc.CreateProperty(fld2, dbDate, Date)
       doc.Properties.Append prp
       doc.Properties.Refresh
   End If
   
   Set prp = Nothing
   Set doc = Nothing
   Set cdb = Nothing
   
   End Function

2. Create a Copy of the RptParameter Form with the name RptParameter2.
3. Open the RptParameter2 Form in Design View.
4. Display the VBA Module of the Form (View -> Code).
   
   

   Revised VBA Code - Form Load.

5. Copy and paste the following Sub-Routines into the VBA Module, overwriting the earlier Code:
   

   Private Sub Form_Load()
   Dim cdb As Database, doc As Document
   Dim fld1 As String, fld2 As String
   
   fld1 = CurrentUser & "DateFrom"
   fld2 = CurrentUser & "DateTo"
   
   'Validate Current User's Status
   'If, necessary create Custom Properties for new User
   CreateCustom_Property Me.Name, CurrentUser
   
   DoCmd.Restore
   
   Set cdb = CurrentDb
   Set doc = cdb.Containers("Forms").Documents(Me.Name)
   Me![fromDate] = doc.Properties(fld1).Value
   Me![toDate] = doc.Properties(fld2).Value
   
   Set cdb = Nothing
   Set doc = Nothing
   
   End Sub
   

   Revised VBA Code - Form Close.

   Private Sub Form_Close()
   Dim cdb As Database, doc As Document
   Dim fld1 As String, fld2 As String
   
   fld1 = CurrentUser & "DateFrom"
   fld2 = CurrentUser & "DateTo"
   
   Set cdb = CurrentDb
   Set doc = cdb.Containers("Forms").Documents(Me.Name)
   
   doc.Properties(fld1).Value = Me![fromDate]
   doc.Properties(fld2).Value = Me![toDate]
   
   Set cdb = Nothing
   Set doc = Nothing
   End Sub

6. Save and Close the Form.

Review of Programs.

As shown in the Form_Load() event procedure, the custom property creation routine CreateCustom_Property() is called only when a user (new or existing) opens the RptParameter2 form.

This means that custom properties are not created for all users in the database—they are only created for users who interact with this specific form.

The Report Parameter Form should be tested simultaneously from different machines by multiple users. Each user can enter their own values in the parameter control text boxes (FromDate and ToDate) to test the procedure. When the same users open the form in a later session, the values they entered previously should automatically appear in the respective text boxes.

Note: The above procedure has not been extensively tested for bugs or side effects, so it should be used at your own risk. If you encounter any issues, please share them. The procedure assumes that the database is implemented with Microsoft Access Security.

For unsecured databases, visit the page “Unsecured Database and Users Log” to learn how to retrieve a user’s name. In unsecured databases.  The CurrentUser() function will always return the username as Admin.

• Office Assistant and MsgBox Menus-2
• Office Assistant and MsgBox Menus
• Color and Picture in MsgBox
• Microsoft-Excel Power in Access
• Unsecured Database and Users-Log