Looping Is Incorrect

[DMT Dave]

Hi Guy's this should be an easy fix, i am trying to add records from one list to another, one list is on the left side of screen, the other is on the right calling my record sets rsL and rsR

rsL has got the data and trying to add to rsR

If the rsL has got 19 records, it is adding 19 records to rsR but only with the first record 19 times instead of 19 different records

I think i may not have looped correctly ? any adjustment would be appreciated

Dim rsL As DAO.Recordset, rsR As DAO.Recordset, myRecs As Integer, i As IntegerDim myItem As String, myStart As Date, myEnd As Date


myItem = "Fuel"
myStart = Format(Me.txtStartDate, "mm/dd/yyyy")
myEnd = Format(Me.txtEndDate, "mm/dd/yyyy")


Set rsL = CurrentDb.OpenRecordset("Select * From tblExpenses WHERE ItemRequired = '" & myItem & "' And Date Between #" & myStart & "# And #" & myEnd & "#")
If Not (rsL.BOF And rsL.EOF) Then
rsL.MoveFirst
Do Until rsL.EOF
myRecs = rsL.RecordCount
rsL.MoveNext
Loop


If MsgBox("Before You Continue To Update Your PPL List (Right List)," & vbNewLine & vbNewLine & _
"Confirm The Correct Amount Of Fuel Receipts From Your Fuel Listing (Left List)" & vbNewLine & vbNewLine & _
vbTab & "Start Date: " & Format(Me.txtStartDate, "ddd-dd-mmm-yyyy") & vbNewLine & _
vbTab & "End Date: " & Format(Me.txtEndDate, "ddd-dd-mmm-yyyy") & vbNewLine & vbNewLine & _
"There Should Be: " & myRecs & " Fuel Receipts" & vbNewLine & vbNewLine & _
"Continue To Update Now ?", vbQuestion + vbYesNo, "CONFIRM RECEIPTS") = vbNo Then
DoCmd.CancelEvent
Else
Set rsR = CurrentDb.OpenRecordset("Select * From tblFuelPrice")
With rsR
For i = 1 To myRecs
rsL.MoveFirst
.AddNew
!Date = rsL.Fields("Date")
!Vehicle = rsL.Fields("Vehicle")
!Price = rsL.Fields("TotalAmount")
!Time = rsL.Fields("FuelTime")
!Station = rsL.Fields("Supplier")
!Litres = rsL.Fields("Litres")
!PricePL = rsL.Fields("Litres") / rsL.Fields("TotalAmount")
.Update
rsL.MoveNext
Next i
End With
End If
End If


Me.frmFuelPrices.Requery


Set rsR = Nothing
Set rsL = Nothing

[Welshgasman]

It is because you are Moving First each time within the loop.?

Why bother with a count?, just use EOF.

[Micron]

I would expect rsL.RecordCount to be 1 anyway because there's no MoveLast? Odd if that's not the case.

[Welshgasman]

I would expect rsL.RecordCount to be 1 anyway because there's no MoveLast? Odd if that's not the case.

No, just a complicated way to get the recordcount?

Do Until rsL.EOF
myRecs = rsL.RecordCount
rsL.MoveNext
Loop

[Micron]

The value of the RecordCount property equals the number of records that have actually been accessed. For example, when you first create a dynaset or snapshot, you have accessed (or visited) only one record. If you check the RecordCount property immediately after creating the dynaset or snapshot (assuming it has at least one record), the value is 1. To visit all the records, use the MoveLast method immediately after opening the Recordset, and then use MoveFirst to return to the first record. This is not done automatically because it may be slow, especially for large result sets.

https://docs.microsoft.com/en-us/off...-dao-recordset

[Welshgasman]

@Micron,

I have just tried his code.
On first entry to the EOF loop, the record count is 1.
As soon as I MoveNext, the record count is the number of records in the table, despite not moving to the end?

I also *thought* you needed to MoveLast to get the correct record count if the table/query was VERY large?

Sub GetRecCount()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim intRecCount As Integer

Set db = CurrentDb()
Set rst = db.OpenRecordset("Select * from Transactions")
If Not rst.EOF Then
    rst.MoveFirst
    Do Until rst.EOF
        intRecCount = rst.RecordCount
        Debug.Print intRecCount
        rst.MoveNext
    Loop
End If
MsgBox rst.RecordCount

Set rst = Nothing
Set db = Nothing

End Sub

Debug output whist stopped at the second MoveNext
1
280

I must admit, I expected the output from Debug to increment by 1 after each MoveNext? After first MoveNext ? 280 records in table.

[Micron]

Interesting. Seems that the documentation is not entirely accurate (not the first time I guess). Don't think I'd rely on what seems to be a quirk, believing that I've seen posts of code that wasn't quite right that worked for years - until Access was upgraded. All of a sudden the improper form no longer seems to work. If not in the habit of moving last, I'd expect some to resort to something like
For i = 1 to rs.RecordCount without moving last beforehand. I'd at least test a MoveLast over sample recordsets. These days it seems that pc power has made a lot of concerns about bogging down an obsolete concern. According to what you've shown and what the documentation says it would seem that you're moving last anyway (by default) so why not code it in and make things clearer/safer from a coding and performance point of view?

[Welshgasman]

