VBA to check user table

[DaveCrate]

Access 2007
log in form - ado connection to SQL2005
trying to see if user is in table and if match then open form if not after 3 times exit app

Using a WHERE statement but I am not getting any error if user does not match

Private Sub cmdLogIn_Click()

'Check to see if data is entered into the uer id box
If IsNull(Me.txtLogInUser) Or Me.txtLogInUser = "" Then
MsgBox "You must enter your User Name.", vbOKOnly, "Required Data"
Me.txtLogInUser.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password box
If IsNull(Me.txtLogInPassword) Or Me.txtLogInPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.txtLogInPassword.SetFocus
Exit Sub
End If



Dim strLogInUser As String
If txtLogInUser.Visible = True Then
Me.txtLogInUser.SetFocus
strLogInUser = Me.txtLogInUser.Text

Dim strLogInPassword As String
If txtLogInPassword.Visible = True Then
Me.txtLogInPassword.SetFocus
strLogInPassword = Me.txtLogInPassword.Text
Else
End If

'Check value of password in tblEmployees to see if this
'matches value chosen in combo box
'Dim strSQL As String
Dim strSQLLogOn As String

Dim adoLogInConnection As ADODB.Connection
Dim adoLogInRecordset As ADODB.Recordset

Set adoLogInConnection = New ADODB.Connection
Set adoLogInRecordset = New ADODB.Recordset

Dim pw As String
pw = "xxxxxxxx"

adoLogInConnection.Open "Driver={SQL Server};" & _
"Server=serverName;" & _
"DataBase=myDatabase;" & _
"User ID=UserID;" & _
"Password=" & pw

adoLogInRecordset.CursorType = adOpenKeyset
adoLogInRecordset.LockType = adLockOptimistic
adoLogInRecordset.Open "tblUserList", adoLogInConnection, , , adCmdTable

strSQL = "Select tblUserList.[fldUserFirst], tblUserList.[fldUserLast] " & _
"From tblUserList " & _
"Where tblUserList.[fldUser] ='" & strLogInUser & "';"

MsgBox ("User '" & strLogInUser & "' is logged on now!")

procLogUser ' procedure writes username and time stamp to server table

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogIn", acSaveNo
DoCmd.OpenForm "frmWork" 'for now open to frmWork during testing

adoLogInRecordset.Close
adoLogInConnection.Close

Set adoLogInRecordset = Nothing
Set adoLogInConnection = Nothing


End Sub

I do have procedure to write to log in table - it is working properly at this time

 

[HarleyQuinn]

You never actually use the strSQL you create, try something like this:

adoLogInRecordset.CursorType = adOpenKeyset
                adoLogInRecordset.LockType = adLockOptimistic
                                
strSQL = "Select tblUserList.[fldUserFirst], tblUserList.[fldUserLast] " & _
"From tblUserList " & _
"Where tblUserList.[fldUser] ='" & strLogInUser & "';"

adoLogInRecordset.Open strSQL, adoLogInConnection

If not adoLogInRecordset.EOF and not adoLogInRecordset.BOF then' check a record is returned

    MsgBox ("User '" & strLogInUser & "' is logged on now!")
             
   procLogUser ' procedure writes username and time stamp to server table
        
'Close logon form and open splash screen
    
                DoCmd.Close acForm, "frmLogIn", acSaveNo
                DoCmd.OpenForm "frmWork" 'for now open to frmWork during testing

else
    msgbox "Not a valid user"
end if

                adoLogInRecordset.Close
                adoLogInConnection.Close
                
                Set adoLogInRecordset = Nothing
                Set adoLogInConnection = Nothing

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[DaveCrate]

If I wanted to check a second field from the table like "password", can I just add to SQL statement?
"Where tblUserList.[fldUser] ='" & strLogInUser & "' and tblUserList.[fldPassword] ='" & strstrLogInPassword & "';"

Thanks HarleyQuinn!

As you can tell i just want to check to see if someone has registered

 

[HarleyQuinn]

Dave, you certainly can do it as you posted

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[DaveCrate]

HarleyQuinn,

Thanks for the help. I asked the question because I kept getting a invalid user error when I added the "strstrLogInPassword". I looked at this after your reply because it sounded simple enough. It was my mistake. If it had been a snake; it would've bit me.

here is what I ended up with

adoLogInRecordset.CursorType = adOpenKeyset
adoLogInRecordset.LockType = adLockOptimistic

strSQL = "Select tblUserList.[fldUserFirst], tblUserList.[fldUserLast] " & _
"From tblUserList " & _
"Where tblUserList.[fldUser] ='" & strLogInUser & "' AND tblUserList.[fldPassword] ='" & strLogInPassword & "';"

adoLogInRecordset.Open strSQL, adoLogInConnection

If Not adoLogInRecordset.EOF And Not adoLogInRecordset.BOF Then ' check a record is returned
MsgBox ("User '" & strLogInUser & "' is logged on now!")

Thanks again!
David
Are you aware of a thread that checks the number of attempts then locks the acount out...or something to that affect

 

[HarleyQuinn]

Glad it's working now

No need for a thread, we can wrap that up now (well, we'll wrap it up one way and then I'll leave you with the idea for a more longer lasting approach) add a variable (named intLoginCounter) under the Option Compare Database line in your form's code. When you click the login button check to see if the value of intLoginCounter = 3, if it does then exit the sub, if not then try the login code. If login fails increment the counter, something like this:

If intLoginCounter < 3 then             
adoLogInRecordset.CursorType = adOpenKeyset
                adoLogInRecordset.LockType = adLockOptimistic
                
strSQL = "Select tblUserList.[fldUserFirst], tblUserList.[fldUserLast] " & _
"From tblUserList " & _
"Where tblUserList.[fldUser] ='" & strLogInUser & "' AND tblUserList.[fldPassword] ='" & strLogInPassword & "';"

adoLogInRecordset.Open strSQL, adoLogInConnection

If Not adoLogInRecordset.EOF And Not adoLogInRecordset.BOF Then ' check a record is returned
    MsgBox ("User '" & strLogInUser & "' is logged on now!")
Else
    intLoginCounter = intLoginCounter + 1
End If

End if

This option will work correctly while the app is open, once the user closes it then the counter is reset to 0 on loading of the application.

A more long term lock out can be achieved by saving thre number of invlaid logins into the database and checking against that rather than a form level variable as this value will persist between sessions (you can also do this on a 'per user' basis based on the attempted login).

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com