Copying data to a new record - skip null records

[helent24]

I've created some code that enables a user to copy an existing record to a new record, which looks like this:

Private Sub btnCopytoNewRecord_Click()

Dim Salutation As String
Dim First_Name As String
Dim Surname As String

'Copy fields to variables
Salutation = Me.Salutation
First_Name = Me.First_Name
Surname = Me.Surname

'Go to a new record
DoCmd.GoToRecord , , acNewRec

'Copy old values into new record
Me.Salutation = Salutation
Me.First_Name = First_Name
Me.Surname = Surname
End Sub​

This code is working fine, until it hits an empty field, and then I get an error message:

Run time error '94':
Invalid use of Null​

Is there some code I can use to tell it to skip any null fields?

 

[madefemere]

Hello,
You can test the values of your controls with IF before to copy as :

Private Sub btnCopytoNewRecord_Click()

Dim Salutation As String
Dim First_Name As String
Dim Surname As String

 'Copy fields to variables
  Salutation = Me.Salutation
  First_Name = Me.First_Name
  Surname = Me.Surname

IF Not isnull(Salutation) AND Not isnull(First_Name) AND Not isnull(Surname) then
'Here, the 3 textboxes must have values. You can change.
        'Go to a new record
     DoCmd.GoToRecord , , acNewRec
  
     'Copy old values into new record
     Me.Salutation = Salutation
     Me.First_Name = First_Name
     Me.Surname = Surname
END IF
End Sub

Good continuation

 

[Mihail]

Private Sub btnCopytoNewRecord_Click()
  If IsNull(Me.Salutation) Or IsNull(Me.First_Name) Or IsNull(Me.Surname) Then
    MsgBox("Fill all fields)
Exit Sub
  End If

........

End Sub

Note please that such a request (to copy information between records) usually denote a poorly design of a database.