Recordset problems

[gdonald20]

Why does this work the first time and not the second?

I am trying to process records in a loop inside a loop – and it kind of
works. (i.e. it doesn’t!)

In the code shown below, the record set Sales Order Lines has two records,
each with a unique Id, SOL_Id

The requirement, clearly, is to process the set of Retail Lines pertaining
to each of the two Sales order Lines.

Hence, the parameter prmRetail_Lines_Value is set each time.

The first time it works perfectly and I can track it round the loop. It then
moves to the second record and at the line: rstRetail_Lines_Open.cmd….etc

It delights me with the famous “Data type mismatch in criteria expressionâ€!

By watching the debug, the first SOL_Id value is 672 followed by 701 for the
second (as if that matters) and they correspond perfectly to the recordset.

What I cannot understand is why it works the first time and gives no error
but when it advances to the second, there is some kind of mismatch.

All assistance and pointers gratefully accepted.

With rstSales_Order_Lines

..MoveFirst
Do Until .EOF
prmRetail_Lines.Value = !SOL_Id
rstRetail_Lines.Open cmdRetail_Lines, , adOpenDynamic, adLockOptimistic,
adCmdStoredProc

With rstRetail_Lines

..MoveFirst
Do Until .EOF

(the processing bit inside seems fine)

.MoveNext
Loop
..Close

End With
.MoveNext
Loop
..Close
End With


Thanks for any help

 

[Steve Sanford]

In the past I tried using nested WITHs and never could get them to work.

Try modifying the code to use only one With statement:

' With rstSales_Order_Lines
rstSales_Order_Lines.MoveFirst

Do Until rstSales_Order_Lines.EOF
prmRetail_Lines.Value = rstSales_Order_Lines!SOL_Id
rstRetail_Lines.Open cmdRetail_Lines, , adOpenDynamic,
adLockOptimistic, adCmdStoredProc

With rstRetail_Lines
.MoveFirst
Do Until .EOF
' (the processing bit inside seems fine)
.MoveNext
Loop
.Close
End With
rstSales_Order_Lines.MoveNext
Loop
rstSales_Order_Lines.Close
' End With


Also note that for both recordsetd rstSales_Order_Lines and rstRetail_Lines,
if no records are returned, when you reach the line

.MoveFirst

you will bomb with a 3021 error "No current record".

I always check to see the state of BOF and EOF of a recordset. If they are
both TRUE, then no records are in the recordset.


HTH