Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Save User Specific Parameter values


Last week we have prepared a Report Parameter Control Form for concurrent users on the Network without clashing with other User's Report Data Filter Parameter Values while preparing different Versions of the same Report. To do this we have discarded the usage of a Parameter Table as Record Source in the Parameter Form and created two Unbound Text Boxes for keying in Parameter Values for setting Filter condition in the Report Source Query. Besides that, we have seen how to save the Parameter Values in the Custom Properties of the Form, when the Form is closed.

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

Last week we have used the Parameter Control's Text Box Names FromDate and ToDate for entering Date-Range values as Criteria for filtering Data in Report Source Query. The Custom Property Names we have created giving slight changes to the Text Box Names, like DateFrom and DateTo.

One Question we raised last week was; is it possible to Save and Retrieve the Report Parameter Values set by each User on the fromDate and toDate TextBox Controls, in different instances of the same form, without getting overwritten by somebody else value? In last week's example, there is only one set of custom properties created for all the users and for all instances of the Form. The last user of the Network who closes his instance of the form will overwrite the Custom Property values of all other earlier instances and will be loaded back into the TextBox control next time when the form is loaded.

User-based Custom Property Name Format.

The solution is simple; create Custom Properties on the Form for each User and save the value set by him/her when their side instance of the Parameter Form is closed. To identify each User's value; add the User-Name along with the TextBox Name, to make the names unique, and create the Custom Property Name.

For example:

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

 We can get the User Name of the current instance of the Form with the Function CurrentUser().

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

Another point we have to keep in mind 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 Custom Properties in that User must be created automatically, rather than creating it manually as we did in the earlier example.

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
    '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
    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 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
    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 you can see from the Form_Load() Event Procedure that the Custom Property Creation Program CreateCustom_Property() is called only when the User (New or existing) opens the RptParameter2 Form. So, the Custom properties are not created for all the Users of the Database if their activity doesn't involve this particular Form.

The Report Parameter Form must be tested from different machines at the same time by different Users and set their own values in the Parameter Control Text Boxes: fromDate and toDate to try out the procedure. When the Form is open by the same Users next time the values set by them in the earlier session should appear in the fromDate and toDate Text Box Controls.

NB: The above Procedure is not extensively tested to trap Bugs or side effects and you may use it at your own risk. If you find any bugs or issues, please share them with me too. The procedure assumes that the Database is implemented with Microsoft Access Security.

Visit the Page: Unsecured Database and Users Log to find out how to get a User's Name when the shared database is an Un-secured one. In un-secured databases, the CurrentUser() Function will always return the User Name as Admin.



  1. Great information! I’ve been looking for something like this for a while now. Thanks!

  2. I see an incredible progress in your posting, I'd love to get in touch. Keep up the perfect work! Your text is rather motivational for a person who is new to this kind of stuff.

  3. I've found out a thing new on a range of websites daily. It's usually exciting to see content of other authors and learn a little some thing from them. Thanks for giving.


Comments subject to moderation before publishing.




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code