ADO rs.EOF failing in MS Access mdb do loop?
Posted on 2007-08-07
Im working on the CBF in an Ms Access mdb (as im sure everyone here knows by now as this is about my fifth question). Right now i'm writing a public sub that will check all of the line items in my tblOrders table and adjust inventory quantities as needed in my tblPartNumbers table. Here is what i have so far:
Public Sub subAdjustInventory()
On Error GoTo Err_SubAdjustInventory
'Adjusts inventory where tblOrders.InventoryAdjusted is false
Dim cnn As New ADODB.Connection
Set cnn = CurrentProject.AccessConnection
Dim rs1 As New ADODB.Recordset
Dim strSQLrs1 As String
'strSQLrs1 = "SELECT * FROM tblOrders"
strSQLrs1 = "SELECT tblPartNumbers.PartID, tblOrders.ShipQuantity, tblOrders.InventoryAdjusted, tblPartNumbers.QuantityOnHand FROM tblOrders INNER JOIN tblPartNumbers "
strSQLrs1 = strSQLrs1 & "ON tblOrders.PartID=tblPartNumbers.PartID WHERE tblOrders.InventoryAdjusted=0"
rs1.Open strSQLrs1, cnn, adOpenForwardOnly, adLockPessimistic
'displays the number of order line items you are about to adjust inventory for
MsgBox "You are about to adjust inventory for " & rs1.RecordCount & " line items"
' now, for each record, set QuantityOnHand=(QuantityOnHand-ShipQuantity) and InventoryAdjusted = true
Do While Not rs1.EOF
QuantityOnHand = (QuantityOnHand - ShipQuantity)
InventoryAdjusted = -1
MsgBox Err.Description & " Error number " & Err.Number & " at " & Err.Source
Everything works great, the MsgBox displays the correct number of records that should be adjusted (which is currently only 2 for testing purposes), but when execution hits 'Do While Not rs1.EOF' it gets stuck in an infinite loop, like EOF just isnt registering... I'm pretty (super) inexperienced with ADO, but after this and a faulty record count i kept getting when using rs.RecordCount in another subroutine i'm thinking maybe i should just learn DAO instead becuase i'm working solely with Jet ...