Reply to thread

Message: [QUOTE="Eka1618, post: 12515095"] 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! [/QUOTE]

Verification