MS Access How To Keep Track Of Users or Employees Using the Database

hi Dustin,

I like using a database property to keep track of the user for each FE since it is remembered even when you close Access -- so you only have to do this once. Alternately, if you collect the user with a login screen, you can set it then ... for cases when multiple people use the same computer

Here is code to set it:

   CurrentDb.Properties(sPropName) = myValue

Select allOpen in new window

WHERE
sPropName is the property name -- I use "local_UserID" and myValue is the value you want to change it to. The FE does not need a list of who each UserID is.

here is code to see who the user is:

   Dim nUserID As Long
   nUserID = get_UserID

Select allOpen in new window

I put tracking fields in all tables for IDadd and IDedit -- these get updated on each form BeforeUpdate event (I call a general procedure that also updates when a record was changed)

and here is the function it calls:

Public Function get_UserID() As Long
   get_UserID = Get_Property("local_UserID", , 0)
End Function

Select allOpen in new window

and here is the Get_Property function that is called:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'
' this is a generic function to be used to
' get the value of a database property
' you can pass an optional database object
' if you want to look somewhere other than CurrentDb
'~~~~~~~~~~~~~~~~~~~~~ Get_Property
Function Get_Property( _
   pPropName As String _
   , Optional Obj As Object _
   , Optional pvDefaultValue As Variant _
   ) As Variant
    
   'crystal (strive4peace)
    
   ' PARAMETERS
   ' pPropName is the (database) property name to return the value of
   ' optional:
   ' obj = database, field, tabledef, querydef,
   '   or other object with properties
   '   if obj is not specified, then CurrentDb is used
   '
   'RETURNS
   ' Null if property has no value or is not defined
   ' OR
   ' Value of property
    
   ' Assumes all needed properties are defined
    
   On Error GoTo Proc_Err
   
   If IsMissing(pvDefaultValue) Then
      Get_Property = Null
   Else
      Get_Property = pvDefaultValue
   End If
   
   On Error GoTo Proc_Exit
   
   If Obj Is Nothing Then
      Set Obj = CurrentDb
   End If
   
   Get_Property = Obj.Properties(pPropName)
      
Proc_Exit:
   On Error Resume Next
   Exit Function
  
Proc_Err:
   
   MsgBox Err.Description, , _
       "ERROR " & Err.Number _
        & "   Get_Property: Property not defined"
 
   Resume Proc_Exit
   Resume
End Function

Select allOpen in new window

have an awesome day,
crystal

Thanks Crystal. You said:

I like using a database property to keep track of the user for each FE since it is remembered even when you close Access -- so you only have to do this once.

Now is this an example of a single person in a single office or can this be used on a computer that several people use. Such as logging into Access with my form and then logging out on exit?

Thanks for all the help!

you're welcome, Dustin

If users are not specifically assigned a FE, then you will need a list of users and their IDs in a table. Once they enter who they are, set the database property. Another advantage is that database properties do not lose their values like global variables sometimes do.

My tracking fields for tables are:

• IDadd, long
• IDedit, long
• dtmAdd, date/time, default value =Now()
• dtmEdit, date/time, default value =Now()

here is a general function I call on a form BeforeUpdate event when data is added or edited:

Public Function FormBeforeUpdate( _
    pF As Form _
   , Optional bUpdateParentToo As Boolean = False _
   ) As Boolean
'161220 strive4peace, 28
'
   ' call on the form BeforeUpdate event - before record is saved
   '
   'UPDATE the record on the current form with dtmEdit, IDadd, IDedit
   '       optionally, also update the record in the parent form
   '
   On Error GoTo Proc_Err
   FormBeforeUpdate = False

   Dim nUserID As Long _
      , nCount As Long
      
   nUserID = get_UserID

   With pF
       If bUpdateParentToo Then
         .Parent.dtmEdit = Now()
         .Parent!IDedit = nUserID
      End If
      !dtmEdit = Now()
      !IDedit = nUserID
      If .NewRecord Then
         !IDadd = nUserID
      End If
   End With
   
Proc_Done:
   FormBeforeUpdate = True

Proc_Exit:
   On Error Resume Next
   Exit Function
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   FormBeforeUpdate"
 
   Resume Next
   Resume Proc_Exit
   Resume
End Function

Select allOpen in new window

Thanks!

Crystal:

Another advantage is that database properties do not lose their values like global variables sometimes do.

What exactly do you mean by this? Is the Tempvar Dale suggested a Global Variable?

What Happens when it loses its value...It just becomes Blank or something?


Also For any future viewers. Here is a video of the tempvar solution on youtube. Link Here

Dustin,

Tempvars are actually part of a collection, which can be created from the immediate window even when the application is not running (during development).  Although they are similar to global variables in that they can be called from anywhere in your code, they have the added advantage that once they are set, they do not lose their value, even in the event of an unhandled error, until the application is closed.  But they do lose their value, unlike the database property described by Crystal.

Many times, developers will use references to a value on a form in a query, like:

SELECT * from yourTable where [ClientID] = Forms![yourFormname].cbo_ClientID

Select allOpen in new window

But this query can only be run when the form is open.  I use tempvars, and set the value of the tempvar in the afterupdate event of a control on a form, for example:

