Invalid Use Of Null caused by leaving textbox blank

[chrisjames25]

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:

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]

Add an Else statement after
"response = MsgBox(msg, style, title)"

 

[strive4peace]

hi @chrisjames25,

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

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.

 

[arnelgp]

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]

You are also not Dimming your variables correctly?
Plus you do not appear to have Option Explicit at the top of your module?

 

[strive4peace]

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

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]

hi @chrisjames25,

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

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]

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]

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 ~

If IsNull(Me.Txt_NewTier1) then
  Cancel = True
  Exit Sub
end if