Invalid Use of Null

  • Thread starter Thread starter Zanstemic
  • Start date Start date
Z

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
 
Zanstemic said:
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
Click to expand...


Instead of
Dim ctrl1 As String 'string example
Dim ctrl2 As String 'string example
Click to expand...

.... declare the holding variables as Variants:

Dim ctrl1 As Variant
Dim ctrl2 As Variant

Only the Variant data type can hold a Null.
 
Back
Top