recordset looping problem

[Cansley]

Please help!
I need to examine a table and find first number >1 in "location" field, then replace "location"= zero until next "location" >1 then replace following zero "location"s with the new number & so one. I know I have a problem with the second loop, but can't figure it out.

example- if raw data is:

10
0
0
0
20
0
0
0

I need:

10
10
10
10
20
20
20
20


and this code gives me:

10
10
10
10
20
10
10
10


here is what I have so far:



Sub copyloctions()

'Declare and instantiate a recordset
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

Dim strSQL As String
Dim strSQL1 As String
Dim plug As Integer
Dim counter


'Establish the connection, cursor type,
'and lock type, and open the recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open ("Select * from Master")

strSQL = "location > 1"
strSQL1 = "location < 1"


'Loop through the recordset, locating all rows meeting
'the designated criteria,

rst.Find strSQL

Do Until rst.EOF
plug = rst("location")
rst.Find strSQL1

Do Until rst.EOF
If rst("location") > 1 Then GoTo exit1
rst("location") = plug
rst.Update
rst.Find strSQL1, 1, adSearchForward
Loop
exit1:

rst.Find strSQL, 1, adSearchForward

Loop


rst.Close
Set rst = Nothing

End Sub



any help is very much appreciated!

thanks again

 

[Jon K]

Try this.

------------------------
Dim rst As ADODB.Recordset
Dim Num As Integer

Set rst = New ADODB.Recordset

rst.Open "Select Location from Master", CurrentProject.Connection, adOpenDynamic, adLockOptimistic


Do Until rst.EOF

If rst("Location") > 0 Then
Num = rst("Location")
Else
rst("Location") = Num
rst.Update
End If

rst.MoveNext

Loop

Set rst = Nothing
------------------------

 

[Cansley]

THANKS

works great!