Creating duplicate records with Form command - sql error

[purextc]

Hi There,

I have some experience with sql, but limited experience with access. I am trying to create duplicate parent and child records using a form. I have added the following VB code and I keep getting a syntax erro in insert into statement. I'm hoping (praying) it's something simple that I am just not seeing.

The Parent record is created correctly.

Thanks

Here is the 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 lngID As String '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

'Pass in new Key
myValue = InputBox("Enter Forecast Date", "Inputbox")
MsgBox myValue
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!PPod = Me.PPod
!Engineer = Me.Engineer
!Current_Product = Me.Current_Product
!Bag_size_kg = Me.Bag_size_kg
!Ppod_downtime = Me.Ppod_downtime
!Date = Me.Date
!Key = Me.PPod & "-" & myValue

.Update

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


'Duplicate the related records: append query.
If Me.[RE_Forecast_Inj subform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [RE_Forecast_Inj] ( Key, Pattern, Ppod, Date, Qinj_m3d, Cppm, Bags_Week ) " & _
"SELECT " & lngID & " As NewID, Pattern, Ppod, Date, Qinj_m3d, Cppm, Bags_Week " & _
"FROM [RE_Forecast_Inj] WHERE Key = " & Me.Key & ";"
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

 

[JHB]

I think you are missing the "(" and ")":
So try with:

strSql = "INSERT INTO [RE_Forecast_Inj] (Key, Pattern, Ppod, Date, Qinj_m3d, Cppm, Bags_Week) " & _
"SELECT (" & lngID & " As NewID, Pattern, Ppod, Date, Qinj_m3d, Cppm, Bags_Week) " & _
"FROM [RE_Forecast_Inj] WHERE Key = " & Me.Key & ";"

 

[purextc]

Hi There,
Thanks so much for responding this is driving me crazy and putting me behind schedule. However you copied the code I had exactly I think you meant to modify it?

Thanks,
S.

 

[JHB]

Hi There,
However you copied the code I had exactly I think you meant to modify it?

Not exactly, se the "red" ( ...):

strSql = "INSERT INTO [RE_Forecast_Inj] (Key, Pattern, Ppod, Date, Qinj_m3d, Cppm, Bags_Week) " & _
"SELECT (" & lngID & " As NewID, Pattern, Ppod, Date, Qinj_m3d, Cppm, Bags_Week) " & _
"FROM [RE_Forecast_Inj] WHERE Key = " & Me.Key & ";"

 

[purextc]

Figured it out. I needed to add single quotes around my key values:
SELECT '" & lngID & "' As NewID, Pattern, Ppod, Date, Qinj_m3d, Cppm, Bags_Week " & _
"FROM [RE_Forecast_Inj] WHERE Key = '" & Me.Key & "';"

However now I can't get my date to insert any idea on that one? The column is set to a date, and it's copying a date but i get a sql error on the insert. If I take the date out of the insert statement everything works perfect.

Thanks!

 

[JHB]

What is the Error description you get, (not only the number, but the whole description)?

Only to info: It is a bad idea to have "Date" as fieldname, it is a "key" word in MS-Access.