CODEWITHSUNDEEP
vbams-access-2013
MK08
MK08

Reputation: 11

Microsoft Access; error 94: Invalid use of Null

I am creating a log in for on Access using VBA.

Everything works until the username entered (on the login form) is misspelled. When misspelled I get "Error 94; Invalid use of Null". The following is my code (up to where the bug is)

Private Sub Command1_Click()

Dim user As String
Dim userlevel As Integer
Dim TempPass As String
Dim ID As Integer
Dim UserName As String
Dim TempLoginID As String
Dim password As String
Dim testcomp As Integer

If IsNull(Me.txtUserName) Then
    MsgBox "Please enter LoginID", vbInformation, "LoginID Required"
    Me.txtUserName.SetFocus
ElseIf IsNull(Me.txtPassword) Then
    MsgBox "Please enter Password", vbInformation, "Password Required"
    Me.txtPassword.SetFocus
Else
    If testcomp = StrComp(Me.txtPassword.Value, (DLookup("[Password]",  "tblUser", "[Userlogin]= '" & Me.txtUserName.Value & "'")), vbBinaryCompare) = 0 Then
    MsgBox "Incorrect LoginID or Password" & vbNewLine & "Please contact an administrator for login information", vbInformation, "Password Required"
    Else
        TempLoginID = Me.txtUserName.Value
    UserName = Nz(DLookup("[UserLogin]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'"), "")
    userlevel = Nz(DLookup("UserSecurity", "tblUser", "UserLogin = '" & Me.txtUserName.Value & "'"), "")
    TempPass = Nz(DLookup("[Password]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'"), "")
    ID = Nz(DLookup("[UserID]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'"), "")
    DoCmd.Close
    If (TempPass = "Password") Then
        MsgBox "Please change password", vbInformation, "New password required"
        DoCmd.OpenForm "pwdupdate_frm", , , "[UserID] = " & ID

        Else

The debug takes me to the line:

 UserName = Nz(DLookup("[UserLogin]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'"),"")

What am I missing?

Thank you for any help!

Upvotes: 1

Views: 1321

Answers (2)

MK08
MK08

Reputation: 11

I believe I figured it out. Posting in case someone in the future has similar issue. I figured out the Nz() function (if in an integer variable) has to be set to the value its looking up.

For instance I had 

userlevel = Nz(DLookup("UserSecurity", "tblUser", "UserLogin = '" & Me.txtUserName.Value & "'"), "")

When in actuality it should:

userlevel = Nz(DLookup("UserSecurity", "tblUser", "UserLogin = '" & Me.txtUserName.Value & "'"), "1, 2, 3")

Because I have 3 levels of access (1;Admin 2;User 3;Guest)

The same is for the line 

ID = Nz(DLookup("[UserID]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'"), "")

needs to have 

), 0) instead of ), "")

because it is an integer and not a string.

Thank you very much for the help and if anyone cares to elaborate more on this or correct me where I am wrong, please feel free to!

Upvotes: 0

marlan
marlan

Reputation: 1485

Do to bad criteria, Dlookup returns a Null value, witch you sign to UserName. Try putting the function in Nz() function:

UserName = Nz(DLookup("[UserLogin]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'"),"")

This way UserName receives a null string, not a null value. Actually, it is recommended for all DLookups, and Form controls.

Upvotes: 1

Related Questions