Username and Password validation in Access 1

[gchaves]

I have created a pretty extensive database in access that tracks all sales for our company. In order for our sales reps to be able to have access to the reports, I would like to have them "log in" to the database by entering a username and password. I have a form which asks for their username and password. I also have a table which stores their username and password. I have a SELECT query which looks for the username and password that the user enters into the login form. What I need now is an IF THEN statement that pretty much says "IF username <> NULL (username exists in the USERS table) THEN IF password <> NULL (password exists in the USERS table) THEN allow the user access into the reports section of the database ELSE give them an error message that says "You entered an incorrect username, password or both! Please try again!" (and redirects them back to the login form). In the ASP world, I have done this using sessions...but am not too sure how to simulate a "session" in Access. Any help would be greatly appreciated!

 

[randysmid]

Hi,
I've done something a bit different. Inside the login form I have a command button labelled "Login". It is here that I wrote custom code to check my "User_security" table for a valid entry. Here is the code:

'Open the user_security table
Dim DbInfo As Database
Dim rstSecurity As Recordset
Set DbInfo = CurrentDb()
Set rstSecurity = DbInfpenRecordset("User_security", dbReadOnly)

' at this point, the user should have entered the initials into "temp_initials"
' Find the record that matches the control.
rstSecurity.FindFirst "[User] = '" & Me![temp_initials] & " '"
If rstSecurity.NoMatch Then
MsgBox "Invalid login, no such user!", vbOKOnly + vbCritical
DoCmd.Quit acQuitSaveNone
End If

' the user should have entered a password into "temp_password"
If rstSecurity![user_password] = temp_password Then
'MsgBox "Valid login", vbOKOnly + vbInformation
gstrUser = Me!temp_initials.Value
'DoCmd.Close acForm, Me.NAME
Me.Visible = False
DoCmd.OpenForm "MainMenu", , , , , acWindowNormal
Else
MsgBox "Invalid password, try again or Quit Application!", vbOKOnly + vbCritical
temp_password.SetFocus
End If



HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association

 

[gchaves]

I tried using the code you provided (thanks very much, by the way!) and am receiving the following error when I click my "Login" button:

The expression OnClick you entered as the event property setting produced the following error: Invalid Outside procedure

*the expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]
* There may have been an error evaluating the function, event, or macro.

Here is the information pertinent to my database:

Database Name: Admin_sales_tracking.mdb
Table Name: users
Form Name: LoginForm
username field: user_name
password field: password

and the code that you gave me:

'Open the users table
Dim DbInfo As Database
Dim rstUsers As Recordset
Set DbInfo = CurrentDb()
Set rstUsers = DbInfpenRecordset("users", dbReadOnly)

' at this point, the user should have entered in their username into the form
' find the record that matches the control
rstUsers.FindFirst "[user_name]='" & LoginForm![user_name] & "' "
If rstUsers.NoMatch Then
MsgBox "Invalid Login, no such user!", vbOKOnly + vbCritical
DoCmd.Quit acQuitSaveNone
End If

' the user should have entered a password into "password"

If rstUser![password] = password Then
MsgBox "Valid login", vbOKOnly + vbInformation
gstrUser = LoginForm!user_name.Value
DoCmd.Close acForm, Me.Name
Me.Visible = False
DoCmd.OpenForm "Admin", , , , , acWindowNormal
Else
MsgBox "Invalid Password, try again or Quit Application!", vbOKOnly + vbCritical
password.SetFocus
End If

Am I coding this incorrectly???

thanks!!!!

 

[randysmid]

Hi,
Yes, I believe it is coded correctly, but you may not have the references set up. While you are in the Visual Basic code editor, click on Tools, then References. Can you give a quick list of those items that have check marks in front of them? You can use "M" for Microsoft as an abbreviation.

HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association

 

[gchaves]

This is what is checked in Tools,References:

Visual Basic for Applications
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library

 

[randysmid]

Hi,
Here are the references I have set for my Windows XP Pro machine with Office 2000 installed:

----> ("M" means Microsoft):
Visual Basic for Applications
M Access 9.0 Object Library
M DAO 3.6 Library
OLE Automation
M Visual Basic for Applications Extensibility 5.3
M Outloook 9.0 Library
M Office 9.0 Library
M ActiveX Data Objects 2.5 Library
M ActiveX Data Objects Recordset 2.6


HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association

 

[gchaves]

Awesome! I am happy to say that my login form is working great! Thank you much! NOW...how can I get the form to update my login history table to track who is accessing the site when and for how long? I had this working when I was using a Macro...but not sure how to program it in VB. I would need to use an INSERT query to add each new record...any ideas?

Again...Thanks for the help!!!

 

[gchaves]

After studying your code, I have also made a couple of updates to the code you gave me which you may find useful. In your original code, even if I enter an invalid username, as long as I entered an valid password, I could gain access to the site. I added a few lines of extra code that prevents this from happening...here is the modified code with new code in bold type:

Private Sub LoginBtn_Click()

'Open the users table
Dim DbInfo As Database
Dim rstUsers As Recordset
Set DbInfo = CurrentDb()
Set rstUsers = DbInfpenRecordset("users", dbReadOnly)

'at this point, the user should have entered the initials into "user name" field
'find the record that matches the control.
rstUsers.FindFirst "[user_name] = '" & Form_LoginForm![user_name] & "'"
If rstUsers.NoMatch Then
MsgBox "Sorry '" & Form_LoginForm![user_name] & "'! You have entered an invalid User Name. If you would like to request a username and password, please contact Greg Chaves!", vbOKOnly + vbCritical
user_name.SetFocus
'DoCmd.Quit acQuitSaveNone
End If

