Duplicate a Recordset

[john]

I have a default recordset of 24 records that are part of a contract that I
need to be able to duplicate and then just change its ID field to a new
value in all 24 records, so that I can then make the some other ajustments
to the new contract and have them as a saved recordset.

I've tried using the menu command codes to duplicate each record one at a
time through a loop with then following code, but it fails to duplicate the
record and returns an error code stating a "BeforeUpdate or ValidationRule
for the field is preventing Access from saving the Data"

I have checked all the fields and there are no ValidationRules set for them
and I have no BeforeUpdate events running either.

1st question is there a way to program a copy of the entire recordset at
once with only the EstimateItemID field being changed to TheNewValue, which
is a Variable.

If not, how do I make this code work to navigate through the "Default"
recordset, copy each record and update it with the TheNewValue.



Dim db As Database, RS As Recordset
Dim strSQL, MatrixID As String
Set db = CurrentDb

MatrixID = TheNewValue

Set db = CurrentDb
strSQL = "SELECT * FROM [EstimatingMatrixInfoQuery] WHERE [EstimateItemID] =
'Default' and [EstimateCostItemFormQueue] = " & Me.ComboMatixFormQ & ""

Set RS = db.OpenRecordset(strSQL)

RS.MoveFirst

Do Until RS.EOF


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

'I've also tried using the runcommand series of copy and paste append with
the same errors.


'Modify Data

With RS
.Edit
!EstimateItemID = MatrixID
.Update
End With

RS.MoveNext

Loop

RS.Close
Set db = Nothing

 

[Alex]

Hi John,

2 different ways to get this to work,

1. insert query (append from in access)

2. some code, in ADO

Dim adoRead As New ADODB.Recordset
Dim adoInsert As New ADODB.Recordset
Dim intFieldCnt As Integer
With adoInsert
.Open "Select * from TblTest", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic
End With
With adoRead
.Open "Select * from TblTest Where field1=1",
CurrentProject.Connection, adOpenKeyset, adLockReadOnly
Do While Not .EOF
intFieldCnt = 1
adoInsert.AddNew
adoinsert.Fields("field1").Value = 'what ever you want the
first field value to be
Do While intFieldCnt < .Fields.Count
If Not IsNull(.Fields(intFieldCnt).Value) Then
adoInsert.Fields(intFieldCnt).Value =
..Fields(intFieldCnt).Value
End If
intFieldCnt = intFieldCnt + 1
Loop
adoInsert.Update
.MoveNext
Loop
.Close
End With
adoInsert.Close

the above code is using ADO but can be converted to DAO very quickly.

Hope helps

Regards

Alex

 

[john]

Is there a reason that I cannot use the menu code or runcommannds, I do not
understand their purpose if they cannot be used for this.

 

[Alex White]

Hi John,

Couple of reason why I have not checked that route is you have one field
that needs to be different and in most cases this could be a problem
with the menu commands. Code will always give you extra flexibility as
well.

If you are not happy with coding the solution I will look into the
runcommands route for you but you will loose flexibility via that route.

Regards

Alex White MCSE MCDBA
www.IntraLAN.co.uk


-----Original Message-----
From: john [mailto:[email protected]]
Posted At: 24 April 2005 19:23
Posted To: microsoft.public.access.formscoding
Conversation: Duplicate a Recordset
Subject: Re: Duplicate a Recordset

Is there a reason that I cannot use the menu code or runcommannds, I do
not
understand their purpose if they cannot be used for this.

 

[john]

No that's Ok, I was just wondering about them, this will work I'm sure.

Thank you

Hi John,

Couple of reason why I have not checked that route is you have one field
that needs to be different and in most cases this could be a problem
with the menu commands. Code will always give you extra flexibility as
well.

If you are not happy with coding the solution I will look into the
runcommands route for you but you will loose flexibility via that route.

Regards

Alex White MCSE MCDBA
www.IntraLAN.co.uk


-----Original Message-----
From: john [mailto:[email protected]]
Posted At: 24 April 2005 19:23
Posted To: microsoft.public.access.formscoding
Conversation: Duplicate a Recordset
Subject: Re: Duplicate a Recordset

Is there a reason that I cannot use the menu code or runcommannds, I do
not
understand their purpose if they cannot be used for this.

Hi John,

2 different ways to get this to work,

1. insert query (append from in access)

2. some code, in ADO

Dim adoRead As New ADODB.Recordset
Dim adoInsert As New ADODB.Recordset
Dim intFieldCnt As Integer
With adoInsert
.Open "Select * from TblTest", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic
End With
With adoRead
.Open "Select * from TblTest Where field1=1",
CurrentProject.Connection, adOpenKeyset, adLockReadOnly
Do While Not .EOF
intFieldCnt = 1
adoInsert.AddNew
adoinsert.Fields("field1").Value = 'what ever you want the
first field value to be
Do While intFieldCnt < .Fields.Count
If Not IsNull(.Fields(intFieldCnt).Value) Then
adoInsert.Fields(intFieldCnt).Value =
.Fields(intFieldCnt).Value
End If
intFieldCnt = intFieldCnt + 1
Loop
adoInsert.Update
.MoveNext
Loop
.Close
End With
adoInsert.Close

the above code is using ADO but can be converted to DAO very quickly.

Hope helps

Regards

Alex