|



Save User Specific Parameter values

Posted: 06 Dec 2009 07:37 AM PST



Last week we have prepared a Report Parameter Control Form for concurrent usage on 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 to 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:



Report Parameter Screen

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 change to the Text Box Names, like DateFrom and DateTo.

One Question we have raised last week was; is it possible to Save and Retrieve the Report Parameter Values set by each User on the fromDate and toDate Text Box Controls, in different instances of the same Form, without getting overwritten by somebody else value. In last week's example the survivor of the values written to the Custom Properties are the values of the last User who closes the Parameter Form.

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 and create the Custom Property Name with that combined text value.


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 is prefixed to the Custom Property Names we can easily save and retrieve individual User's parameter values and use 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 new comer and Custom Properties for that User must be created automatically, rather than creating it manually as we did in the earlier example.



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



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


  3. Create a Copy of the RptParameter Form with the name RptParameter2.

  4. Open RptParameter2 Form in Design View.

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

  6. Copy and Paste the following Sub-Routines into the VBA Module overwriting the earlier Code:



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



    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



  8. Save and Close the Form.



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



StumbleUpon Toolbar



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

0 comments:

Post a Comment

Note: Only a member of this blog may post a comment.

 

©2009 Programming & Security | Template Blue by TNB

Vida de bombeiro Recipes Informatica Humor Jokes Mensagens Curiosity Saude Video Games Diario das Mensagens Eletronica Rei Jesus News Noticias da TV Artesanato Esportes Noticias Atuais Games Pets Career Religion Recreation Business Education Autos Academics Style Television Programming Motosport Humor News The Games Home Downs World News Internet Design Entertaimment Celebrities 1001 Games Doctor Pets Net Downs World Enter Jesus Mensagensr Android Rub Letras Dialogue cosmetics Genexus Só Humor Curiosity Gifs Medical Female American Health Madeira Designer PPS Divertidas Estate Travel Estate Writing Computer Matilde Ocultos futebolcomnoticias girassol lettheworldturn topdigitalnet Bem amado enjohnny produceideas foodasticos cronicasdoimaginario downloadsdegraca compactandoletras newcuriosidades blogdoarmario arrozinhoii sonasol halfbakedtaters make-it-plain amatha lisboaohyeah lasofia thebushrajr wingshock tripedes gorduravegetal dainfamia dejavu-transpersonal jsbenfica republicadasbadanas ruiherbon iranianforaryans eaystcheyl fotosdanadir ojosmasabiertos ceilasantos