Code to AddNew to Recordset only works part of the time (1 Viewer)

[Elana]

Here is my code to add records to a table based on records shown on a subform.

Problem: This will only add records if I open the form and run the code on the first record. If I move to another record in the form and attempt to run the code, it will not add records to the table, BUT, If I close the form, reopen it, move to any record and then run the code, it will work.

Why does it only work on the first attempt after opening the form. Advice Please!

Thanks Elana

Dim dbs As Database
Dim rst As Recordset
Dim rst1 As Recordset


Set dbs = CurrentDb()
Set rst = Forms!frmClaimsEntry!frmPolicyUwrsSubform.Form.RecordsetClone
Set rst1 = dbs.OpenRecordset("tblUwrByClaim")



Do Until rst.EOF
rst1.AddNew
rst1!ClaimID = Forms!frmClaimsEntry!ClaimID
rst1!SUwrID = rst!SUwrID
rst1.Update
rst.MoveNext

Loop


rst.Close
rst1.Close

 

[Rich]

Please don't post the same question under multiple topics, you still have this one under discussion elsewhere.

 

[Elana]

Thanks Rich. I know I broke the rules, but I'm so desperate for an answer that I posted it here too, hoping someone with VBA expertise could help me, in case they weren't looking in the forms forum.

EB

 

[KKilfoil]

I notice the following line in your code:
rst1!SUwrID = rst!SUwrID

If SWwrID is a primary or unique key, you will not be allowed to create a duplicate of an exisitng value.

 

[Elana]

Thanks - I figured out the problem. I needed to add rst.movefirst just before the Do Loop starts.

EB