login form enter button click error

[Jean-Francois Gauthier]

Hi,

I have the following code on the cmdok button on my login form. I think
there is soemthing wrong with my code, but I can't pin point what. I put the
correct user id and password as is in the table, however I always get the
access denied eror message and doesn't log me in. I'd appreciate if a second
pair of eyes could look at this code and tell me what is wrong. The password
field is made of numbers and letters (that is still a string right?).
Basically I get the if valid_user = 0 or valid_user = 1 cases at the end of
code, but what I am entering should be bringing back case wehre if valid_user
= 2. Any help is appreciated.

Option Compare Database
Option Explicit
' ******************************************************
' close form
' ******************************************************
Private Sub close_form_Click()
On Error GoTo Err_close_form_Click

DoCmd.RunMacro "macro_exit"

Exit_close_form_Click:
Exit Sub

Err_close_form_Click:
msgbox Err.Description
Resume Exit_close_form_Click
End Sub

' ***********************************************************
' User has entered user_id and Password
' ***********************************************************
Private Sub cmdok_Click()
On Error GoTo Err_cmdOK_Click

Dim valid_user As Integer
Dim check_user As String
Dim password_period As Date
Dim check_password As String
Dim strmsg As String
Dim userpass As Integer
Dim passpass As Integer

' Validate User ID and Password and then display menu items
' **********************************************
' validate user_id
' **********************************************
check_user = DLookup("[user_id]", "tbl_users", "user_id = " & "'" &
Me.user_id & "'")
If UCase("'" & Me.user_id & "'") = UCase(check_user) Then
userpass = 1
Else
If "'" & Me.user_id & "'" <> UCase(check_user) Then
userpass = 0
End If
End If

If IsNull(Me.password) Then
passpass = 0
End If

If Not IsNull(Me.password) Then
' **********************************************
' validate password
' **********************************************
If valid_user = 2 Then
check_password = DLookup("[passwords]", "tbl_users", "user_id = " &
"'" & Me.user_id & "'")
If UCase(check_password) = UCase("'" & Me.password & "'") Then
passpass = 1
Else
passpass = 0
End If

End If
End If

valid_user = userpass + passpass

If valid_user = 0 Then
strmsg = " Access Denied" & _
vbCrLf & "The user ID or password could not be validated. Please try
logging in again. Contact your Administrator if the problem persists. "
msgbox strmsg, vbInformation, "INVALID USER ID or PASSWORD"
DoCmd.Quit
End If

If valid_user = 1 Then
strmsg = " Access Denied" & vbCrLf & "The user ID or password could not
be validated. Please try logging in again. Contact your Administrator if the
problem persists. "
msgbox strmsg, vbInformation, "INVALID USER ID or PASSWORD"
End If

If valid_user = 2 Then
' validate password expiry
password_period = DLookup("[password_date]", "tbl_users", "user_id = " &
"'" & Me.user_id & "'")
If password_period < DATE - 90 Then
strmsg = " Your password has expired. You must change your password"
msgbox strmsg, vbInformation, "Expired Password"
DoCmd.OpenForm "frm_change_password", acNormal
Else
DoCmd.Close
DoCmd.OpenForm "welcomescreen"
DoCmd.OpenForm "invisibleuserid"
Forms!invisibleuserid.userid = Forms!welcomescreen.userid
End If
End If

Err_cmdOK_menu:
Exit Sub

Exit_cmdOK_Click:
Exit Sub

Err_cmdOK_Click:
msgbox Err.Description
Resume Exit_cmdOK_Click
End Sub




' ******************************************************
' maximizee the screen
' ******************************************************
Private Sub Form_Load()
On Error GoTo Err_Form_Load
DoCmd.Maximize


Exit_Form_Load:
Exit Sub

Err_Form_Load:
msgbox Err.Description
Resume Exit_Form_Load

End Sub



' ****************************************************************
' when the user id gets the focus, the user id and password are reset to blank
' ****************************************************************
Private Sub user_id_GotFocus()
On Error GoTo Err_user_id_GotFocus

'remove current user and password
Me!password = Null

Exit_user_id_GotFocus:
Exit Sub

Err_user_id_GotFocus:
msgbox Err.Description
Resume Exit_user_id_GotFocus
End Sub