billcute
asked on
Invalid use of Null Error
In my cmdOk, when users type in their Username, they are expected to type in their password as well. Bu if the User enter's his name and failed to enter his password, he gets the error message below:
If IsNull(Me.txtUser) Or IsNull(Me.txtPassword) Then
MsgBox "You left the User Name and/or Password blank." & Chr(13) & "Please enter the correct User Name and Password or " & Chr(13) & "contact the Database Adminstrator for assistance.", vbOKOnly + vbCritical, "Logon Denied"
However, when the msgbox above appears, another error message occur on top of that:
"Invalid use of Null"
How do I eliminate this error: "Invalid use of Null" from showing ?.
If IsNull(Me.txtUser) Or IsNull(Me.txtPassword) Then
MsgBox "You left the User Name and/or Password blank." & Chr(13) & "Please enter the correct User Name and Password or " & Chr(13) & "contact the Database Adminstrator for assistance.", vbOKOnly + vbCritical, "Logon Denied"
However, when the msgbox above appears, another error message occur on top of that:
"Invalid use of Null"
How do I eliminate this error: "Invalid use of Null" from showing ?.
Try IsEmpty instead of IsNull - IsNull is typically only used with database fields.
Is it possible to force the user to first go to the ussername and then the password by using tab stops and setfocus as the form opens? Then you can trap them in each textbox until they enter something by testing in the before update. Something like this...
Private Sub txtUser_BeforeUpdate(Cance l As Integer)
If IsNull(Me.txtUser.Text) then
MsgBox "...."
Else
Cancel = True
EndIf
End Sub
Private Sub txtUser_BeforeUpdate(Cance
If IsNull(Me.txtUser.Text) then
MsgBox "...."
Else
Cancel = True
EndIf
End Sub
First of all, try the following:
If IsNull(me![txtUser]) Or IsNull(me![txtPassword) then
...
Second,
Make sure that you are either exiting the sub after the custom error message:
MsgBox "You left the User Name and/or Password blank." & Chr(13) & "Please enter the correct User Name and Password or " & Chr(13) & "contact the Database Adminstrator for assistance.", vbOKOnly + vbCritical, "Logon Denied"
exit sub ... or something like that
Third,
Make sure you are also probing for blank spaces " " or " "
Fefo
If IsNull(me![txtUser]) Or IsNull(me![txtPassword) then
...
Second,
Make sure that you are either exiting the sub after the custom error message:
MsgBox "You left the User Name and/or Password blank." & Chr(13) & "Please enter the correct User Name and Password or " & Chr(13) & "contact the Database Adminstrator for assistance.", vbOKOnly + vbCritical, "Logon Denied"
exit sub ... or something like that
Third,
Make sure you are also probing for blank spaces " " or " "
Fefo
Try this:
If IsNull(Me.txtUser) Or IsNull(Me.txtPassword) Then
MsgBox "You left the User Name and/or Password blank." & Chr(13) & "Please enter the correct User Name and Password or " & Chr(13) & "contact the Database Adminstrator for assistance.", vbOKOnly + vbCritical, "Logon Denied"
exit sub
end if
If IsNull(Me.txtUser) Or IsNull(Me.txtPassword) Then
MsgBox "You left the User Name and/or Password blank." & Chr(13) & "Please enter the correct User Name and Password or " & Chr(13) & "contact the Database Adminstrator for assistance.", vbOKOnly + vbCritical, "Logon Denied"
exit sub
end if
I would use:
If Len(NZ(Me.txtUser)) = 0 Or Len(NZ(Me.txtPassword)) = 0 Then
But also check your other code as it looks like this statement didn´t trigger the error...
Nic;o)
If Len(NZ(Me.txtUser)) = 0 Or Len(NZ(Me.txtPassword)) = 0 Then
But also check your other code as it looks like this statement didn´t trigger the error...
Nic;o)
The error doesn't apear to be in your code snippet as I can recreate it in access and don;t get the same problem.
I feel that the error is coming form somewhere else. Do you have any validation or events hanging off the text boxes or the form?
I feel that the error is coming form somewhere else. Do you have any validation or events hanging off the text boxes or the form?
Put a STOP command on the line following the MSGBOX, then step trace to find where the Null error is originating. I agree with others that it does not appear to come from the code that you are showing.
Try changing your login form Cycle property to 'Current Record' instead of 'All Records' that should stop the form from triggering update events.
Alan
Alan
ASKER
I tried all the suggestions solution here, the error persist after trying.
EXCEPTION:
When I tried Letcher's code, the error stopped except that the next code was not processed. I am now posting related codes here if this will help.
' *******
If IsNull(Me.txtUser) Or IsNull(Me.txtPassword) Then
MsgBox "You left the User Name and/or Password blank." & Chr(13) & "Please enter the correct User Name and Password or " & Chr(13) & "contact the Database Adminstrator for assistance.", vbOKOnly + vbCritical, "Logon Denied"
' Stop <<<<<----- Test point
ElseIf Me.txtUser = "Admin" And PerformEncryption(Me.txtPa ssword, True) = GetPassword("Admin") Then
User.UserID = Me.txtUser
DoCmd.OpenForm "frmMainMenu1"
Else
Set rst = currentdb.OpenRecordset("t blSecurity ", dbOpenDynaset)
rst.FindFirst "UserID = '" & Me.txtUser & "'"
If rst.NoMatch Then
GoTo Exit_cmdOk_Click
Else
'We're here - found the user record.
With User
.UserID = Me.txtUser
.AccessID = rst("AccessID")
End With
' .......................... .......... .......
' .......................... .......... ........
End Sub
EXCEPTION:
When I tried Letcher's code, the error stopped except that the next code was not processed. I am now posting related codes here if this will help.
' *******
If IsNull(Me.txtUser) Or IsNull(Me.txtPassword) Then
MsgBox "You left the User Name and/or Password blank." & Chr(13) & "Please enter the correct User Name and Password or " & Chr(13) & "contact the Database Adminstrator for assistance.", vbOKOnly + vbCritical, "Logon Denied"
' Stop <<<<<----- Test point
ElseIf Me.txtUser = "Admin" And PerformEncryption(Me.txtPa
User.UserID = Me.txtUser
DoCmd.OpenForm "frmMainMenu1"
Else
Set rst = currentdb.OpenRecordset("t
rst.FindFirst "UserID = '" & Me.txtUser & "'"
If rst.NoMatch Then
GoTo Exit_cmdOk_Click
Else
'We're here - found the user record.
With User
.UserID = Me.txtUser
.AccessID = rst("AccessID")
End With
' ..........................
' ..........................
End Sub
ASKER
I traced this error to another function that kicks into my cmdok code.
I simply rem out "On Error GoTo Err_cmdOk_Click" from my cmdOk and then run the code again, the time, I received:
Run-time error '94': Invalid use of Null
...and debug highlighted:
If CByte(Len(txtPassword)) >= 8 Then from the function below:
' ********
Public Function ValidatePassword(ByRef txtPassword As TextBox) As Boolean
Dim validate As Boolean
Dim CurPos As Byte
validate = False
If CByte(Len(txtPassword)) >= 8 Then ' <<<<------ Debug highlighted this line
For CurPos = 1 To CByte(Len(txtPassword))
Select Case Asc(Mid(txtPassword, CurPos, 1))
Case 33, 35, 36, 37, 38, 40, 41, 42, 64
validate = True
Exit For
End Select
Next CurPos
' .........................
' .......................... .
End Sub
I simply rem out "On Error GoTo Err_cmdOk_Click" from my cmdOk and then run the code again, the time, I received:
Run-time error '94': Invalid use of Null
...and debug highlighted:
If CByte(Len(txtPassword)) >= 8 Then from the function below:
' ********
Public Function ValidatePassword(ByRef txtPassword As TextBox) As Boolean
Dim validate As Boolean
Dim CurPos As Byte
validate = False
If CByte(Len(txtPassword)) >= 8 Then ' <<<<------ Debug highlighted this line
For CurPos = 1 To CByte(Len(txtPassword))
Select Case Asc(Mid(txtPassword, CurPos, 1))
Case 33, 35, 36, 37, 38, 40, 41, 42, 64
validate = True
Exit For
End Select
Next CurPos
' .........................
' ..........................
End Sub
try this:
If CByte(Len(txtPassword)) >= 8 Then from the function below:
' ********
Public Function ValidatePassword(ByRef txtPassword As TextBox) As Boolean
Dim validate As Boolean
Dim CurPos As Byte
validate = False
If CByte(Len(txtPassword & "")) >= 8 Then ' <<<---Append a "" (zero-length string)
For CurPos = 1 To CByte(Len(txtPassword))
Select Case Asc(Mid(txtPassword, CurPos, 1))
Case 33, 35, 36, 37, 38, 40, 41, 42, 64
validate = True
Exit For
End Select
Next CurPos
' .........................
' .......................... .
End Sub
Appeanding a "" onto a normal string will not have no effect on that string, but appeanding a "" onto a NULL value, will convert it into a "" (zero-length string), thus avoiding the error message - Though you need to figure out why you are getting a NULL string, when you expact to see a value Password string.
AW
If CByte(Len(txtPassword)) >= 8 Then from the function below:
' ********
Public Function ValidatePassword(ByRef txtPassword As TextBox) As Boolean
Dim validate As Boolean
Dim CurPos As Byte
validate = False
If CByte(Len(txtPassword & "")) >= 8 Then ' <<<---Append a "" (zero-length string)
For CurPos = 1 To CByte(Len(txtPassword))
Select Case Asc(Mid(txtPassword, CurPos, 1))
Case 33, 35, 36, 37, 38, 40, 41, 42, 64
validate = True
Exit For
End Select
Next CurPos
' .........................
' ..........................
End Sub
Appeanding a "" onto a normal string will not have no effect on that string, but appeanding a "" onto a NULL value, will convert it into a "" (zero-length string), thus avoiding the error message - Though you need to figure out why you are getting a NULL string, when you expact to see a value Password string.
AW
Hi, billcute !
Here is a suggesstion for you to eliminate this error and many more that may come in future:
>>> DONOT use txtPassword (Textbox) as a reference argument in public function ValidatePassword.
You must pass the text value of txtpassword to this function, that's a better way of programming.
e.g.: Public Function ValidatePassword(Optional varPassword As string = " ") As Boolean
I hope you get this...
Thanks,
Syed Nabeel.
Here is a suggesstion for you to eliminate this error and many more that may come in future:
>>> DONOT use txtPassword (Textbox) as a reference argument in public function ValidatePassword.
You must pass the text value of txtpassword to this function, that's a better way of programming.
e.g.: Public Function ValidatePassword(Optional varPassword As string = " ") As Boolean
I hope you get this...
Thanks,
Syed Nabeel.
The simplest way to fix this problem is to change the following line of code:
For CurPos = 1 To CByte(Len(txtPassword))
change to:
For CurPos = 1 To CByte(Len(NZ(txtPassword," ")))
For CurPos = 1 To CByte(Len(txtPassword))
change to:
For CurPos = 1 To CByte(Len(NZ(txtPassword,"
ASKER
Arthur Wood / SyedNabeel / DrLechter,
Thanks for your assistance. I tried each suggestion on it's own merit, instead of getting better, it worsens the situation.
This is how it worsens the situation:
(1). I replaced my code with the suggested code from each expert, then
type in a username that already exist in both in tblSecurity and tblUsers.
(2). User deliberately did not input any password in the password field
but clicks on the "cmdOk" button.
(3). User receives a msgbox to input 8 character,,,then jumps to the Update
password form.
Problem:
If user does not input any password or the default password "wizard" but clicks on the "cmdOk" - he is expected to ONLY get a msgbox "that the password field is left blank" - he is not expected to see "frmUpdatePassword" at all. This is the way the code works.
I noticed that correcting the error might be difficult without the sample of my Login Form and the associated modules in it.
To test the sample:
Type in "Developer" or "Bill" as Unser name ---password if desired (Abc123) <<--It's case sensitive.
Other users --- "Frank" / "Admin" etc...default password: Wizard <<- Not case sensitive.
To this end, I have now included a sample of my working Login Form (140k) at the link below to provide visualization of the problem first hand:
http://www.geocities.com/bombastikbill/InvalidUseOfNull.zip
Regards
Bill
Thanks for your assistance. I tried each suggestion on it's own merit, instead of getting better, it worsens the situation.
This is how it worsens the situation:
(1). I replaced my code with the suggested code from each expert, then
type in a username that already exist in both in tblSecurity and tblUsers.
(2). User deliberately did not input any password in the password field
but clicks on the "cmdOk" button.
(3). User receives a msgbox to input 8 character,,,then jumps to the Update
password form.
Problem:
If user does not input any password or the default password "wizard" but clicks on the "cmdOk" - he is expected to ONLY get a msgbox "that the password field is left blank" - he is not expected to see "frmUpdatePassword" at all. This is the way the code works.
I noticed that correcting the error might be difficult without the sample of my Login Form and the associated modules in it.
To test the sample:
Type in "Developer" or "Bill" as Unser name ---password if desired (Abc123) <<--It's case sensitive.
Other users --- "Frank" / "Admin" etc...default password: Wizard <<- Not case sensitive.
To this end, I have now included a sample of my working Login Form (140k) at the link below to provide visualization of the problem first hand:
http://www.geocities.com/bombastikbill/InvalidUseOfNull.zip
Regards
Bill
Removed your After_update on the password box and added this - no Null error and stays in box until the user supplies a name and then a password.
Private Sub txtPassword_Exit(Cancel As Integer)
If IsNull(Me.txtPassword) Then
MsgBox "No Password!'"
Cancel = True
End If
End Sub
Private Sub txtUser_Exit(Cancel As Integer)
If IsNull(Me.txtUser) Then
MsgBox "No username!"
Cancel = True
End If
End Sub
Private Sub txtPassword_Exit(Cancel As Integer)
If IsNull(Me.txtPassword) Then
MsgBox "No Password!'"
Cancel = True
End If
End Sub
Private Sub txtUser_Exit(Cancel As Integer)
If IsNull(Me.txtUser) Then
MsgBox "No username!"
Cancel = True
End If
End Sub
ASKER
p912s,
I made changes to the password box of frmUserLogon per your description above but I am still getting the "Invalid use of Null". Please try it again click on my username and send me your sample for review.
Regards
Bill
I made changes to the password box of frmUserLogon per your description above but I am still getting the "Invalid use of Null". Please try it again click on my username and send me your sample for review.
Regards
Bill
ASKER
p912s,'
On a closer look at your code the following are my observations.
(a). when frmuserLogon opens, it does not set focus on txtPassword
and I noted that if user clicks the cmdok without the focus being
set, the "Invalid us of Null" still display despit your added code..
(b). If user remember to tab or click in txtPassword and the box recieves
foucs, then your code makes it impossible to click or tab to any other
controls on the form.
This could spell disaster, since user may change his mind and wants to
EXIT db by clicking the Cancel button. However, with focus set on
txtPassword - user would be unable to cancel his login procedure,
It seems to me that resolving the problem from the root cause of the problem itself might be the best option. Thanks for trying.
Regards
Bill
On a closer look at your code the following are my observations.
(a). when frmuserLogon opens, it does not set focus on txtPassword
and I noted that if user clicks the cmdok without the focus being
set, the "Invalid us of Null" still display despit your added code..
(b). If user remember to tab or click in txtPassword and the box recieves
foucs, then your code makes it impossible to click or tab to any other
controls on the form.
This could spell disaster, since user may change his mind and wants to
EXIT db by clicking the Cancel button. However, with focus set on
txtPassword - user would be unable to cancel his login procedure,
It seems to me that resolving the problem from the root cause of the problem itself might be the best option. Thanks for trying.
Regards
Bill
I think you're working to hard at this....
My code wasn't the end solution but rather to show you that you can lock them into the boxes and not let them clcik on any buttons. You can set the focus wherever you want as the form opens. You can also disable the Ok button so it can't be clicked until the username and password have been filled in, so rather than create the opportuinty for the problem to happen you can avoid the possibility.
Just trying to get you to look at the problem from a different angle...
>>code makes it impossible to click or tab to any other
As I said in the email you need to provide the user an out so they can cancel....
My code wasn't the end solution but rather to show you that you can lock them into the boxes and not let them clcik on any buttons. You can set the focus wherever you want as the form opens. You can also disable the Ok button so it can't be clicked until the username and password have been filled in, so rather than create the opportuinty for the problem to happen you can avoid the possibility.
Just trying to get you to look at the problem from a different angle...
>>code makes it impossible to click or tab to any other
As I said in the email you need to provide the user an out so they can cancel....
ASKER
I am open to other suggestions / possibilities that will resolve the Run-time Error 94.
Regards
Bill
Regards
Bill
Bill:
By simply adding an Exit Sub command after your first msgbox as follows everything seems to get fixed:
If IsNull(Me.txtUser) Or IsNull(Me.txtPassword) Then
MsgBox "You left the User Name and/or Password blank." & Chr(13) & "Please enter the correct User Name and Password or " & Chr(13) & "contact the Database Adminstrator for assistance.", vbOKOnly + vbCritical, "Logon Denied"
exit sub
In the event that the user does not enter a password (or user name), the remaning code should NOT be processed. The remaining code only makes sense if a user name/password are entered. This includes the case of new users who have a default password of Wizard.
If this solution does not work for you, please provide a specifc reason why you think the remaning code needs to be executed.
By simply adding an Exit Sub command after your first msgbox as follows everything seems to get fixed:
If IsNull(Me.txtUser) Or IsNull(Me.txtPassword) Then
MsgBox "You left the User Name and/or Password blank." & Chr(13) & "Please enter the correct User Name and Password or " & Chr(13) & "contact the Database Adminstrator for assistance.", vbOKOnly + vbCritical, "Logon Denied"
exit sub
In the event that the user does not enter a password (or user name), the remaning code should NOT be processed. The remaining code only makes sense if a user name/password are entered. This includes the case of new users who have a default password of Wizard.
If this solution does not work for you, please provide a specifc reason why you think the remaning code needs to be executed.
Here's the line that causes the error if someone clicks OK without entering a pasword when the form opens. You test for the password to equal Wizard and if not you send the password field to be validated without checking to see if anything is there.
If ValidatePassword(Me.txtPas sword) Then
If ValidatePassword(Me.txtPas
ASKER
DrLechter ,
I tried the exit sub, it stops the error but it affected the Login restriction of two login attempts after which user is logged off from db.
The "Sub CheckLogAttempts1" is expected to kick in after user's two login attempts failed.
Regards
Bill
I tried the exit sub, it stops the error but it affected the Login restriction of two login attempts after which user is logged off from db.
The "Sub CheckLogAttempts1" is expected to kick in after user's two login attempts failed.
Regards
Bill
Bill:
Don't you think the login restriction should occur only when the user types some bad password - as though they were trying to illegally gain access to the system. If they enter no password at all, they have not really made an attempt to login yet? By the way, that is how this code seems to have been designed to operated.
JTC
Don't you think the login restriction should occur only when the user types some bad password - as though they were trying to illegally gain access to the system. If they enter no password at all, they have not really made an attempt to login yet? By the way, that is how this code seems to have been designed to operated.
JTC
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is ok except that the msgbox is flagging twice instead of once. I rem out the after update event code of the txtPassword field. It did not resolve the double flagging.
Regards
Bill
Regards
Bill
It's evident to me you are calling ValidatePassword() when txtPassword is NULL. Two days ago, Dr. Lechter advised you to add Exit Sub and you responded:
>When I tried Letcher's code, the error stopped except that the next code was not processed.
Isn't that what you want? To NOT execute the "the next code" which causes the error? Here is "the next code" that is produces the invalid use of Null error.
If Me.txtPassword = "Wizard" Then
Call UserEntryGate(txtPassword. Text)
Else
If ValidatePassword(Me.txtPas sword) Then <==you cannot do this if txtPassword is null
Call UserEntryGate(txtPassword. Text)
Else
DoCmd.OpenForm "frmUpdatePassword", , , "UserID = '" & Forms!frmUserLogon!txtUser & "'"
DoCmd.Close acForm, "frmUserLogon"
End If
End If
To fix your immediate problem, then add "exit sub" or, better yet, add "GoTo Exit_cmdOk_Click" following your msgbox. After that, the user is forced to enter a userid/password or cancel the application. What else are you expecting?
>When I tried Letcher's code, the error stopped except that the next code was not processed.
Isn't that what you want? To NOT execute the "the next code" which causes the error? Here is "the next code" that is produces the invalid use of Null error.
If Me.txtPassword = "Wizard" Then
Call UserEntryGate(txtPassword.
Else
If ValidatePassword(Me.txtPas
Call UserEntryGate(txtPassword.
Else
DoCmd.OpenForm "frmUpdatePassword", , , "UserID = '" & Forms!frmUserLogon!txtUser
DoCmd.Close acForm, "frmUserLogon"
End If
End If
To fix your immediate problem, then add "exit sub" or, better yet, add "GoTo Exit_cmdOk_Click" following your msgbox. After that, the user is forced to enter a userid/password or cancel the application. What else are you expecting?
ASKER
dqmq,
You got it all wrong. There was a follow up post to Letcher's EXIT SUB that you are missing and "Letcher" himself had responded again by suggesting that I place "Call CheckLogAttempts1" before the "Exit Sub" which was already done.
The Call for CheckLogAttempts1 simply limits users to three login attempts after which the user is logged off.
The issue at hand now is that msgbox now flag twice instead of once after the new code was added and as a matter of fact I have opened a new post to determine where the new error is coming from.
You raised another valid point here which has also received a new listing on EE.
If ValidatePassword(Me.txtPas sword) Then <==you cannot do this if txtPassword is null
I put up these posts realising that one post may not be enough for ever ending error messages. As soon as I am able to figure out the problem (s), I'll know what to do with each post listed.
Regards
bill
You got it all wrong. There was a follow up post to Letcher's EXIT SUB that you are missing and "Letcher" himself had responded again by suggesting that I place "Call CheckLogAttempts1" before the "Exit Sub" which was already done.
The Call for CheckLogAttempts1 simply limits users to three login attempts after which the user is logged off.
The issue at hand now is that msgbox now flag twice instead of once after the new code was added and as a matter of fact I have opened a new post to determine where the new error is coming from.
You raised another valid point here which has also received a new listing on EE.
If ValidatePassword(Me.txtPas
I put up these posts realising that one post may not be enough for ever ending error messages. As soon as I am able to figure out the problem (s), I'll know what to do with each post listed.
Regards
bill
I did miss that followup, which explains what you are expecting. Will check new posts.
Bill:
Using the DB you posted in the hyperlink, and adding the two lines of code I suggested (Call CheckLogAttempts1 and Exit sub), the system seems to work perfectly.
I do not understand what you mean by "The issue at hand now is that msgbox now flag twice instead of once"? Perhaps you mean you are getting one message box saying the user name and/or password are blank, and another saying the database will be shutdown because the login rules have been violated?
If that is the case, it seems to be perfectly reasonable behavior to me. What were you expecting?
Using the DB you posted in the hyperlink, and adding the two lines of code I suggested (Call CheckLogAttempts1 and Exit sub), the system seems to work perfectly.
I do not understand what you mean by "The issue at hand now is that msgbox now flag twice instead of once"? Perhaps you mean you are getting one message box saying the user name and/or password are blank, and another saying the database will be shutdown because the login rules have been violated?
If that is the case, it seems to be perfectly reasonable behavior to me. What were you expecting?
ASKER
DrLechter ,
Your summation is right I guess. I have re-opened a new post to deal with the msgbox problem.
I have considered all the contributions here and found that your suggestion works better, I will have to award you the full points here for the extra mile.
Regards
Bill
Your summation is right I guess. I have re-opened a new post to deal with the msgbox problem.
I have considered all the contributions here and found that your suggestion works better, I will have to award you the full points here for the extra mile.
Regards
Bill
If IsNull(Me.txtUser) Or IsNull(Me.txtPassword) Then
To this
If IsNull(Me.txtUser.text) Or IsNull(Me.txtPassword.text