How do I iterate through records on a continuou subform?

[Robert Meerwaldt]

Hi,

I have an order form, with a continuous order detail subform. The order
detail subform has a field 'QuantityReceived' (that can be edited), to
reflect how much of an item has actually arrived in a certain order. When
the user edits the values of the received quantities, I want to adjust the
stocklevels in the table TbItems accordingly for each items in the order.

Now the updating is not the problem, I have figured that one out (the rsorg
in the code is a recordset snapshot of the records before we allow editing).
The problem I am facing is how to iterate through all the records in the
order detail subform. I have tried various things but have not come up with
a solution. The code below is as far as I have gotten. I thought of using
the For Each ... Next loop, but cannot get it to work, probably because of
my limited knowledge of the VBA syntax. I am originally an Oracle
programmer, and with PL/SQL I could write the code blindly ...

Any help would be highly appreciated!

Robert

SAMPLE CODE:
'Update the stocklevel in TbItems from the Quantity Received field if it has
changed
For Each {record} in {FrmOrderdetails}
If FrmOrderDetails!QuantityReceived <> rsorg!QuantityReceived Then
strSQL = "UPDATE TbItems " & _
"SET TbItems.Stock = TbItems.Stock + " &
Forms!FrmOrders!FrmOrderDetails.Form!QuantityReceived & " " & _
"WHERE TbItems.Item_ID = " &
Forms!FrmOrders!FrmOrderDetails.Form!Item_ID
CurrentDb.Execute strSQL, dbFailOnError
End If
Next

 

[TC]

This will iterate through all the records in the curent form - be it a
main form, or a subform. This does consider any filters that you might
have set.

(untested)

with me.recordsetclone
if not (.bof and .eof) then
.movefirst
while not .eof
debug.print .fields(0).name, .fields(0).value
.movenext
wend
endif
end with

You'd better copy & paste that from this post, so you get all the
leading dots!

The code, as written, will just output the name & value of the first
field, from each record, to the debug window.

The IF-test is because .movefirst will fail if the (sub)form currently
has NO records.

HTH,
TC

 

[TC]

PS. Also, you should not repeatedly call currentdb() in the way you are
doing. Each call refreshes all collections, which is a resource
intensive operation, & takes a measurable amount of time to do. Better
to cache the value locally:

dim db as database
set db = currentdb()
....
(now use db.blah instead of currentdb.blah)
....
set db = nothing

HTH,
TC

 

[Robert Meerwaldt]

Thanks for the speedy reply TC!

Will give your suggestions a go later this afternoon.

Robert

 

[TC]

No probs, hope it helps.

Cheers,
TC
(off for din-dins!)