Invalid Use of Null

[Zanstemic]

The following is to duplicate record which works well. Except for if a field
is blank it gives an Invalid Use of Null error. When I originally used this
code, I required all fields to be entered though this time, it should just
leave the field blank.

The reason for not just duplicating the record is one field has to be auto
generated and cannot be an auto field.

Any suggestions on how to allow a null to pass through would be extremely
helpful.

Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim ctrl1 As String 'string example
Dim ctrl2 As String 'string example


'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Please Enter Information to Duplicate."
Else
'Duplicate the main record: add to new form.

ctrl1 = cboClaimNumber
ctrl2 = cboSpecialty

DoCmd.GoToRecord , , acNewRec
[Claim Number] = ctrl1
SpecialtyID = ctrl2

End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler
End Sub

 

[Dirk Goldgar]

The following is to duplicate record which works well. Except for if a
field
is blank it gives an Invalid Use of Null error. When I originally used
this
code, I required all fields to be entered though this time, it should just
leave the field blank.

The reason for not just duplicating the record is one field has to be auto
generated and cannot be an auto field.

Any suggestions on how to allow a null to pass through would be extremely
helpful.

Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim ctrl1 As String 'string example
Dim ctrl2 As String 'string example


'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Please Enter Information to Duplicate."
Else
'Duplicate the main record: add to new form.

ctrl1 = cboClaimNumber
ctrl2 = cboSpecialty

DoCmd.GoToRecord , , acNewRec
[Claim Number] = ctrl1
SpecialtyID = ctrl2

End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler
End Sub

Instead of

Dim ctrl1 As String 'string example
Dim ctrl2 As String 'string example

.... declare the holding variables as Variants:

Dim ctrl1 As Variant
Dim ctrl2 As Variant

Only the Variant data type can hold a Null.