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 ?.
LVL 4
billcuteAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

p912sCommented:
Any effect if you change this

  If IsNull(Me.txtUser) Or IsNull(Me.txtPassword) Then

To this

  If IsNull(Me.txtUser.text) Or IsNull(Me.txtPassword.text) Then

0
Lee W, MVPTechnology and Business Process AdvisorCommented:
Try IsEmpty instead of IsNull - IsNull is typically only used with database fields.
0
p912sCommented:
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(Cancel As Integer)
    If IsNull(Me.txtUser.Text) then
        MsgBox "...."
    Else
       Cancel = True
    EndIf
End Sub


0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

fefo_33065Commented:
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
0
DrLechterCommented:
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

0
nico5038Commented:
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)

0
sbarrow1Commented:
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?
0
dqmqCommented:
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.
0
Alan WarrenApplications DeveloperCommented:
Try changing your login form Cycle property to 'Current Record' instead of 'All Records' that should stop the form from triggering update events.

Alan
0
billcuteAuthor Commented:
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.txtPassword, True) = GetPassword("Admin") Then
    User.UserID = Me.txtUser
    DoCmd.OpenForm "frmMainMenu1"
  Else
    Set rst = currentdb.OpenRecordset("tblSecurity", 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
0
billcuteAuthor Commented:
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
0
Arthur_WoodCommented:
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
0
SYED NABEEL SHAHIDCommented:
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.
 
0
DrLechterCommented:
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,"")))
0
billcuteAuthor Commented:
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
0
p912sCommented:
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

0
billcuteAuthor Commented:
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
0
billcuteAuthor Commented:
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
0
p912sCommented:
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....

0
billcuteAuthor Commented:
I am open to other suggestions / possibilities that will resolve the Run-time Error 94.

Regards
Bill
0
DrLechterCommented:
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.
0
p912sCommented:
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.txtPassword) Then
0
billcuteAuthor Commented:
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
0
DrLechterCommented:
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
0
DrLechterCommented:
If you would like it to work that way anyway, change as follows:

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"
    Call CheckLogAttempts1
    exit sub


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
billcuteAuthor Commented:
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
0
dqmqCommented:
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.txtPassword) 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?  


0
billcuteAuthor Commented:
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.txtPassword) 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
0
dqmqCommented:
I did miss that followup, which explains what you are expecting.   Will check new posts.
0
DrLechterCommented:
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?


0
billcuteAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.