Hello all, Happy Holidays
I used a code from Allen Browne website to pass value from Form and Subform to a new table, but it does not work for me. Can someone take a look at the code and see what is wrong? I get a Compile error: Method or data member not found on my WHERE clause, at least that where it is highlighted. Not sure how to run the Debug.Print on this code.
Private Sub cmdSaveRevision_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 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
![BOM No] = Me.BOM_No
![Version Code] = Me.VersionCode
![BOM Description] = Me.[BOM Description]
![Unit of Measure Code] = Me.[Unit of Measure Code]
![Status] = Me.Status
![Revision Date] = Me.txtRevisionDate
![Created by] = Me.txt_CreatedBy
![Last Modified by] = Me.txt_LastModifiedBy
![Last Date Modified] = Me.txt_LastDateModified
'etc for other fields.
.Update
'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
lngID = ![BOM No] And [Version Code]
'Duplicate the related records: append query.
If Me.sftblTempMaster.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO tblMaster2 (*) '" & _
"SELECT " & lngID & " As NewID, Type, No, [Variant Code], Description, Quantity, Scrap % '" & _
"FROM tblMaster2 WHERE [BOM No]= '" & Me.BOM_No & " ' AND [Version Code]= '" & Me.Version_Code & "';"
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, , "cmdSaveRevision_Click"
Resume Exit_Handler
End Sub
I used a code from Allen Browne website to pass value from Form and Subform to a new table, but it does not work for me. Can someone take a look at the code and see what is wrong? I get a Compile error: Method or data member not found on my WHERE clause, at least that where it is highlighted. Not sure how to run the Debug.Print on this code.
Private Sub cmdSaveRevision_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 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
![BOM No] = Me.BOM_No
![Version Code] = Me.VersionCode
![BOM Description] = Me.[BOM Description]
![Unit of Measure Code] = Me.[Unit of Measure Code]
![Status] = Me.Status
![Revision Date] = Me.txtRevisionDate
![Created by] = Me.txt_CreatedBy
![Last Modified by] = Me.txt_LastModifiedBy
![Last Date Modified] = Me.txt_LastDateModified
'etc for other fields.
.Update
'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
lngID = ![BOM No] And [Version Code]
'Duplicate the related records: append query.
If Me.sftblTempMaster.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO tblMaster2 (*) '" & _
"SELECT " & lngID & " As NewID, Type, No, [Variant Code], Description, Quantity, Scrap % '" & _
"FROM tblMaster2 WHERE [BOM No]= '" & Me.BOM_No & " ' AND [Version Code]= '" & Me.Version_Code & "';"
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, , "cmdSaveRevision_Click"
Resume Exit_Handler
End Sub