Multi user login screen - question about tracking logged in users

[anewuser]

Hi,

I currently have a working multi user login form which automatically directs users to the relevent forms e.g. general staff have access to a form called staff1 and staff2 whereas managers have access to a form called manager1. I have used the following code:

Option Compare Database
Private Sub Login_Click()
UserName.SetFocus
If UserName = "Staff1" And Password = "Staff1" Then
MsgBox "Welcome"
DoCmd.Close
DoCmd.OpenForm "Staff1"
ElseIf UserName = "Staff2" And Password = "Staff2" Then
MsgBox "Welcome"
DoCmd.Close
DoCmd.OpenForm "Staff2"
ElseIf UserName = "Manager1" And Password = "Manager1" Then
MsgBox "Welcome, please exercise caution when changing query or table conditions", vbInformation, "CDSignatures"
DoCmd.Close
DoCmd.OpenForm "Manager1"
Else
MsgBox "Please re-enter Username and Password"
End If
End Sub

My question is how can I track users once they have logged in. I have a database which tracks data that has been editted from the main database and I would like to know how to show which user has done this.

Thanks in advance.

[TheSmileyCoder]

I use a combination of alot of different techniques picked up over the years.

First I have a table of users with the fields:
PK_User (Autonumber, primary key)
tx_UserInitials (in my case matching the username of people logged in) (Text Field)
tx_FirstName (Text Field)
tx_LastName (Text Field)
b_Admin (Yes/no field)


In your login screen, you can capture the username, and match it to a ID in your user table. This could be done in the login forms open event

Private Sub Form_Open(Cancel as Integer)
  Dim strUserInitials as String
  strUserInitials=Environ("UserName")

  'Match user initials to a user
    Dim lngUserID as long
    lngUserID=nz(Dlookup("PK_User","tbl_User","tx_UserInitials='" & strUserInitials & "'"),0)
    If lngUserID=0 then
      'Means user was not found in table of users. Inform user 
      ' to contact a database admin
      Cancel=True
      MsgBox "You are not a registered user" & vbnewline & _
             "Please contact John Doe by email JD@MyCompany.Com",vbokonly+vbinformation
 
      Docmd.Quit
    End If

  'We made it to here, so User is recognized
  'Store information in local table for easy access
  CurrentDB.Execute "UPDATE uSysCurrentUser set CurrentUser=" & lngUserID
End Sub

First off, for the above to make sense it requires that each user uses a individual frontend, and that the frontend contains a uSysCurrentUser table with 1 record allready created, and the table should contain a number field named currentuser



With the above code, we register the user as he open the DB.

Now step 2 is tying his user identity to each record he or she creates. That means we need fields in each table called:
ID_CreatedBy Number, Long
Date_Created, DateField
ID_ChangedBy, Number, Long
Date_Changed, DateField

In every form you need to modify the beforeUpdate event with the following code:

Private Sub Form_beforeUpdate(Cancel as Integer)
  If me.NewRecord then
    me.ID_CreatedBy=UserID()
    Me.Date_Created=Now()
  End If
    Me.ID_ChangedBy=USerID()
    Me.Date_Changed=Now()
End Sub

Now UserID is a custom function, that simply looks into the table uSysCurrentUser . The function should be placed in a public module, so it can be called from anywhere

Public Function UserID() as Long
  UserID=Dlookup("CurrentUser","uSysCurrentUser")
End Function

[anewuser]

Firstly thanks for your help.

Just one question, do I need to use your code instead of my code on the login form? or is it in addition to my code. Sorry, this feels like a stupid question but I am very new to access.

[TheSmileyCoder]

The above is simply an example of how to record the user logging into the db, and how to use that information to mark records with his userID. At this point none of my code really relates to what rights the user has. This could be setup in a the user table, or in a seperate table.

[anewuser]

Hi, I am having problems with the first code provided. Ih ave myself set up in the tbl_User table and yet when I am trying to login it tells me that I am not registered and closes the program. Am I doing something wrong.

And by the way I don't know if this will make a difference but the front end will only be accessible on a couple of computers but different users will need access to it.

[TheSmileyCoder]

Try this in your VBE windows immediate pane:

Debug.Print Environ("UserName")

What the code shows you, is what should be stored as initials in the tbl_User. It might make sense to call it something else, depending on your company setup, but where I have worked the most common thing is to have the username be the initials of the persons working there.


