?
Solved

Invalid use of Null Error

Posted on 2006-03-27
31
Medium Priority
?
5,523 Views
Last Modified: 2008-01-09
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 ?.
0
Comment
Question by:billcute
  • 10
  • 6
  • 5
  • +8
31 Comments
 
LVL 18

Expert Comment

by:p912s
ID: 16307583
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
 
LVL 97

Expert Comment

by:Lee W, MVP
ID: 16307585
Try IsEmpty instead of IsNull - IsNull is typically only used with database fields.
0
 
LVL 18

Expert Comment

by:p912s
ID: 16307602
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 7

Expert Comment

by:fefo_33065
ID: 16307606
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
 
LVL 4

Expert Comment

by:DrLechter
ID: 16307691
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
 
LVL 54

Expert Comment

by:nico5038
ID: 16307857
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
 
LVL 1

Expert Comment

by:sbarrow1
ID: 16308104
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
 
LVL 42

Expert Comment

by:dqmq
ID: 16308299
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 16308662
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
 
LVL 4

Author Comment

by:billcute
ID: 16309428
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
 
LVL 4

Author Comment

by:billcute
ID: 16309587
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 16309764
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
 
LVL 4

Expert Comment

by:SYED NABEEL SHAHID
ID: 16314749
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
 
LVL 4

Expert Comment

by:DrLechter
ID: 16314806
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
 
LVL 4

Author Comment

by:billcute
ID: 16317050
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
 
LVL 18

Expert Comment

by:p912s
ID: 16317297
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
 
LVL 4

Author Comment

by:billcute
ID: 16317597
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
 
LVL 4

Author Comment

by:billcute
ID: 16318162
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
 
LVL 18

Expert Comment

by:p912s
ID: 16318305
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
 
LVL 4

Author Comment

by:billcute
ID: 16320113
I am open to other suggestions / possibilities that will resolve the Run-time Error 94.

Regards
Bill
0
 
LVL 4

Expert Comment

by:DrLechter
ID: 16320188
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
 
LVL 18

Expert Comment

by:p912s
ID: 16320238
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
 
LVL 4

Author Comment

by:billcute
ID: 16320810
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
 
LVL 4

Expert Comment

by:DrLechter
ID: 16320893
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
 
LVL 4

Accepted Solution

by:
DrLechter earned 2000 total points
ID: 16320961
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
 
LVL 4

Author Comment

by:billcute
ID: 16321475
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
 
LVL 42

Expert Comment

by:dqmq
ID: 16323533
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
 
LVL 4

Author Comment

by:billcute
ID: 16324206
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
 
LVL 42

Expert Comment

by:dqmq
ID: 16325262
I did miss that followup, which explains what you are expecting.   Will check new posts.
0
 
LVL 4

Expert Comment

by:DrLechter
ID: 16327356
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
 
LVL 4

Author Comment

by:billcute
ID: 16327511
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question