Can't seem to add null field using .addnew

[shahulf]

I used Allen Browne example for 'Duplicate the record in form and subform'
from his website. It works fine as long as there is data in the field I am
copying. If the field is null, it give me error saying 3167 - record is
deleted. As soon as I enter data into the field that I am copying, then the
command button works fine. All the column in the code except the Agreement
field is required. So they all have data, Agreement field is some time null.
If I comment out the agreement line it works fine. Any ideas on how to fix
this.

Here's my code;
Private Sub cmdDupe_Click()
On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the subform.
Dim strSql As String 'SQL statement.
Dim SessionID As Long 'Primary key value of the new record.

'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 "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
..AddNew
!STATUS_FK = Me.STATUS_FK
!STATUS_DATE = Date
!PROGRAM_FK = Me.PROGRAM_FK
!BEGIN_DATE = Me.BEGIN_DATE
!ACTIVITY_DATE = Date
!SEND_TO_BANNER_IND = "N"
!CURRENT_IND = "Y"
!CM_ID_FK = Me.CM_ID_FK
!SESSION_TITLE = Me.SESSION_TITLE
!AGREEMENT = Me.AGREEMENT

'etc for other fields.
..Update

'Save the primary key value, to use as the foreign key for the related
records.
..Bookmark = .LastModified
SessionID = !SESSION_PK
'lngID = !PlanID

'Duplicate the related records: append query.
If Me.sfrmLogisticsTesting.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [SESSIONS_LT] ( Session_pk_fk, Checklist_ind,
BU_LC_ID_FK, TC_ID_FK, LC_ID_FK, Notes) " & _
"SELECT " & SessionID & " As NewID, Checklist_ind, BU_LC_ID_FK, TC_ID_FK,
LC_ID_FK, Notes " & _
"FROM [SESSIONS_LT] WHERE Session_pk_fk = " & Me.SESSION_PK & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
strSql = "INSERT INTO [SESSIONS_INSTRUMENT] (Session_pk_fk,
Instrument_pk_fk, Notes, Units, Unit_cost) " & _
"SELECT " & SessionID & " As NewID, Instrument_pk_fk, Notes, Units,
Unit_cost from [SESSIONS_INSTRUMENT] " & _
"WHERE Session_pk_fk = " & Me.SESSION_PK & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

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

 

[Rui]

Hi,

just amend the line !AGREEMENT = Me.AGREEMENT to read:

If Not IsNull(Me.AGREEMENT) Then !AGREEMENT = Me.AGREEMENT


Cheers
Rui

 

[shahulf]

Hi Rui,

Thank you sooo much. I added your code and it work fine now. Looks like I
have to add your code to check for null for all the field in the table that
could be null before I do insert. However, I still don't understand why I
have to check for null, is is vba code or access that is not recognizing null
as null. Thanks again.

Best regards,
shahulf

Hi,

just amend the line !AGREEMENT = Me.AGREEMENT to read:

If Not IsNull(Me.AGREEMENT) Then !AGREEMENT = Me.AGREEMENT


Cheers
Rui

I used Allen Browne example for 'Duplicate the record in form and subform'
from his website. It works fine as long as there is data in the field I am
copying. If the field is null, it give me error saying 3167 - record is
deleted. As soon as I enter data into the field that I am copying, then the
command button works fine. All the column in the code except the Agreement
field is required. So they all have data, Agreement field is some time null.
If I comment out the agreement line it works fine. Any ideas on how to fix
this.

Here's my code;
Private Sub cmdDupe_Click()
On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the subform.
Dim strSql As String 'SQL statement.
Dim SessionID As Long 'Primary key value of the new record.

'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 "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!STATUS_FK = Me.STATUS_FK
!STATUS_DATE = Date
!PROGRAM_FK = Me.PROGRAM_FK
!BEGIN_DATE = Me.BEGIN_DATE
!ACTIVITY_DATE = Date
!SEND_TO_BANNER_IND = "N"
!CURRENT_IND = "Y"
!CM_ID_FK = Me.CM_ID_FK
!SESSION_TITLE = Me.SESSION_TITLE
!AGREEMENT = Me.AGREEMENT

'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the related
records.
.Bookmark = .LastModified
SessionID = !SESSION_PK
'lngID = !PlanID

'Duplicate the related records: append query.
If Me.sfrmLogisticsTesting.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [SESSIONS_LT] ( Session_pk_fk, Checklist_ind,
BU_LC_ID_FK, TC_ID_FK, LC_ID_FK, Notes) " & _
"SELECT " & SessionID & " As NewID, Checklist_ind, BU_LC_ID_FK, TC_ID_FK,
LC_ID_FK, Notes " & _
"FROM [SESSIONS_LT] WHERE Session_pk_fk = " & Me.SESSION_PK & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
strSql = "INSERT INTO [SESSIONS_INSTRUMENT] (Session_pk_fk,
Instrument_pk_fk, Notes, Units, Unit_cost) " & _
"SELECT " & SessionID & " As NewID, Instrument_pk_fk, Notes, Units,
Unit_cost from [SESSIONS_INSTRUMENT] " & _
"WHERE Session_pk_fk = " & Me.SESSION_PK & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

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