Hi all,
I have been trying to get an SQL Insert statement to duplicate a form and subform but changing two fields in one of the tables.
I got the following code from allen browne`s site (Can`t post link as first post with this username) after searching around.
This copies the main form record as expected but no matter what I put in the values clause it doesn't seem to copy.
Basically from the following :
I want to copy all the fields as they are on the form but change the OrderHeaderID field to whatever is stored in lngID and to change the OrderTypeID to whatever is in NewOrderTypeID.
I have tried using single and double quotes in place of fields theat I want to keep the same.
Any help will be much appreciated.
I have been trying to get an SQL Insert statement to duplicate a form and subform but changing two fields in one of the tables.
I got the following code from allen browne`s site (Can`t post link as first post with this username) after searching around.
Code:
Private Sub BtnOrder_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 Integer 'Primary key value of the new record.
Dim NewOrderTypeID As Integer
'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
!AccountID = Me.AccountID
!OrderDate = Me.OrderDate
!DueDate = Me.DueDate
!Reference = Me.Reference
!OrderStatusID = "3"
!OrderTypeID = "5"
'etc for other fields.
.Update
'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
lngID = !OrderHeaderID
NewOrderTypeID = 5
'Duplicate the related records: append query.
If Me.[FrmOrderDetails].Form.RecordsetClone.RecordCount > 0 Then
Stop
strSql = "INSERT INTO [TblOrderDetails] (OrderDetailsID, OrderHeaderID, OrderTypeID, ProductID, Discount, Quantity, ListPrice)" & _
"VALUES(OrderDetailsID," & lngID & "," & NewOrderTypeID & ",ProductID,Discount,Quantity,ListPrice)" & _
"SELECT " & lngID & " As NewID, OrderHeaderID, OrderTypeID, ProductID, Discount, Quantity, ListPrice " & _
"FROM [TblOrderDetails] WHERE OrderHeaderID = " & Me.OrderHeaderID & ";"
Debug.Print strSql
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
This copies the main form record as expected but no matter what I put in the values clause it doesn't seem to copy.
Basically from the following :
Code:
"VALUES(OrderDetailsID," & lngID & "," & NewOrderTypeID & ",ProductID,Discount,Quantity,ListPrice)"
I want to copy all the fields as they are on the form but change the OrderHeaderID field to whatever is stored in lngID and to change the OrderTypeID to whatever is in NewOrderTypeID.
I have tried using single and double quotes in place of fields theat I want to keep the same.
Any help will be much appreciated.