help with my code, No Current Record error continues to haunt me

i have two tables : one is a header and the other is for line items.
in both the header and line item table is a field called "ORDER_NO" and
both have corresponding numbers.

(i.e. in the header can be a 2
and in the line items can be several 2's that hold all of the order's
items for that particular order.)

i am trying to assign order numbers to replace the 2,3,4, etc in both
the header and the line items tables.

i am successfull with the following code, however ,when the last of the
2,3,4,etc is reached, i get the "No current record"(3021) error.

Can someone please help me solve this error from creeping up?
Thanks in advance.


'rs1 is the header table and rs3 is the line items table

'MaxNum is the number of records counted in the header table.

'OrdNum is the order number assigned (number received from
'a previous operation).

' i is a placeholder that holds the 2, 3, 4, etc and is incremented
' and then is replaced with the order number.

'assign order numbers to the orders
rs1.MoveFirst

For i = 1 To MaxNum
i = rs1.Fields("ORDER_NO")
rs1.Edit
rs1.Fields("ORDER_NO") = OrdNum
rs1.Update
rs1.MoveNext

While rs3.Fields("ORDER_NO") = i
rs3.Edit
rs3.Fields("ORDER_NO") = OrdNum
rs3.Update
rs3.MoveNext
Wend

OrdNum = OrdNum + 1

Next

[J Staniforth]

While rs3.Fields("ORDER_NO") = i
rs3.Edit
rs3.Fields("ORDER_NO") = OrdNum
rs3.Update
rs3.MoveNext
Wend

The last rs3.MoveNext will always attempt to read past the end of the recordset and fails.
There are several alternatives - but I would strongly recommend you always test eof first - there may even be less lines(missing) than you anticipated!

This should work--

While NOT(rs3.eof) AND rs3.Fields("ORDER_NO") = i

Regards,
John.



------------------
Woodland Trust - Preserving & Creating Native Woodland
www.woodland-trust.org.uk

[john_murphy]

You are changing the value of your for loop as well!!!

Bad Boyyyyy

[john_murphy]

Seriously you should be doing something like this

findstring = "Order_num = " & order_to_change
rs1.findfirst findstring
rs1.edit
rs1("order_num") = Ordernum
rs1.update

rs3.movefirst
rs3.findfirst findstring
do while rs3.nomatch = false
rs3("order_num") = Ordernum
rs3.findnext findstring
loop

this is a lot easier for you to read etc.....

[john_murphy]

spot the deliberate mistake!!! missed the second update!!! sorry

i've tried

While NOT(rs3.eof) AND rs3.Fields("ORDER_NO") = i

without success. still get no current record
error. funny thing, i checked all the records and i get everything in the tables with the correct matching order numbers.

there must be a way, i have yet to find it...

thanks,

[Clunietp]

Just use a

do until rs3.EOF = true

DoStuff

Loop

-- no AND conditions

i finally got something to work here if
anyone is interested.

i removed the for..next loop, realized i didn't need it after all.

i use a variable OrdID to hold the original ORDER_NO value.

i added:
If rs3.EOF = True Then
Exit Do
Else
End If
after the rs3.movenext and that works.

i am getting all of my information that i need..

thanks for all the suggestions,
larry

'//assign order numbers to the orders
rs1.MoveFirst
Do

rs1.Edit
OrdID = rs1.Fields("ORDER_NO")
rs1.Fields("ORDER_NO") = OrdNum
rs1.Update
If Not rs3.EOF Then

Do While rs3.Fields("ORDER_NO") = OrdID
rs3.Edit
rs3.Fields("ORDER_NO") = OrdNum
rs3.Update
rs3.MoveNext
If rs3.EOF = True Then
Exit Do
Else
End If
Loop
End If 'rs3.EOF
OrdNum = OrdNum + 1
rs1.MoveNext
Loop Until rs3.EOF = True