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

I have most of this set up. The things I have already.
  • Login Page Form on AutoExec
  • Employee/User Table With fields like EmpID (Key), Username, Password, First Name, Last Name, Etc.....
  • On Every Table in the Database a Foreign Key EmpID field and Relationship on the Foreign Key EmpID to the Employees Table EmpID Key.

This is where I am stuck....
How do I set up for Access to retain the EmpID after successful login and apply that to all the altered database tables when someone changes them?  

Basically how does Access know what EmpID is currently using the database and changing records?

Thanks for the help!
LVL 1
Dustin StanleyEntrepreneurAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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

Open 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

Open 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

Open 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

Open in new window

have an awesome day,
crystal
0
Dustin StanleyEntrepreneurAuthor Commented:
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!
0
Dale FyeCommented:
Usually, I store that info in a tempvar after the user has entered their name and password (or after I've validated their Active Directory groups).  Then I can set the default value of controls on forms based on this tempvar.

So, after they have validated the password check, I would store the EmpID (although I actually prefer to use the Windows loginID of the user logged into the computer).

Tempvars!UserID = fosusername()  

You can find the function for fosusername here.  Then, I would set the default value of the control that contains my ModifiedBy field something like:

Default Value: = Tempvars!UserID
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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

Open in new window

1
Dustin StanleyEntrepreneurAuthor Commented:
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
0
Dale FyeCommented:
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

Open 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

Open in new window

Then, in my query I would use:
SELECT * from yourTable where [ClientID] = [Tempvars]![ClientID]

Open 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

Open 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")

Open in new window

1
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
0
PatHartmanCommented:
Call me a neanderthal but I use a very simple method.  I use a bound login form that hides itself (Me.Visible = False)  before it opens the menu so the login form controls are always available.  That leaves me with two lines of code in every BeforeUpdate event:

Me.ChangeBy = Forms!frmLogin!UserID
Me.ChangeDT = Now()

My login form also stores certain security info that allows me control who can do what as well as providing the source for the logged in userID
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
keeping a form open is another option, as Pat suggested. One advantage she did not mention (with a form that is always open) is that you can use that form Unload event to (maybe, sort of, kind of, mostly) know when a user closed the database ... for instance, if you are keeping track of who is in the database and when they opened and closed it (you can also use another procedure to get a list of active users if you want to find out ad-hoc)

For the users with a static FE that is not shared, maybe you want to also set the user in a database property? This lets you control things better when the database is opened and is just one less thing for them to do to do ... and then if you keep track of where they were (local_FormID) and what record they were on, you might even just want to put them right back there ... if circumventing a main menu would be a good thing to do.
0
Dustin StanleyEntrepreneurAuthor Commented:
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

Open 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.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
0
Dustin StanleyEntrepreneurAuthor Commented:
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.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
0
PatHartmanCommented:
I was on my way out so I didn't elaborate on what else the form can do for you..
1. I use its unload event to check who is logged in and if it is me, ask if I want to make a backup if I have changed any objects.  I keep a log table that tracks when I make backups so that date/time is compared to the date/time of all the objects in MSysObjects to determine if something was changed (not foolproof but good enough).  This ensures that I remember to back up frequently when I am making design changes.  In fact, the backup goes to two folders.  One on my hard drive and another on a server folder.  For apps I build that will be managed and modified in the future by others, I make the backup automatic if they have changed any objects because users cannot be trusted to do this themselves.  Usually, my users are non technical and so would never be changing objects but I have several at one client site that are power users and the apps I build are essentially to give them a leg up and then they take over.
2. As Crystal mentioned, keeping a bound form open minimizes the open/closing of the BE when it is ACE.  It makes no difference for SQL Server BE's.
3. The form is bound to a query that has a WHERE clause that uses the UNBOUND userID AND Password  controls as criteria.  The login button requeries the form and I then check to see if certain fields are populated.  If they are, then the login was successful and the code hides the login form and opens the menu.  If the required fields are not populated then either the userID or password is invalid and I give the user an error message and leave the login form visible.  I deliberately do not distinguish between invalid userID and invalid password.  The less info you give a hacker, the harder it is to hack so don't be too helpful.
0
Dustin StanleyEntrepreneurAuthor Commented:
Thank you for the help. This really set me in the right direction!
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome, Dustin ~ happy to help

have an awesome day,
crystal
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.