Recordsets ....I'm missing something easy here.

[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. 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

[This message has been edited by Elana (edited 04-07-2002).]

 

[Fizzio]

Which event do you have the code attatched to or is it called by a button press? Is there a reason why you are using a recordset ?

 

[Elana]

For now I have it attached to a button. I'm going to move it once I've worked out the glitch.

I'm using a recordset clone because I need to refer to the records behind one form to add records to a table. It was the only way I could think of to do it.

It works just fine, except for the problem I've described. I must be missing some kind of event order or something like that?

 

[Rich]

Why can't you use an append query?

 

[Elana]

Hi Rich - thanks for looking at my post. As a bit of background, I need Access to look at the underwriters listed on the subform and add a record to tblUwrByClaim for each underwriter on the form.

The thing is, this code works just fine, so long it only runs on the first record after I open the main form (frmclaimsentry). If I open the form, run the code on one claim and then move to another claim record and attempt to run the code again, it won't update tblUwrByClaim.

I figure since it works some of the time, that only I'm missing some code that will make it run for the next record.

If an append query is the better way to handle this, could you give me an idea of how you would write the code? My brain is beginning to get fried. A little push in the right direction would be a big help.

 

[Elana]

Okay, I've figured out part of this - the second time I try to run the code, it bypasses the DO Loop because the recordset clone is already at the EOF. But, I thought if I closed the recordset at the end of the procedure, that it would wipe out the recordset clone. Obviously I'm missing something. Any thoughts about how I can fix this?

Thanks

 

[Elana]

Okay, I figured it out myself!

Just add rst1.movefirst to the code just prior to the DO Loop.

 

[chrismcbride]

First of all I think you only need one recordset, the one that holds the value you want to copy. You can fill that recordset, loop thru it and use DoCmd.RunSql to add records to you base table.

On error GoTo EH

Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblUwrByClaim")

If rst.recordCount > 0 then
rst.MoveLast ' populates rst
rst.MoveFirst
DoCmd.SetWarnings False

Do Until rst.EOF

DoCmd.RunSql "INSERT INTO tblWhatever (ClaimID, SUwrID) VALUES (" & me.ClaimID
& ", " & rst!SUwrID & ");"

rst.MoveNext

Loop

end if

ExitProc:
set rst = nothing ' Not enough to close your
set dbs = nothing ' objects - must destroy
' them
DoCmd.SetWarnings True
Exit Sub

EH:
msgbox err.number & " - " & err.description
resume ExitProc

Try this and see if it works for you....
Chris




[This message has been edited by chrismcbride (edited 04-08-2002).]