Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 773
  • Last Modified:

ADO rs.EOF failing in MS Access mdb do loop?

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

Loop

Exit Sub

Err_SubAdjustInventory:
    MsgBox Err.Description & " Error number " & Err.Number & " at " & Err.Source
    Exit Sub

End Sub


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 ...
0
zhenchyld
Asked:
zhenchyld
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:


Do While Not rs1.EOF
    QuantityOnHand = (QuantityOnHand - ShipQuantity)
    InventoryAdjusted = -1
   
rs1.movenext
Loop
0
 
zhenchyldAuthor Commented:
lol hold on
0
 
zhenchyldAuthor Commented:
yep, forgot to move the cursor.  that'll do it.

once again my good man you totally pwn.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now