Private Sub cbo_ClientID_AfterUpdate()

    Tempvars!ClientID = me.cbo_ClientID

End Sub

Select allOpen in new window

Then, in my query I would use:

SELECT * from yourTable where [ClientID] = [Tempvars]![ClientID]

Select allOpen in new window

But I don't have to have the form open to test this, I can just enter a line of code in the immediate window, like:

Tempvars!ClientID = 5

Select allOpen in new window

and then am able to test the query.  With the technique Chrystal is suggesting, your query would look like:

SELECT * FROM yourTable where [ClientID] = GetProperty("ClientID")

Select allOpen in new window

hi Dustin,

you're welcome

>> What Happens when it loses its value...It just becomes Blank or something? <<

 if there is an unhandled error, variables can lose their values. Therefore it is like the values were never set.

>> What exactly do you mean by this? Is the Tempvar Dale suggested a Global Variable?<<

TempVars is a collection so it works differently than global variables. I use them sometimes. They are preferable, in my opinion, to global variables. When Access closes, however, they are lost.

I like using  database properties to keep values persistent even when Access closes  (or crashes) and opens again, and explicitely declaring the data type. For example: user and privileges (local_UserID, local_PrivilegeCD), form active records (ie:  local_CID, local_MyTypeID, local_CustomerID, local_OrderID, local_ProductID) for processing code behind other objects and to be put back to that record when they go there again, criteria (including the "friendly" way to say it) and other form and report options (local_Where, local_Friendly, local_View, local_Title, etc) which avoids having to send and parse OpenArgs, whether or not user has admin privileges, if BE is verified to be valid, paths (ie: to back-end, to images and templates for constructing relative addressing, opening stand-alone, using like in RecordSources to render a picture file name using a relative path in an image control), and other settings depending on the application.

thanks for the link, I'll check it out ~

[edit: I just saw Dale's comment ... he said some of the same things ... and shared some nice code :) ]

have an awesome day,
crystal

Ok so as Pat said to keep the form open at all times and make it invisible. Would you see the form tab?

When I post code on a test form with a button and click it it seems to just close completely also the tab.....Or is it actually still there????

Private Sub btnButton1_Click()

On Error GoTo ErrorHandler

Me.Visible = False

Exit Sub

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbCritical, "Error"
End Sub

Select allOpen in new window

Also Can this work on a popup form as well?


Ok I am pretty sure I answered that question. I did the unloading event with a msgbox. That is good to know! Thanks.

hi Dustin,

you're welcome

>> Would you see the form tab? <<

No. When a form becomes not Visible, users cannot see it -- but it is still open, so code and queries and other objects can use it.  Keeping a form open is also another way to increase performance for retrieving data from a back-end, if the form is bound to a table in that back-end.

>>  is it actually still there???? <<

yes. To see this:

press Ctrl-G to go to the Immediate window

type:
forms!MyFormName.Visible = true
and press ENTER

WHERE
MyFormName is the name of your form, assuming the name does not have special characters (except _) or spaces

>>Can this work on a popup form as well?<<
yes -- even though the user cannot see it when Visible is not True, you can get values it stores in code, queries, and other objects.

have an awesome day,
crystal

Well isn't that some Witchcraft ;)

Honestly this form idea is seeming like a best option for me.  I have a few questions to clarify some things.

Would I use on the login form a Invisible Textbox say txtEmpID?
If so would I then use a dlookup to get the value of the EmpID from my employees table WHERE the Username matches the Login Forms User name?

Most of all what are the drawbacks on this way versus other ways. Especially security? (I will be keeping the table on the back end.

Thanks for all the help and clarifying all this.

hi Dustin,

Slick, eh? Access can do so many cool things like this ~

you're welcome

>> Would I use on the login form a Invisible Textbox say txtEmpID? <<

The form would be visible when the information is collected and then then WHOLE FORM would probably be made not visible. Individual controls would probably be visible when the user looks at the form so they can be seen, if they are meant to be seen, when the form is active.

>> If so would I then use a dlookup to get the value of the EmpID from my employees table WHERE the Username matches the Login Forms User name? <<

If all employees will be users then yes, sort of -- I would usually a numeric value, like EmpID for the UserID, not look up a string like name, but --yes, could use that table if there is a flag in there for Active or not, so past employees are still there too -- although you might want to consider a separate Usrs table for this purpose since some users, in the future perhaps?, may not be employees.  DLookup would be one way of getting the value.  Another is using a recordset in vba, or getting the value from a control on a form that is always open (like what Pat suggested). If the form was not open, you could also use a database property.

Another advantage, imo, of database properties is that casual browsers do not usually know how to enumerate them. Access has other database properties (AppTitle, etc), so I preface all of mine with something consistent for that application -- my examples used "local_"

>> Especially security? (I will be keeping the table on the back end. <<
if you are concerned about casual browsers, you can encrypt data for storage in tables and decrypt it for viewing by those with privileges ... this adds complexity. Alternately (not as secure but good enough for most cases), in the table design, set field property InputMask as Password and then a number of * (asterisk)s will display instead of the real data when a table is opened directly (which users shouldn't do!).  


have an awesome day,
crystal

Thank you for the help. This really set me in the right direction!

you're welcome, Dustin ~ happy to help

have an awesome day,
crystal