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

Posted on 2007-08-07
Last Modified: 2013-12-25
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


Exit Sub

    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 ...
Question by:zhenchyld
    LVL 119

    Accepted Solution


    Do While Not rs1.EOF
        QuantityOnHand = (QuantityOnHand - ShipQuantity)
        InventoryAdjusted = -1

    Author Comment

    lol hold on

    Author Comment

    yep, forgot to move the cursor.  that'll do it.

    once again my good man you totally pwn.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now