E
Eka1618
Hello,
I used Allen Browne's script for duplicating records from a form and it's
subforms.
The one subform I use has a composite PK. I am having dificulty writing the
SQL statement in my code. The following script does not seem to grab the
value for PAT_NO.
Here is what I have so far:
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 lngID 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
!TITLE = Me.TITLE
!EMP_ID = Me.lboRequestor.Value
!REQUESTEE = Me.lboRequestee.Value
!DUE_DATE = Me.DUE_DATE
!CUSTOMER = Me.CUSTOMER
!NOTES = Me.NOTES
!R_TYPE = Me.R_TYPE
.Update
'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !REQUEST_NO
'Duplicate the related records: append query.
If Me.frmPattern.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO tblPattern( QUANTITY ) " & _
"SELECT QUANTITY FROM tblPattern WHERE REQUEST_NO = " &
Me.REQUEST_NO & _
" AND PAT_NO = " & Me.frmPattern.Form.PAT_NO & " AND
PAT_SIZE = '" & Me.frmPattern.Form.PAT_SIZE & "';"
DBEngine(0)(0).Execute strSql, dbFailOnError
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
The message I get is that The value for PAT_NO cannot be NULL. There is a
value in all of the fields I am trying to copy at the time I click this
button.
Here are the specs for tblPattern:
PAT_NO (PK)
PAT_SIZE (PK)
REQUEST_NO (PK/FK)
QUANTITY
If anyone knows how I can modify this SQL statement, Please let me know,
Thank You!
I used Allen Browne's script for duplicating records from a form and it's
subforms.
The one subform I use has a composite PK. I am having dificulty writing the
SQL statement in my code. The following script does not seem to grab the
value for PAT_NO.
Here is what I have so far:
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 lngID 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
!TITLE = Me.TITLE
!EMP_ID = Me.lboRequestor.Value
!REQUESTEE = Me.lboRequestee.Value
!DUE_DATE = Me.DUE_DATE
!CUSTOMER = Me.CUSTOMER
!NOTES = Me.NOTES
!R_TYPE = Me.R_TYPE
.Update
'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !REQUEST_NO
'Duplicate the related records: append query.
If Me.frmPattern.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO tblPattern( QUANTITY ) " & _
"SELECT QUANTITY FROM tblPattern WHERE REQUEST_NO = " &
Me.REQUEST_NO & _
" AND PAT_NO = " & Me.frmPattern.Form.PAT_NO & " AND
PAT_SIZE = '" & Me.frmPattern.Form.PAT_SIZE & "';"
DBEngine(0)(0).Execute strSql, dbFailOnError
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
The message I get is that The value for PAT_NO cannot be NULL. There is a
value in all of the fields I am trying to copy at the time I click this
button.
Here are the specs for tblPattern:
PAT_NO (PK)
PAT_SIZE (PK)
REQUEST_NO (PK/FK)
QUANTITY
If anyone knows how I can modify this SQL statement, Please let me know,
Thank You!