Cloning Record in Form/subform

[isoman53]

I am trying to set up "Cloning" on a form so that we don't have to enter all info every time when most of it is the same.
I have the main form [frmRFQInput] linked to [tblRFQ]:
[RFQNumber]
[RFQDate]
[RFQDueDate]
[Supplier]
[BuyerName]
[Notes]

and a subform [frmRFQInputsubform]linked to [tblRFQItems]:
[ID]......(linked to RFQNumber)
[PartNumber]
[Revision]
[Material]
[ProcessDescription]
[Qty1]
[Qty2]
[Qty3]
[Notes]


I am still getting errors in the code and I am not sure where I have gone wrong.

Private Sub Command35_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 ldslD As Long    'Primary key value of the new record.

'Save any edits first
If Me.Dirty Then
   Me.Dirry=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
     RFQDate=Date
     RFQDueDate="Enter Due Date"
     Supplier="Enter Supplier"
     BuyerName=BuyerName
     
     'etc for other fields.

   .Update

    'Save the primary key value, to use as the foreign key for the related records.

    .Bookmark=LastModified
     ID=!RFQNumber

    'Duplicate the related RFQ records: append query

    If Me.[frmRFQInputsubform].Form.RecordsetClone.RecordCount>0 Then

     strSql+"INSERT INTO [tblRFQItems]

    (ID,PartNumber,Revision,Material,ProcessDescription,Qty1,Qty2,Qty3,Notes) "&_
    "SELECT"& ID & "As NewID,PartNumber,Revision,Material,ProcessDescription,Qty1,Qty2,Qty3,Notes" &_

    "FROM [tblRFQItems] WHERE ID="& Me.RFQNumber & ";"

     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, , "Command35_Click"
        Resume Exit_Handler

     End Sub

 

[pbaldy]

It would be helpful to know what error(s) you get and where. Off the top you have a spaces issue. This should help:

http://www.baldyweb.com/ImmediateWindow.htm


This should be an =:

strSql+"INSERT INTO [tblRFQItems]

 

[isoman53]

It would be helpful to know what error(s) you get and where. Off the top you have a spaces issue. This should help:

http://www.baldyweb.com/ImmediateWindow.htm


This should be an =:

strSql+"INSERT INTO [tblRFQItems]

Error is a compile error:
"Expected line number or label or statement or end of statement"

Showing in red:

  strSql=:"INSERT INTO [tblRFQItems] (PartNumber,Revision,Material,ProcessDescription,Qty1,Qty2,Qty3,Notes) "& _

     "SELECT" & ID & " As NewID, PartNumber,  Revision, Material, ProcessDescription ,Qty1,Qty2,Qty3,Notes" & _

     "FROM [tblRFQItems] WHERE ID = " & Me.RFQNumber & ";"

 

[pbaldy]

Are there actually blank lines between? There can't be.

 

[isladogs]

As pbaldy said there shouldn't be an empty line between SQL code

Also its still incorrect at the start. REMOVE THE COLON :
It should start with

strsql="INSERT

There should also be a space after SELECT and after each comma

 

[isoman53]

OK I see where I missed some stuff and have made some changes. It still stops at .Update with the following error:

Run-time error '3058':
Index or primary key cannot contain a Null value.

Here is my code after corrections:

Private Sub Command35_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 ldslD As Long    'Primary key value of the new record.
'Save any 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
Supplier = "Enter Supplier"
'etc for other fields.
.Update
'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = LastModified
     ID = !RFQNumber
'Duplicate the related RFQ records: append query
If Me.[frmRFQInputsubform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblRFQItems] (PartNumber, Revision, Material, ProcessDescription, Qty1, Qty2, Qty3, Notes) " & _
"SELECT" & ID & " As NewID, PartNumber,  Revision, Material, ProcessDescription, Qty1, Qty2, Qty3, Notes" & _
"FROM [tblRFQItems] WHERE ID = " & Me.RFQNumber & ";"
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, , "Command35_Click"
Resume Exit_Handler
End Sub

 

[Frothingslosh]

It's telling you the problem. You're attempting to insert a NULL value into your primary key. If it's an AutoNumber field, don't include it in your insert statement. If it's not, then you need to validate your data to ensure that that particular field always has data.

 

[Cronk]

Isoman

Another technique if you want to carry certain field values into the next new record, is to set, in the Form's afterUpdate event, the default value of the various controls to the previous values saved.

Me.yourControl.defaultValue = me.yourControl

 

[isoman53]

Isoman

Another technique if you want to carry certain field values into the next new record, is to set, in the Form's afterUpdate event, the default value of the various controls to the previous values saved.

Me.yourControl.defaultValue = me.yourControl

This was exactly what I needed. I hadn't thought of that method. Much appreciated!