If people are using the client from shared workstations that are "open" all the time, then the Environ function won't give the results you are after. The Environ returns the username of the person who logged into windows during startup. So if its a shared PC where each individual does not log off and back on again, then
you need a different approach.

[anewuser]

Is there a way of doing it so that the Username and Password can be identified from the tbl_User table?

[NeoPa]

If multiple users require access to the database from a single machine / logon then you will need something where the user selects or types their own ID. Giving access based simply on the Windows logon code will not suffice in such circumstances.

[anewuser]

What I have is a login form with a username and password field and a table, "tbl_User", which I would like to use to store user details i.e. Username, first name, surname, password, admin (yes/no) as suggested earlier. I have the source of the form set to tbl_User and what I am trying to do is to allow users to type their username and password into the relevant boxes and use a code to identify that they are in the table and what access rights they have.

However, at the moment I am having trouble simply getting the form to identify the user. I have tried using the nz(Dlookup...) function shown earlier in the thread and I have also tried running it through a query. I just can't seem to get it to work.

[NeoPa]

Typically, you wouldn't need, or even want, the form to be bound to any table. The process would be that the user enter the UserName and Password in unbound TextBoxes then click on a button to submit the logon attempt. The code behind the button would first encrypt the password using the same function as was used to encrypt the passwords stored in the table (Never store passwords unencrypted as that's asking for trouble. There are threads on this site that can help with that side of things - AES Encryption Algorithm for VBA and VBScript, RC4 Encryption Algorithm for VBA and VBScript & SHA2 Cryptographic Hash Algorithm for VBA and VBScript.) then it finds the values for the [Password] and [Tries] fields from the table for the matching record. I suggest you do this using a DAO.Recordset as you will need to update [Tries] when done.

If the encrypted password entered matches that found in the record then you have entry. Reset [Tries] to zero.

If the encrypted password entered doesn't match that found in the record then you have a failed attempt. Increment [Tries].

If there are other field values you may need from this table later on in your session then make sure you get them and make them available somewhere accessible for use later.

[Rabbit]

You may want to read this article: http://bytes.com/topic/access/insigh...atabase-access

[anewuser]

Hi, I am still having trouble with the login form I can't get the code to compare the username and password typed in with the one in the table. I need some help as I have tried numerous different methods e.g. running through a query, applying a filter and using the Nz(DLookup... mentioned earlier. I don't know how else I can do this.

[anewuser]

I have now made some progress, possibly, the code will now let people type into the Username and password boxes and will allow them entry, however, it will also let in those whose usernames and passwords are not in the tbl_User. Below is the new code I have used and I need some advice on improving it.

Option Compare Database

Private Sub Login_Click()
UserName.SetFocus
UserName = DLookup("UserInitials", "tbl_User", "UserInitials =" & strUserInitials & "UserName")
Password = DLookup("Userpassword", "tbl_User", "Userpassword =" & strUserpassword & "Password")
If True Then
    DoCmd.Close
    MsgBox "Welcome"
    DoCmd.OpenForm "Staff1"
End If
End Sub

[NeoPa]

First, please check out Require Variable Declaration. This is a very important point and you will benefit greatly by taking it on board at this early stage of your development.

Anyway, try the following. I'm not sure all the control and field names are correct but you should be able to fix that up easily enough :

Option Compare Database
Option Explicit

Private Sub Login_Click()
    Dim strWhere As String, strPassword As String

    strWhere = "([UserInitials]='%I') AND ([UserPassword]='%P')"
    strWhere = Replace(strWhere, "%I", Me.UserName)
    strWhere = Replace(strWhere, "%P", Encrypt(Me.Password))
    If Not IsNull(DLookup("UserInitials", "tbl_User", strWhere)) Then
        Call DoCmd.Close
        Call MsgBox("Welcome")
        Call DoCmd.OpenForm("Staff1")
    End If
End Sub

Private Function Encrypt(strText) As String
    Encrypt = strText
End Function

The Encrypt function I've used here is just a dummy. It's there to illustrate how you should be working with such data. Earlier posts include links that can help you to produce a function to handle this for yourself (Mainly just Copy / Paste TBF).

[anewuser]

Thanks for the previous code, with a few small tweaks I have got it working perfectly, thank you. I have one more question, in my tbl_User I have two fields regular and admin (both yes/no) I would like to allow some users to access the form "staff2" if the regular box is yes and others to access the form "manager1" if the admin box is yes.

If you can break down the code above for me I will give it a try myself. I don't really understand it and may be able to get some results if I understand it better.