Look "ahead" in a record set

[Gasman]

You really should have looked at and read that link I posted? :(

Then if you walk your code and test you would get

Code: Select all

Sub BKnight()
Dim rs As Recordset
Dim arrTest() As Variant
Dim db As DAO.Database

Set db = CurrentDb


Set rs = db.OpenRecordset("Select * from testtransactions")
rs.MoveLast
Debug.Print "Recordset count is " & rs.RecordCount
rs.MoveFirst
arrTest = rs.GetRows(rs.RecordCount)
Debug.Print "Array count is " & UBound(arrTest, 2) & "  Rows And "; UBound(arrTest, 1) & " Columns "
Set rs = Nothing
Set db = Nothing
End Sub

with output of

Code: Select all

Recordset count is 321

? ubound(arrtest,2)
 320 
Array count is 320  Rows And 8 Columns 

[HansV]

I had already posted that link in the first reply in this thread...

[bknight]

I used that link but that doesn't tell me how to load a full set of rows.

[bknight]

You really should have looked at and read that link I posted? :(

Then if you walk your code and test you would get

Code: Select all

Sub BKnight()
Dim rs As Recordset
Dim arrTest() As Variant
Dim db As DAO.Database

Set db = CurrentDb


Set rs = db.OpenRecordset("Select * from testtransactions")
rs.MoveLast
Debug.Print "Recordset count is " & rs.RecordCount
rs.MoveFirst
arrTest = rs.GetRows(rs.RecordCount)
Debug.Print "Array count is " & UBound(arrTest, 2) & "  Rows And "; UBound(arrTest, 1) & " Columns "
Set rs = Nothing
Set db = Nothing
End Sub

with output of

Code: Select all

Recordset count is 321

? ubound(arrtest,2)
 320 
Array count is 320  Rows And 8 Columns 

I'm curious why the first rs? rs.GetRows(rs.RecordCount) and the second. Why is not a number inside the brackets not sufficient, or just RecordCount?

[bknight]

Code: Select all

Sub CalcProfit(Profit)
'This Function Will Calculate The Profit
Dim db As DAO.Database
Dim Rs As DAO.Recordset
Dim Fld1 As Field, Fld2 As Field, Fld3 As Field
Dim Fld4 As Field, Fld5 As Field, Fld6 As Field
Dim Fld7 As Field, Fld11 As Field, Fld12 As Field
Dim I As Long, intCurQty As Integer, intnextQty As Integer, intbackRec As Integer
Dim dblPrevAmt As Double, dblCurAmt As Double, dblQuantity As Double
Dim strCurSymbol As String, strCurCon As String
Dim recData As Variant
Set db = CurrentDb
Set Rs = db.OpenRecordset("Select * From NinjaTrader2024 Order By Time")
Set Fld1 = Rs!Time
Set Fld2 = Rs!Instrument
Set Fld3 = Rs!Quantity
Set Fld4 = Rs!Price
Set Fld5 = Rs!Amount
Set Fld6 = Rs!Profit
Set Fld7 = Rs!Action
Set fld8 = Rs!Commission
Set Fld9 = Rs!Ent_Ex
Rs.MoveFirst
Rs.MoveLast
Rs.MoveFirst
numrec = Rs.RecordCount
Debug.Print (numrec)
dblPrevAmt = 0
'recData(Field,Record) both zero based
'recData = Rs.GetRows(RecordCount, 16)Produces invalid property assignment
recData = Rs.GetRows(Rs.RecordCount)
numrows = UBound(recData) + 1
Debug.Print (numrows)

produces numrec=121;numrows=18
Not the entire recordset! Now what am I doing incorrectly?

[HansV]

As gasman tried to tell you, you should use

numrows = UBound(recData, 2) + 1

UBound(recData) + 1 is equivalent to UBound(recData, 1) + 1; it returns the number of fields

[bknight]

I guess I misread the Ubound statement, as I read it to display the number of rows. Yes there are 18 fields, that makes sense. Using numrows = UBound(recData, 2) + 1 produces 121 rows.
Whatever I did incorrectly, as it is difficult to remember the iterations I used, now
recData(7, I - 1), recData(7, I) and, recData(7, I + 1) all produce values instead of subscript out of range.
Thanks to both for the explanation.

[bknight]

Please explain this.
Two code snippets the first runs the second fails. Both are identical with the exception of recData has been commented out of the first.

Code: Select all

Rs.MoveFirst
Rs.MoveLast
Rs.MoveFirst
dblPrevAmt = 0
'recData(Field,Record) both zero based
'recData = Rs.GetRows(Rs.RecordCount)
For I = 1 To Rs.RecordCount
Rs.Edit

Code: Select all

Rs.MoveFirst
Rs.MoveLast
Rs.MoveFirst
dblPrevAmt = 0
'recData(Field,Record) both zero based
recData = Rs.GetRows(Rs.RecordCount)
For I = 1 To Rs.RecordCount
Rs.Edit

[SpeakEasy]

GetRows moves the record pointer. Given you have instructed getrows to return ALL the records, that means the record pointer is at EOF. Hence you'll get a 'No current record' error with rs.Edit

[HansV]

So insert

rs.MoveFirst

before rs.Edit

[bknight]

So insert

rs.MoveFirst

before rs.Edit

Yes that works, but it does not answer why it fails, which is a question I forgot to ask in my previous post.

[HansV]

See the reply by SpeakEasy.

[Gasman]

I had already posted that link in the first reply in this thread...

Oops, sorry Hans. Still, perhaps worth reiterating to read it fully again?

[Gasman]

I guess I misread the Ubound statement, as I read it to display the number of rows. Yes there are 18 fields, that makes sense. Using numrows = UBound(recData, 2) + 1 produces 121 rows.
Whatever I did incorrectly, as it is difficult to remember the iterations I used, now
recData(7, I - 1), recData(7, I) and, recData(7, I + 1) all produce values instead of subscript out of range.
Thanks to both for the explanation.

Yes, on a single dimension array, that is all you would use, which TBH is the most common usage, at least that I have seen.

[HansV]

Oops, sorry Hans. Still, perhaps worth reiterating to read it fully again?

No need to apologize! It wasn't directed at you, but at bknight.

[bknight]

See the reply by SpeakEasy.

I penned a reply to speakeasy, but failed to post it(got distracted)
Yes that works but it doesn't answer a question that I didn't ask in the previous post.
That question would be why? Now the question would be why the Rs.MoveFirst needs to be added again, we haven't done any Rs operations since the last Rs.MoveFirst just added a GetRows statement.

[HansV]

As speakeasy mentioned, Rs.GetRows moves the record pointer by the number of records that it returns. Since Rs.GetRows(Rs.RecordCount) returns ALL records, it moves the record pointer of Rs to the end, after the last record. So using Rs.GetRows(Rs.RecordCount) effectively executes Rs.MoveLast too.

[Gasman]

See the reply by SpeakEasy.

I penned a reply to speakeasy, but failed to post it(got distracted)
Yes that works but it doesn't answer a question that I didn't ask in the previous post.
That question would be why? Now the question would be why the Rs.MoveFirst needs to be added again, we haven't done any Rs operations since the last Rs.MoveFirst just added a GetRows statement.

From the link posted

The GetRows method will return fewer than the desired number of rows in two cases: either if EOF has been reached, or if GetRows tried to retrieve a record that was deleted by another user.

I must admit, I did not think about the record pointer being moved, as I just tested what was being retrieved, but if you think about it, it has to to do that to retrieve each records data.? To get to EOF it must be moving the record pointer?

[bknight]

You must be right but no recorset operations were performed in accessing all the records, just doesn't seem logical.

[HansV]

From the link that both Gasman and I posted:

After you call GetRows, the current record is positioned at the next unread row. That is, GetRows has the same effect on the current record as Move numrows.