Looping Through Recordset - Problem updating records

[Matt W]

Hi All

I'm looping through a recordset and updating one field in it. I have
prompts to indicate the values, so I know it is working "OK", but when
I leave the loop and re-read the values, it has copied the final value
for the last row into the entire field, rather than the individual
values.

Any help would be greatly appreciated!
Thanks!
Matt

The code is -

rs1.MoveFirst
Do While Not rs1.EOF
rs1.Edit
rs1!Radial_Distance = Sqr(((rs1!Model_East - a) * (rs1!
Model_East - a)) + ((rs1!Model_North - b) - (rs1!Model_North - b)))
V = rs1!Radial_Distance
MsgBox (V)
Ground = rs1!GS
MsgBox (Ground)
rs1.Update
rs1.MoveNext
Loop
MsgBox ("first dump after loop")
rs1.MoveFirst
Do While Not rs1.EOF
MsgBox (rs1!Model_East)
MsgBox (V)
MsgBox (Ground)
rs1.MoveNext
Loop

 

[pietlinden]

Hi All

I'm looping through a recordset and updating one field in it.  I have
prompts to indicate the values, so I know it is working "OK", but when
I leave the loop and re-read the values, it has copied the final value
for the last row into the entire field, rather than the individual
values.

Any help would be greatly appreciated!
Thanks!
Matt

The code is -

    rs1.MoveFirst
        Do While Not rs1.EOF
        rs1.Edit
        rs1!Radial_Distance = Sqr(((rs1!Model_East - a) * (rs1!
Model_East - a)) + ((rs1!Model_North - b) - (rs1!Model_North - b)))
        V = rs1!Radial_Distance
        MsgBox (V)
        Ground = rs1!GS
        MsgBox (Ground)
        rs1.Update
        rs1.MoveNext
        Loop
    MsgBox ("first dump after loop")
 rs1.MoveFirst
    Do While Not rs1.EOF
    MsgBox (rs1!Model_East)
    MsgBox (V)
    MsgBox (Ground)
    rs1.MoveNext
    Loop

Pardon me for asking, but is there a reason that a simple update query
will not work in this instance?

 

[JvC]

Matt,

Re-read the code in your loop to look at the recordset. You are putting
rs1!Model_East out to a msgbox, and your code doesn't update that
field. You are then msgboxing (new verb!) V and Ground, which are
program variables, and are not dependent on the individual record.

John

Matt W wrote on 8/6/2008 :

 

[Matt W]

Matt,

Re-read the code in your loop to look at the recordset. You are putting
rs1!Model_East out to a msgbox, and your code doesn't update that
field. You are then msgboxing (new verb!) V and Ground, which are
program variables, and are not dependent on the individual record.

John

Matt W wrote on 8/6/2008 :

Thanks to both of you for the responses!

Regarding Piet's question - I can give that a try, I hadn't thought
that approach so thanks!

Regarding your question John, I had lots of extraneous stuff in the
code I posted. Here's a repost with the irrelevant stuff taken out
(msgboxes and Model_East display which was just to let me know which
record it was at).

The basic problem is that I have the same value for Radial_Distance in
rs1 when I read it after leaving the first loop. Even through when I
loop through the first loop I can see the value of Radial_Distance
changing. My code seems to update the Radial_Distance field for all
records with just the last value that is calculated in the first loop?

Thanks again and appreciate your time.
Matt

rs1.MoveFirst
Do While Not rs1.EOF
rs1.Edit
rs1!Radial_Distance = Sqr(((rs1!Model_East - a) * (rs1!
Model_East - a)) + ((rs1!Model_North - b) - (rs1!Model_North - b)))
V = rs1!Radial_Distance
MsgBox (V)
rs1.Update
rs1.MoveNext
Loop

MsgBox ("first dump after loop")
rs1.MoveFirst
Do While Not rs1.EOF
MsgBox (V)
rs1.MoveNext
Loop

 

[JvC]

Try this for your loop to view the values in your table:

rs1.MoveFirst
Do While Not rs1.EOF
MsgBox rs1!Radial_Distance 'V is the variable, it wont change
rs1.MoveNext
Loop

That should work, or am I missing something?

John

Matt W formulated the question :

 

[Matt W]

Try this for your loop to view the values in your table:

rs1.MoveFirst
Do While Not rs1.EOF
     MsgBox rs1!Radial_Distance 'V is the variable, it wont change
     rs1.MoveNext
Loop

That should work, or am I missing something?

John

Matt W formulated the question :

Thanks John

I was being a dingbat - everything works now!

Thanks
Matt