Invalid Use of Null

[vb9063]

HI Guys,
I have a problem that occurs when you create a new record and start typing in the name field. If you change your mind and delete what you have written so that there is no longer any text in the box if you try and do anything else the Invalid use of Null error message comes up.
How can i fix this so that it stops doing this? I have the following code in the beforeupdate event of the name field...

Private Sub Sup_Name_BeforeUpdate(Cancel As Integer)

    Dim SNM As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset
    Set rsc = Me.RecordsetClone
    
   ' If Not IsNull(Me.Sup_Name.Value) Then
            SNM = Me.Sup_Name.Value
   ' End If
    
    If Me.NewRecord Then
    stLinkCriteria = "[Sup_Name]=" & "'" & SNM & "'"
    'Check Suppliers table for duplicate Supplier
     If DCount("Sup_Name", "Suppliers", _
              stLinkCriteria) > 0 Then
       ' Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning the Supplier: " _
             & SNM & " has already been entered." _
             & vbCr & vbCr & "You will now been taken to the record.", _
               vbInformation, "Duplicate Information"
        'Go to record of original supplier
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If
End If
    Set rsc = Nothing
End Sub

Thanks very much! VB

 

[namliam]

change: SNM = Me.Sup_Name.Value
to: SNM = NZ(Me.Sup_Name.Value, "")

Or... might be better to...
change: ' If Not IsNull(Me.Sup_Name.Value) Then
to: If Not IsNull(Me.Sup_Name.Value) Then

Then add a "End if" at the bottom
End If
End if
Set rsc = Nothing

I wont preach to you about "indenting code for redability's sake..."

 

[vb9063]

change: SNM = Me.Sup_Name.Value
to: SNM = NZ(Me.Sup_Name.Value, "")

Or... might be better to...
change: ' If Not IsNull(Me.Sup_Name.Value) Then
to: If Not IsNull(Me.Sup_Name.Value) Then

Then add a "End if" at the bottom
End If
End if
Set rsc = Nothing

I wont preach to you about "indenting code for redability's sake..."

sorry Mailman, my indenting is a bit rubbish!
If i do the ( if Not isnull ) I get a message saying that I must enter a valid entry for Sup_name because before I had only SNM=Me.Sup_Name.Value and changed it to include the isnull expression.
It removes the runtime error but then it is expecting an entry....so I guess how do i get round it if the user just makes a mistake and wants to go back as opposed to entering all the other field information and not entering a supplier name...???Thanks again VB

 

[namliam]

Private Sub Sup_Name_BeforeUpdate(Cancel As Integer)

    Dim SNM As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset
    Set rsc = Me.RecordsetClone
    
   If Not IsNull(Me.Sup_Name.Value) Then
            SNM = Me.Sup_Name.Value
   ' End If
    
    If Me.NewRecord Then
    stLinkCriteria = "[Sup_Name]=" & "'" & SNM & "'"
    'Check Suppliers table for duplicate Supplier
     If DCount("Sup_Name", "Suppliers", _
              stLinkCriteria) > 0 Then
       ' Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning the Supplier: " _
             & SNM & " has already been entered." _
             & vbCr & vbCr & "You will now been taken to the record.", _
               vbInformation, "Duplicate Information"
        'Go to record of original supplier
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If
End If
end if
    Set rsc = Nothing
End Sub

It hink that is what I was suggesting and I think that is what should work.

 

[vb9063]

Hi Mailman,
Thanks very much, I have copied that code in and it works but how do I distinguish between a new record that the user will enter other details in for the supplier and one that the user erases because they change their mind about putting the supplier in? I still get the sup_name required box come up because it is a required field. Is the only way to get round this to make it a non-required field??
Thanks again,
VB

 

[namliam]

If supplier is a required field, it is a required field, if the user change their mind or not dont matter...

 

[The_Doc_Man]

An .UNDO should get rid of the requirement of that required field. When you get a box that says something is required and you thought you were getting rid of it, you didn't get rid of it in the right way. The message would be triggered by the presence of a required field that is bound to the (empty) control when you are about to STORE it, i.e. some form of SAVE operation, because that is the only time a required field is truly required.

 

[namliam]

UNDO though clear all changes on the form right? So any other changes would be lost too?