Duplicate record code almost works

B

Ben

I have the following code that duplicates the main form, but not the subform.

With Me.RecordsetClone
.AddNew
!ProjectName = Me.ProjectName
!DEASRALeads = Me.DEASRALeads
!Phase1 = Me.Phase1
!Phase2 = Me.Phase2
!Phase3 = Me.Phase3
!Phase4 = Me.Phase4
!Phase5 = Me.Phase5
!Phase1Status = Me.Phase1Status
!Phase2Status = Me.Phase2Status
!Phase3Status = Me.Phase3Status
!Phase4Status = Me.Phase4Status
!Phase5Status = Me.Phase5Status
!Other = Me.Other


'etc for other fields.
.Update

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



'Duplicate the related records: append query.
If Me.[frmWebFarmAIs].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblWebFarmAIs] ( WebFarmAIID,
ActionItemsNew, Owner, WebFarmID ) " & _
"SELECT " & lngID & " As NewID, WebFarmAIID,
ActionItemsNew, Owner, WebFarmID " & _
"FROM [tblWebFarmAIs] WHERE WebFarmID = " & Me.WebFarmID
& ";"
'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

Please help

Ben
 
D

Dale Fye

My first thought is that your insert statement only includes 4 fields, but
the SELECT statement that goes with it includes 5.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
B

Ben

I guess I'm a little confused. I thought the select statement was for the
subform only. Is it for both?

Ben

Dale Fye said:
My first thought is that your insert statement only includes 4 fields, but
the SELECT statement that goes with it includes 5.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Ben said:
I have the following code that duplicates the main form, but not the subform.

With Me.RecordsetClone
.AddNew
!ProjectName = Me.ProjectName
!DEASRALeads = Me.DEASRALeads
!Phase1 = Me.Phase1
!Phase2 = Me.Phase2
!Phase3 = Me.Phase3
!Phase4 = Me.Phase4
!Phase5 = Me.Phase5
!Phase1Status = Me.Phase1Status
!Phase2Status = Me.Phase2Status
!Phase3Status = Me.Phase3Status
!Phase4Status = Me.Phase4Status
!Phase5Status = Me.Phase5Status
!Other = Me.Other


'etc for other fields.
.Update

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



'Duplicate the related records: append query.
If Me.[frmWebFarmAIs].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblWebFarmAIs] ( WebFarmAIID,
ActionItemsNew, Owner, WebFarmID ) " & _
"SELECT " & lngID & " As NewID, WebFarmAIID,
ActionItemsNew, Owner, WebFarmID " & _
"FROM [tblWebFarmAIs] WHERE WebFarmID = " & Me.WebFarmID
& ";"
'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

Please help

Ben
Click to expand...
Click to expand...
 
D

Dale Fye

Ben,

The part of your code I am talking about currently looks like (I've taken
the liberty to add line breaks for readability):

If Me.[frmWebFarmAIs].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblWebFarmAIs] " _
& "(WebFarmAIID, ActionItemsNew, Owner, WebFarmID ) " _
& "SELECT " & lngID & " As NewID, WebFarmAIID, ActionItemsNew,
" _
& "Owner, WebFarmID " & _
& "FROM [tblWebFarmAIs] " _
& "WHERE WebFarmID = " & Me.WebFarmID
'DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

There are two problems with this.
1. You have indicated in the SQL statement that you want to insert 4 fields
(WebFarmAIID, ActionItemsNew, Owner, WebFarmID). But in the second (SELECT)
part of that SQL statement, you include 5 fields (lngID & " As NewID,
WebFarmAIID, ActionItemsNew, Owner, WebFarmID) you want to SELECT. In this
type of a query, the fields in the INSERT portion must match (both the count
and data types) the fields shown in the SELECT portion of the query.

2. The line that looks like:

'DBEngine(0)(0).Execute strSql, dbFailOnError

has a single quote at the beginning of the line, which actually prevents
that line from firing (but I'm sure you already knew that). Personally, I
generally just use:

Currentdb.Execute strSQL, dbFailOnError

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Ben said:
I guess I'm a little confused. I thought the select statement was for the
subform only. Is it for both?

Ben

Dale Fye said:
My first thought is that your insert statement only includes 4 fields, but
the SELECT statement that goes with it includes 5.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Ben said:
I have the following code that duplicates the main form, but not the subform.

With Me.RecordsetClone
.AddNew
!ProjectName = Me.ProjectName
!DEASRALeads = Me.DEASRALeads
!Phase1 = Me.Phase1
!Phase2 = Me.Phase2
!Phase3 = Me.Phase3
!Phase4 = Me.Phase4
!Phase5 = Me.Phase5
!Phase1Status = Me.Phase1Status
!Phase2Status = Me.Phase2Status
!Phase3Status = Me.Phase3Status
!Phase4Status = Me.Phase4Status
!Phase5Status = Me.Phase5Status
!Other = Me.Other


'etc for other fields.
.Update

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



'Duplicate the related records: append query.
If Me.[frmWebFarmAIs].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblWebFarmAIs] ( WebFarmAIID,
ActionItemsNew, Owner, WebFarmID ) " & _
"SELECT " & lngID & " As NewID, WebFarmAIID,
ActionItemsNew, Owner, WebFarmID " & _
"FROM [tblWebFarmAIs] WHERE WebFarmID = " & Me.WebFarmID
& ";"
'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

Please help

Ben
Click to expand...
Click to expand...
Click to expand...
 
Top