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