Invalid Use Of Null caused by leaving textbox blank (1 Viewer)

chrisjames25

Registered User.
Local time
Today, 12:18
Joined
Dec 1, 2014
Messages
401
Hi all. I have a form with 2 textboxes. The first textbox is txt_newtier1 and second is txt_confirmtier1. Basically you enter a new name in first text box and then reenter it in second textbox to ensure inputter has entered it correctly.

After inputting the data in the first textbox I run a dcount to ensure this isn't duplicating a name already in existence.

My code is as follows:

Code:
Private Sub Txt_NewTier1_AfterUpdate()

Dim msg, style, title, response, MyString, field, Source, criteria

field = StrTable
Source = "Tbl_" & field
criteria = field & "= '" & Replace(Me.Txt_NewTier1, "'", "''") & "'"

If DCount(field, Source, criteria) > 0 Then
        msg = "This " & StrTier11 & " already exsits!!!"
        style = vbOKOnly + vbExclamation
        title = StrTier1 & " Duplicate Error"

        response = MsgBox(msg, style, title)
    
    Me.Txt_NewTier1.Value = ""
    'Me.Cmd_Add.SetFocus
    Me.Cmd_ChangeTier1.SetFocus
    
    Me.Txt_NewTier1.SetFocus
       Dim vSearchString As String

Exit Sub
End If

Work fine unless i have entered some info in txt_newtier1 and then delete it and tab out of textbox. I get an invalid use of null error message.

Can anyone see why from the code? Any help as ever much appreciated.
 

oleronesoftwares

Passionate Learner
Local time
Today, 05:18
Joined
Sep 22, 2014
Messages
1,159
Add an Else statement after
"response = MsgBox(msg, style, title)"
 

strive4peace

AWF VIP
Local time
Today, 07:18
Joined
Apr 3, 2020
Messages
1,003
hi @chrisjames25,

in the beginning test Me.Txt_NewTier1 to see if it has a value

Rich (BB code):
If IsNull(Me.Txt_NewTier1) then
   'what do you want to do if its empty? Exit?
else
   'do your stuff
end if

to answer your question, when Me.Txt_NewTier1 doesn't have a value, the criteria = assignment will get an error because Replace is a STRING function and doesn't work with Null

if this control must be filled, you can might want to use the form BeforeUpdate event to validate it.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:18
Joined
May 7, 2009
Messages
19,169
you also need to Move your code in the Control's BeforeUpdate event, so you can Cancel
updating the control when it does not pass the validation.
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:18
Joined
Sep 21, 2011
Messages
14,038
You are also not Dimming your variables correctly?
Plus you do not appear to have Option Explicit at the top of your module?
 

strive4peace

AWF VIP
Local time
Today, 07:18
Joined
Apr 3, 2020
Messages
1,003
hi @chrisjames25 ,

instead of a control BeforeUpdate, I prefer to use form BeforeUpdate so controls can be filled in whatever order.

to validate a record and prevent it from being saved, put code in the form BeforeUpdate event. Here is a generic example you can modify

Rich (BB code):
Private Sub Form_BeforeUpdate(Cancel As Integer) 
'170820 strive4peace 
   
   '-------------- make sure required data is filled out
   'make sure required data is filled out
   With Me.ControlName  '--- customize ControlName
      If IsNull(.Value) Then 
        'if it is not filled out, then move the focus to that control
        .SetFocus 
        
        'give the user a message
        MsgBox  "You must enter WHATEVER",, "Missing Data" '--- customize Whatever and other text
        
        'IF this is a combobox, drop the list for them
        '.Dropdown    'UNCOMMENT if combo
        
        'don't save the record yet
        Cancel = True 
        
        'quit checking and give them a chance to fill it out
        Exit Sub 
      End If 
   End With 
   
   'if you have a field in the table to keep track of when a record was modified
   'Me.dtmEdit = Now()

   'Call FormBeforeUpdate(Me) 'uncomment if you have something generic to run
   
End Sub

I call the field to keep track of when a record was added dtmAdd and the field to keep track of when it was edited dtmEdt. Both have a DefaultValue =Now()

I create fields to keep track of when a record was added (dtmAdd) and when it was edited (dtmEdt). Both have a DefaultValue =Now()

If you have multiple controls to check, repeat and customize what is between 'With Me.ControlName' and 'End With'
 

chrisjames25

Registered User.
Local time
Today, 12:18
Joined
Dec 1, 2014
Messages
401
hi @chrisjames25,

in the beginning test Me.Txt_NewTier1 to see if it has a value

Rich (BB code):
If IsNull(Me.Txt_NewTier1) then
   'what do you want to do if its empty? Exit?
else
   'do your stuff
end if

to answer your question, when Me.Txt_NewTier1 doesn't have a value, the criteria = assignment will get an error because Replace is a STRING function and doesn't work with Null

if this control must be filled, you can might want to use the form BeforeUpdate event to validate it.
Thanks for response. So I only get this error when i enter data in textbox and then delete it and tab out. If i just enter textbox and instantly tab out it doesnt cause the null error. Why is that? Surely the textbox is still a null?
 

chrisjames25

Registered User.
Local time
Today, 12:18
Joined
Dec 1, 2014
Messages
401
You are also not Dimming your variables correctly?
Plus you do not appear to have Option Explicit at the top of your module?
I will look up the correct way of dimming variables. Thanks for flagging it.
 

strive4peace

AWF VIP
Local time
Today, 07:18
Joined
Apr 3, 2020
Messages
1,003
hi @chrisjames25

actually, that code to test IsNull should be in the control BEFORE UPDATE so you can Cancel. Then AfterUpdate won't run if it's Null and therefore you can leave it where it is -- just as @arnelgp suggested. Also Dim variable as per @Gasman's suggestion ~

Rich (BB code):
If IsNull(Me.Txt_NewTier1) then
  Cancel = True
  Exit Sub
end if
 

Users who are viewing this thread

Top Bottom