Trouble with looping in VBA

[Biz Enhancer]

Hi All,

I usually get around this sort of issue with multiple queries but I've
decided to tackle the beast this time.

The problem is that I wish to loop through inventory selecting the oldest
batches to fill an order and to append to a table the required amount from
each batch. Each order will have multiple inventory items, therefore
requiring a loop through the order then a loop through the recordset of
batches based on that inventory item.

E.G.

rs1: ' the order
invitem qnty
Blue 15
Green 20
Red 100

rs2: ' returned records from querying the inventory table
stkid invitem qty
1 red 10
7 blue 12
9 red 80
10 red 50
16 green 40
22 blue 30
23 blue 35

For Each i In rs1
If rs1.qnty < rs2.qty Then
DoCmd.RunSQL "INSERT into rprocess (stkid, qty) SELECT rs2.stkid, rs2
qnty FROM rs2;"
Else
For Each i In rs2
QT = rs1.qnty
Do While QT > 0
DoCmd.RunSQL "INSERT into rprocess (stkid, qty) SELECT rs2.stkid,
rs2.qty FROM rs2;"
QT = QT - rs2.qty
.Next
Loop
End If
..Next
Loop

Results appended to table would equal:

stkid invitem qty
1 red 10
7 blue 12
9 red 80
10 red 10
16 green 20
22 blue 3

I know my code is somewhat screwy but I am hoping someone can straighten me
out.
Thanks in advance.

Regards,

Nick.

 

[Stefan Hoffmann]

hi Biz,

cool name. Praise your parents.

For Each i In rs1
If rs1.qnty < rs2.qty Then
DoCmd.RunSQL "INSERT into rprocess (stkid, qty) SELECT rs2.stkid, rs2
qnty FROM rs2;"
Else
For Each i In rs2
QT = rs1.qnty
Do While QT > 0
DoCmd.RunSQL "INSERT into rprocess (stkid, qty) SELECT rs2.stkid,
rs2.qty FROM rs2;"
QT = QT - rs2.qty
.Next
Loop
End If
.Next
Loop
I know my code is somewhat screwy but I am hoping someone can straighten me
out.

The For Each construct is only for objects supporting the IEnumerator
interface. For looping thru datasets you use the following:

If Not rs.Bof And Not rs.Eof Then
Do While Not rs.Eof
'your code
rs.Next
Loop
End If

The If-conditional ensures that data is available and the Do-Loop with
the rs.Next navigates you thru your data.


mfG
--> stefan <--

 

[Biz Enhancer]

Many Thanks Stefan.
The great thing about this forum is the number of knowledgable programmers
who are willing to spread the wealth.

 

[Stefan Hoffmann]

hi,

Many Thanks Stefan.
The great thing about this forum is the number of knowledgable programmers
who are willing to spread the wealth.

It's not pure benevolence. Explaining knowledge improves also my
comprehension.


mfG
--> stefan <--