I thought that if you had a reasonable small amount of record (no idea as to the limit for that), then the recordcount would show that.?
If it was a very large table, you had to to move last to get the real record count?. I thought the initial value was just as to how many records Access read on opening the file?

As I mentioned I expected my debug.print to increment 1 each print until EOF, so I was surprised that it showed 280 after just one MoveNext.

I have only had to work with small tables, so, would be curious in plus 2000 record tables result? purely for knowledge purposes.
If no one has such a table to hand , I might create one, but when I have time and the inclination.

I do not really use Access anymore, having retired, and when I did, I was like most people that ask for help here and on other sites, just using it to make life easier in work for everyone.

[DMT Dave]

Hi Guy's thank you all for your input, for some reason as you have mentioned i am not looping and/or moving records correctly

Would you kindly highlight the line that i am going wrong and replace with the correct method please ???

maybe one quick question, do i have the 2 recordsets incorrect ie, i am trying to update Recordset right rsR from recordset Left rsL

Much appreciated

Dim rsL As DAO.Recordset, rsR As DAO.Recordset, myRecs As Integer, i As IntegerDim myItem As String, myStart As Date, myEnd As Date




myItem = "Fuel"
myStart = Format(Me.txtStartDate, "mm/dd/yyyy")
myEnd = Format(Me.txtEndDate, "mm/dd/yyyy")




Set rsL = CurrentDb.OpenRecordset("Select * From tblExpenses WHERE ItemRequired = '" & myItem & "' And Date Between #" & myStart & "# And #" & myEnd & "#")
If Not (rsL.BOF And rsL.EOF) Then
rsL.MoveFirst
Do Until rsL.EOF
myRecs = rsL.RecordCount
rsL.MoveNext
Loop




If MsgBox("Before You Continue To Update Your PPL List (Right List)," & vbNewLine & vbNewLine & _
"Confirm The Correct Amount Of Fuel Receipts From Your Fuel Listing (Left List)" & vbNewLine & vbNewLine & _
vbTab & "Start Date: " & Format(Me.txtStartDate, "ddd-dd-mmm-yyyy") & vbNewLine & _
vbTab & "End Date: " & Format(Me.txtEndDate, "ddd-dd-mmm-yyyy") & vbNewLine & vbNewLine & _
"There Should Be: " & myRecs & " Fuel Receipts" & vbNewLine & vbNewLine & _
"Continue To Update Now ?", vbQuestion + vbYesNo, "CONFIRM RECEIPTS") = vbNo Then
DoCmd.CancelEvent
Else
Set rsR = CurrentDb.OpenRecordset("Select * From tblFuelPrice")
With rsR
For i = 1 To myRecs
.AddNew
!Date = rsL.Fields("Date")
!Vehicle = rsL.Fields("Vehicle")
!Price = rsL.Fields("TotalAmount")
!Time = rsL.Fields("FuelTime")
!Station = rsL.Fields("Supplier")
!Litres = rsL.Fields("Litres")
!PricePL = rsL.Fields("Litres") / rsL.Fields("TotalAmount")
.Update
rsL.MoveNext
Next i
End With
End If
End If


Me.frmFuelPrices.Requery




Set rsR = Nothing
Set rsL = Nothing

[DMT Dave]

Got it guy's, thank you i moved rsl.moveFirst to before loop as suggested, thank you so much

[Welshgasman]

You could use a query as well. much faster and a lot smaller to code?

[Micron]

If it was a very large table, you had to to move last to get the real record count?

Can't answer that or the other questions; can only interpret what I read

The value of the RecordCount property equals the number of records that have actually been accessed. For example, when you first create a dynaset or snapshot, you have accessed (or visited) only one record.

Seems straight forward but this thread doesn't seem to support it. I'm going to stick with what I think is best practice unless something dictates otherwise.

[Welshgasman]

Can't answer that or the other questions; can only interpret what I read

The value of the RecordCount property equals the number of records that have actually been accessed. For example, when you first create a dynaset or snapshot, you have accessed (or visited) only one record.

Seems straight forward but this thread doesn't seem to support it. I'm going to stick with what I think is best practice unless something dictates otherwise.

I am assuming, just assuming that when you read/move to the first record, Access brings in a block of data.?
If that block of data by chance holds all the records, like my 280 records, then you, just by chance, get the actual record count.?

If the table holds 5K records etc, then that is not likely going to give you the real record count?

[moke123]

I've always subscribed to Allen Brownes explanation:

4. RecordCount without MoveLast

For recordsets based on queries, SQL statements, and attached tables, the RecordCount property returns the number of records accessed so far. When you first OpenRecordset(), Access grabs the first record, and keeps processing your code while the others load. So, if you test RecordCount immediately after you OpenRecordset, you typically get 0 (if there are no records), or 1 (if there are any, regardless of how many will load.) This does not apply to recordsets of type dbOpenTable type (the default for local tables.)
Solution:

If you need to know the RecordCount, use the MoveLast method first. This forces Access to wait while all records load, so the RecordCount reflects the entire recordset.
Hint:

Don't MoveLast unless you really need to: this will be slow with a large recordset or a recordset drawn across a network. RecordCount will always be at least 1 if records exist, so there is no need to MoveLast if you only want to know if you have records to work with.

[Micron]

I've always subscribed to Allen Brownes explanation:

Me too, and it's not much different from what I copied from M$ documentation.