Login Problems

[tanker]

I am having some trouble making my login screen work properly. I have an Access database named eiss.mdb. The table in the database is called [Security]. And the fields in that table are called [Username] & [Password]. In my program I have a login screen that has two text boxes; Username and Password; and two Command buttons; OK and Cancel. My question to you is what code would I use to make this login screen validate the username and password every time? Also what else do I need to add; if anything; to the login screen to make this work? I am including the code that I have right now. . .it doesn't work but maybe it will be somewhere to start.

Private Sub cmdCancel_Click()
End
End Sub

Private Sub CmdOK_Click()

'On your CmdOK_Click event of the login screen.

Select Case ValidateUser(txtUserName, txtPassword)
Case 0
MsgBox "Successful Login.", vbOKOnly + vbInformation, "Logon"
Me.Hide
frmMain.Show

Case 4
MsgBox "Successful Login", vbInformation + vbOKOnly, "Logon"
Me.Hide
frmMain.Show

Case 1
MsgBox "Invalid User ID.", vbInformation + vbOKOnly
txtUserName.SetFocus
Case 2
MsgBox "Invalid User Password.", vbInformation + vbOKOnly
txtPassword.SetFocus
End Select

End Sub

Private Function ValidateUser(ByVal lpUserID As String, ByVal lpUserPwd As String) As Integer

Data1.Recordset.Index = "UserID"
Data1.Recordset.Seek "=", lpUserID

If Not Data1.Recordset.NoMatch Then
If StrComp(lpUserPwd, Data1.Recordset!UserPwd, vbBinaryCompare) = 0 Then
If Data1.Recordset("Specialuser") = True Then
ValidateUser = 0
ElseIf Data1.Recordset("SpecialUser") = False Then
ValidateUser = 4
End If
Else
ValidateUser = 2
End If
Else 'if there is no match in the userid
ValidateUser = 1
End If

End Function

[Gary.Lowe]

Tanker

Hope this helps, youshould be able to mess about with it to suit your needs

Private Sub cmdOK_Click()
Dim strUser As String
Dim strPWD As String
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

'//Validate the text boxes to ensure the user has entered something into them
If IsNull(txtUsername) Or txtUsername = "" Then
MsgBox "please input your log on ID as this will be needed to access the system," _
, vbOKOnly
txtUsername.SetFocus
Exit Sub
End If

If IsNull(txtPassword) Or txtPassword = "" Then
MsgBox "please input your log on Password as this will be needed to access the system," _
, vbOKOnly
txtPassword.SetFocus
Exit Sub
End If

strUser = Me.txtUsername
strPWD = Me.txtPassword

'Open a connection to the database
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "C:\MyDB"
End With

'Create SQL to search the user table.
sSQL = "SELECT * From Security WHERE UserName='" & strUser & "' AND Password='" & strPWD & "';"

'Open the recordset
Set rs = New ADODB.Recordset
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
rs.Open sSQL, cnn

'Check to see if a record was returned
If rs.RecordCount = 0 Then
'Username or password do not exist
MsgBox "The username or password you have entered is incorrect..."
txtUsername = ""
txtPassword = ""
txtUsername.SetFocus
Exit Sub
End If

'Succesfull log on if you have reached this far
Me.Visible = False
Load frmMain
strUsername = ""
strPassword = ""
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
End Sub