Do While loop not fully functioning

[Malcy]

Hi
Sorry this is a fresh post resulting from earlier ones where (with a lot of help) I got so far but not quite there. Perhaps redefining the current state of play will help.

I have a Do While loop (code shown below) which works off a recordset where all records meet the initial criteria for the internal Do While loop, i.e. they have a SupQty >499
The first record in the record set processes perfectly. On my test data it has a SupQty value of 1050. It passes through the loop twice (giving a 500 record each time) and then appends a third record of 50
Once it is deleted, however, and the loop goes through again for the second record in the test data it does not "read" the SupQty for what is now the active record (which should be 700).

I have run it through the debug and it shows an erroneous SupQty value and jumps straight to the second part and appends one record of 700 through qapp_tblTmpDispLab2a
Do I need to somehow refresh the recordset so that it can recognise the new record?
Alternatively does anyone have any suggestions as to how I can achieve the subsequent records behaving as the first does?
Any help greatly appreciated.
Thanks

Malcy


code
_____________________________
Do While Not rst.EOF

' edit a record in our recordset
.Fields("Act") = -1
.Update

' Process active record whilst quantity is greater than 500
Do While Me.SupQty > 499
DoCmd.SetWarnings False
DoCmd.OpenQuery "qapp_tblTmpDispLab2"
DoCmd.OpenQuery "qupd_tblTmpDispLab1_Bal"
DoCmd.SetWarnings True
Loop

' Append residual amount (less than 500ml) into tblTmpDispLab2
DoCmd.SetWarnings False
DoCmd.OpenQuery "qapp_tblTmpDispLab2a"
DoCmd.SetWarnings True

.Delete
.MoveNext

Loop
___________________________

 

[itsmarkdavies]

Try this :-


Do While Not rst.EOF

' edit a record in our recordset
rst.Fields("Act") = -1
rst.Update

' Process active record whilst quantity is greater than 500
Do While rst.SupQty > 499
DoCmd.SetWarnings False
DoCmd.OpenQuery "qapp_tblTmpDispLab2"
DoCmd.OpenQuery "qupd_tblTmpDispLab1_Bal"
DoCmd.SetWarnings True
Loop

' Append residual amount (less than 500ml) into tblTmpDispLab2
DoCmd.SetWarnings False
DoCmd.OpenQuery "qapp_tblTmpDispLab2a"
DoCmd.SetWarnings True

rst.Delete
rst.MoveNext

Loop

 

[itsmarkdavies]

Sorry, I think I missed the point of what you are trying to do, try the following instead. It should point you in the right direction :-


Dim Qty As Integer

rst.MoveFirst

Do While Not rst.EOF

Qty = rst.SupQty

'edit a record in our recordset
rst.Fields("Act") = -1
rst.Update

'Process active record whilst quantity is greater than 500
If Qty > 499
DoCmd.SetWarnings False
DoCmd.OpenQuery "qapp_tblTmpDispLab2"
DoCmd.OpenQuery "qupd_tblTmpDispLab1_Bal"
DoCmd.SetWarnings True
Else
'Append residual amount (less than 500ml)
'into tblTmpDispLab2
DoCmd.SetWarnings False
DoCmd.OpenQuery "qapp_tblTmpDispLab2a"
DoCmd.SetWarnings True
End If

rst.Delete
rst.MoveNext

Loop

 

[Malcy]

Thanks Mark
I tried that. It is happy to accept rst preceeding the dot on the commands but will not compile rst.SupQty saying member not found (or something like). As soon as I change back to Me.SupQty it compiles OK.
When I run it there is still the same problem where the first test record loops perfectly but the second doesn't.
Must be some other solution.
Thanks for the help though
All best wishes

Malcy

 

[Mile-O]

That's because recordset objects don't have a SupQty property, method, or event.

What you should try is: rst.Fields("SupQty")

 

[Malcy]

That worked a treat. Thanks a million.
What threw me was that after I put Me. in I got SupQty from the pull down list so I thought it was OK.
Clearly it wasn't.
This learning process is a long slow business but having had my first foray into ADO I am keen to explore further so will head onto the Amazon site and see what seems a suitable tutor!
Thanks again

Malcy