UserName and Password for a DB

[solrac_otos]

Good morning all.

I would like to implement a login form to my DB.

I have a table db-users with three fields: empID; empName; empPassword

I have a form with an unbound combo box: cmbdbUserName
And I have a text box: txtPassword where the user will type in their password.

cmdDBLogin is the button I want to add the VBA code to on click.

Can I get some assistance on the code that will compare the inputs of the two objects listed above to the data in the table fields listed above that?

Your help is greatly appreciated.

-C.

 

[pr2-eugin]

Something like this?

Private Sub cmdDBLogin_Click()
    If Len(Me.cmbdbUserName & vbNullString) = 0 Then
        MsgBox "You cannot leave the UserName field empty, please try again.", vbCritical
        Exit Sub
    End Sub
    
    If Len(Me.yourPassword & vbNullString) = 0 Then
        MsgBox "You cannot leave the Password field empty, please try again.", vbCritical
        Exit Sub
    End Sub
    
    If Me.yourPassword <> Nz(DLookup("empPassword", "db-users", "empName = '" & Me.cmbdbUserName & "'"), "HellNo") Then
        MsgBox "You do not have access to the DataBase, please contact the Admin.", vbCritical, "NO ACCESS !"
    Else
        MsgBox "Welcome " & Me.cmbdbUserName & " !", vbInformation 
    End If
End Sub

 

[solrac_otos]

Seems pretty straight forward. I tweaked it to use all of my object names and ran it but got the error: Compile error: Block If without End If

When I click ok and it takes me to the code it highlights the top line below:

Private Sub cmdDBLogin_Click()
If Len(Me.cmbdbUserName & vbNullString) = 0 Then
MsgBox "You cannot leave the User Name field empty, please try again.", vbCritical
Exit Sub
End Sub

If Len(Me.txtPassword & vbNullString) Then
MsgBox "You cannot leave the Password field empty, please try again.", vbCritical
Exit Sub
End Sub

If Me.txtPassword <> Nz(DLookup("empPassword", "db-users", "empName = '" & Me.cmbdbUserName & "'"), "HellNo") Then
MsgBox "You do not have access to the DataBase, please contact the Admin.", vbCritical, "NO ACCESS !"
Else
MsgBox "Welcome " & Me.cmbdbUserName & " !", vbInformation
End If
End Sub

Ideas?

Thanks again for the help.
-C.

 

[pr2-eugin]

Try including the code in CODE tags, with proper indenting. Then even you could see the error.

 

[solrac_otos]

Here is the code. Sorry...I still don't see where the issue is. I am relatively new to VBA.

Private Sub cmdDBLogin_Click()
If Len(Me.cmbdbUserName & vbNullString) = 0 Then
        MsgBox "You cannot leave the UserName field empty, please try again.", vbCritical
        Exit Sub
    End Sub
    
    If Len(Me.txtPassword & vbNullString) Then
        MsgBox "You cannot leave the Password field empty, please try again.", vbCritical
        Exit Sub
    End Sub
    
    If Me.txtPassword <> Nz(DLookup("empPassword", "db-users", "empName = '" & Me.cmbdbUserName & "'"), "HellNo") Then
        MsgBox "You do not have access to the DataBase, please contact the Admin.", vbCritical, "NO ACCESS !"
    Else
        MsgBox "Welcome " & Me.cmbdbUserName & " !", vbInformation
    End If
End Sub

 

[pr2-eugin]

You have End Sub instead of End If in the first two If blocks.

 

[solrac_otos]

Agh! Ok. Fixed that. I do not get an error any more but the code isnt working. Says I cannot leave the password empty when I purposely put in the correct password or left it empty to test the code. The code looks right but its not working how it should. Any ideas?

Private Sub cmdDBLogin_Click()
If Len(Me.cmbdbUserName & vbNullString) = 0 Then
        MsgBox "You cannot leave the UserName field empty, please try again.", vbCritical
        Exit Sub
    End If
    
    If Len(Me.txtPassword & vbNullString) Then
        MsgBox "You cannot leave the Password field empty, please try again.", vbCritical
        Exit Sub
    End If
    
    If Me.txtPassword <> Nz(DLookup("empPassword", "db-users", "empName = '" & Me.cmbdbUserName & "'"), "HellNo") Then
        MsgBox "You do not have access to the DataBase, please contact the Admin.", vbCritical, "NO ACCESS !"
    Else
        MsgBox "Welcome " & Me.cmbdbUserName & " !", vbInformation
    End If
End Sub

 

[pr2-eugin]

OOPS ! My fault ! Sorry. You need to use Len(...) = 0, I have edited my code in Post #2, try that.

 

[solrac_otos]

Gosh I cant believe I missed that too.

Okay. Fixed. Now ran the code entered a valid username and password and I get the "you do not have access to the database, please contact admin" msgbox.. The if thens on the empty fields is working properly. Ideas?

Private Sub cmdDBLogin_Click()
If Len(Me.cmbdbUserName & vbNullString) = 0 Then
        MsgBox "You cannot leave the UserName field empty, please try again.", vbCritical
        Exit Sub
    End If
    
    If Len(Me.txtPassword & vbNullString) = 0 Then
        MsgBox "You cannot leave the Password field empty, please try again.", vbCritical
        Exit Sub
    End If
    
    If Me.txtPassword <> Nz(DLookup("empPassword", "db-users", "empName = '" & Me.cmbdbUserName & "'"), "HellNo") Then
        MsgBox "You do not have access to the DataBase, please contact the Admin.", vbCritical, "NO ACCESS !"
    Else
        MsgBox "Welcome " & Me.cmbdbUserName & " !", vbInformation
    End If
End Sub

 

[pr2-eugin]

What is your cmbdbUserName's RowSource, ColumnCount, ColumnWidths and Bound column?

 

[solrac_otos]

The forms control source is a table: DB-USERS
cmbdbUserName is bound to empName

I only brought in empName column into that combo box.

Does this make sense?

 

[pr2-eugin]

Use some of the Debugging techniques : http://www.cpearson.com/excel/DebuggingVBA.aspx

You might be able to debug the error.