Iterating through a recordset created by a continuous form

[kewelch]

Hi, I have an issue with the following code working some of the time. I have a continuous bound form to the table tblTempIncoming, but the last entry on the form does not count toward the RecordCount of tblTempIncoming even though the last entry has been correctly stored to the table. This is the case when you don't click on the line following the last entry, but if you do click on the line following the last entry, the RecordCount of the table is correct. It is odd to me that the data can be stored correctly then "read" incorrectly at the same time. Any advice towards improvement on this functionality and/or solution to my problem will be appreciated.

    Set db = CurrentDb
    Set rsRec = db.OpenRecordset("tblTempIncoming")                     'the bound table
    Set rsInvUniq = db.OpenRecordset("tblUniqueFinishedInventory")  


    For i = 1 To rsRec.RecordCount
        Debug.Print "this recordset has reached record number ", i
        PartID = rsRec.Fields("TempPartID")
        Received = rsRec.Fields("TempQuantity")
        
        rsInvUniq.MoveFirst
        For j = 1 To rsInvUniq.RecordCount
            If PartID = rsInvUniq.Fields("PartID") Then
                rsInvUniq.Edit
                rsInvUniq.Fields("CurrentInventory") = rsInvUniq.Fields("CurrentInventory") + Received
                rsInvUniq.Update
            End If
            rsInvUniq.MoveNext
        Next j
        rsRec.MoveNext
    Next i

[Missinglinq]

It really sounds as if the last entered Record hasn't actually been saved, but as a workaround I'd try changing

For j = 1 To rsInvUniq.RecordCount

to

For j = 1 To rsInvUniq.RecordCount +1

Linq ;0)>

[kewelch]

I've tried variations of this suggestions, and they do not work. Running the for loop for an extra iteration always gives the run-time error "no current record" on the last go. Which is to be expected, since it runs one past what the RecordCount is. The peculiar thing is that tblTempIncoming can hold the same information but give two different record counts, and I'm entirely convinced that by clicking or not clicking on the row following the last entry (during data entry on the form) causes this confusion. For a specific example, I've done this data entry probably a dozen times over with the exact information.

(Continuous Form)
IncomingParts

Part # Quantity
PartA 1
PartB 2
PartC 3
PartD 4
|___ _

Regardless of the clicking situation or not, tblTempInventory will have all four of these parts with quantities correct. But the updated tblUniqueFinishedInventory will have Parts A,B and C correct when you do not make the following data row active and will have all four parts correct when you do. Incredibly odd.

[Missinglinq]

What do your Dim Statements look like for db, rsRec and rsInvUniq?

Linq ;0)>

[kewelch]

    Dim db As Database
    Dim rsRec As Recordset
    Dim rsInvUniq As Recordset

[John_G]

Hi -

As I recall, the .recordcount property of a recordset may not be accurate right after opening the recordset, and in order to get an accurate value you have to actually go to the last record.

Add these two lines before your For.. loop to see if that works:

rsRec.movelast
rsRec.movefirst

You might have to to the same thing for the rsInvUniq recordset.

Instead of using the For loop with the Recordcount, you could use a While loop and the .EOF recordset property:

While not rsRec.EOF
PartID = rsRec.Fields("TempPartID")
Received = rsRec.Fields("TempQuantity")

rsInvUniq.MoveFirst
While not rsInvUniq.EOF
If PartID = rsInvUniq.Fields("PartID") Then
rsInvUniq.Edit
rsInvUniq.Fields("CurrentInventory") = rsInvUniq.Fields("CurrentInventory") + Received
rsInvUniq.Update
End If
rsInvUniq.MoveNext
Wend
rsRec.MoveNext
Wend

This is a lot more efficient for large recordsets.

John

[kewelch]

Yes, those two lines of code corrected my issue, thanks John. Also, if while loops are more efficient, I have other places in my code where I can replace some nested For loops. In some cases I'm subtracting from the loop counter to continue the loop, but not progress a recordset through its table.

[John_G]

Sorry, I should have been a bit clearer.

The While...Wend loop is more efficient when going through recordsets because you don't have to move to the end and then back to the top to get the recordcount, which can take a while for large recordsets.

That doesn't mean that a While loop is always the best method - the For... loop can be just as good in many cases.

John