'the user should have entered a password into "password" field
If rstUsers.NoMatch = False Then
If rstUsers![password] = Form_LoginForm![password] Then
'MsgBox "Valid Login", vbOKOnly + vbInformation
gstrUser = Form_LoginForm!user_name.Value
DoCmd.Close acForm, Form_LoginForm.user_name
Form_LoginForm.Visible = False
DoCmd.OpenForm "Admin", , , , , acWindowNormal
Else
MsgBox "Sorry '" & Form_LoginForm![user_name] & "'! You have entered an invalid Password. If you would like to request a username and password, please contact Greg Chaves!", vbOKOnly + vbCritical
password.SetFocus
End If
End If


End Sub


Thanks again for all of your help!

 

[randysmid]

Hi,
I am surprised that the code allowed a user into the system without a valid username. The "findfirst" function is suppose to only do an exact match.

Anyway, you won't need a query to insert new records into a table that might be called UserHistory. By using behind the scenes VBA code (similar to the code above), you can add a new record with a date and time stamp. The difficulty might come from writing code for application exit event, if there is one. (either that, or my brain is dead about now - which could be true for Friday afternoon).

Once you have your recordset setup, you can add new records with code like this:

'Open the UserHistory table
Dim DbInfo As Database
Dim rstUserHistory As Recordset
Set DbInfo = CurrentDb()
Set rstUserHistory = DbInfpenRecordset("UserHistory")
db.AddNew
db![user_name] = user_name
db![EntryDateTime] = Now
db![ExitDateTime] = 0 '????
db.Update

How would you find this record when it is time to update the ExitDateTime field? Possible solutions: 1) add a unique number and hold it as a global variable, just like gstrUser, OR 2) use FindLast method for that particular user.

Because the user can exit the application from a number of places, I cannot think of an "ApplicationExit" event that can be coded to update the ExitDateTime field. SO, can you post another question on Tek-Tips for this?



HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association

 

[randysmid]

Hi,
Here is a possible solution to the problem of getting the date and time when the user exits the application. As you know, the user can exit the database in alot of different ways. So, by leaving one window open (perhaps the Login window), any attempt to exit the application will cause the form unload event to fire. This is where the "rstUserHistory.Update" method can live.

By the way, I had some code from another database in the code previously posted here. All the db! and db. should be replaced with "rstUserHistory".

HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association

 

[gchaves]

Thanks! The code does capture the username, password(with an input mask to hide the true data), the users first name and last name and the date and time that they logged into the application. Perfect! Now, I have been having some issues with coding the app to record the logout date and time. I've tried it in several places...the latest being on the properties of the login form as an on unload event. Here is the code I was using:

Private Sub Form_Unload(Cancel As Integer)

'Open the login_history Table
Dim DbInfo As Database
Dim rstlogin_history As Recordset
Set DbInfo = CurrentDb()
Set rstlogin_history = DbInfpenRecordset("login_history")
rstlogin_history.FindLast "[user_name] = '" & Form_LoginForm![user_name] & "'"
rstlogin_history.AddNew
rstlogin_history![Logout_Date] = Now
rstlogin_history.Update
'End If

End Sub

I want to open the recordset login_history table, where the login information is stored. I then want to find the last instance of the user's username and record the logout date in the field Logout_Date. Am I putting this code in the right spot? Am I even coding this correctly? Please help!!! Thanks!

 

[gchaves]

Actually...I found the one, tiny little error that was causing the bug in the above code...it's so silly, I feel silly even reporting it. It took my over a day to find cause it was so small. My code was looking for a field called Logout_Date. HOWEVER...the field in the table was named Logout_ Date. THAT ONE LITTLE SPACE WAS THROWING EVERYTHING OFF!!! I removed that space and voila...it is writing the logout date and time to the login_history table.

The only issue there is now is that it is writing the login information on one line and the logout time on the next line below it? If you can look at my code and help me determine how to fix that...I would be greatful!

Thanks!

 

[randysmid]

Hi,
I think you have the correct idea above, but you won't want to create a new record with the Addnew in the close event. AND, you wouldn't want to do the .update in the Login. That means your original rstlogin_history.AddNew is still pending, and won't ever get written if there is a power failure. The final rstlogin_history.Update is done in the close event of the form.

HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association

 

[gchaves]

I kinda, sorta follow you...so all I should have as VB code for my login form is an event procedure that checks for a valid username and password and, if they are both validated, write the username, password and login information to the login_history table? I have moved the code to record the logout information to the on close property of my admin form...where users are prompted to click an exit button to close the form (I have turned off all other close features and am trying to disable the main Access close app button at the top right of the app). However, I am not sure how to code it so that it writes the logout time on the same line. If I remove the .AddNew line, the logout time does not write. I'm sorry for all the back and forth...you've been a great help! This is my first attempt at trying to code such functionality.

 

[randysmid]

Hi,
The idea is to write the first part of the line when the user logs into your system, but without doing the update (which is where the record is really being written). Then, on the form close event, you will add one additional item to the line, and that is the logout date/time. THEN you will do the .update. In other words, the transaction to write the record is pending (maybe up to 8 hours!!!).

Otherwise, you can create a global variable in modPublic that will keep track of a unique number for that person. You would then be able to write the record upon entry into the system right away (with the .update), but you would include this unique number. When the user exits the system, the Login close event would do a FindFirst using the user ID AND unique number. Then you could do the rstlogin_history.edit, insert the logout date/time, and finally, the rstlogin_history.update.

This is demonstrated in my frmChangePassword in my coredatabase.MDB. Did you get a copy of it? If not, send me an email and I will send to you right away.

HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association