Look "ahead" in a record set

[bknight]

I'm running a looping sub on a record set. Suppose we are in record n, I'm wondering if it is possible to look ahead in n+1 and/or n+2 or n+3 records for field values without performing a rs.movenext? If this is possible, how would I code it?

[HansV]

No, you'd need MoveNext.
An alternative could be to read the entire recordset into an array using its GetRows method.
You can then loop through the records using a variable of type Long as loop index, say i. To refer to the next record, use i + 1 etc.

[bknight]

That adds a whole new coding that I unfamiliar with If I set a recordset to a table with identical records(sorted similarly) I would be searching for a record that contains the word Entry or Exit if the word of I+1 is Entry ontinue the loop if not then eit the loop and rs.movenext to seek the same word Once Exit is found exit the loop and movenext until the record number is/was the same as in the loop. Start a new loop again. Always searching for Entry/Exit

1 start loop if Entry is in field, a flag is imitated with value of a different field can be positive or
2 if field is Exit exit loop if flag =0, if not continue loop
3 if field is Exit exit loop if flag =0, if not continue loop
4 if field is Exit exit loop if flag =0, if not continue loop, in this speific case the word Exit will be found.

[HansV]

Sorry, I don't get that.

[bknight]

They are the only possibilities in this specific case. The way that the developers write code is they assume you know what are referring. I don't understand getRows either. Showing what I have, what I need.

[snb]

MS introduced .xlsx in 2007.

[bknight]

Yes I have 2007 installed on other machines. But I seldom use xlsx I have too any files that are xls.

[Gasman]

They are the only possibilities in this specific case. The way that the developers write code is they assume you know what are referring. I don't understand getRows either. Showing what I have, what I need.

https://learn.microsoft.com/en-us/offic ... method-dao

[bknight]

Ok, before spending an inordinate amount of time debugging. I have.

Code: Select all

Function 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
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
dblPrevAmt = 0
'recData(Field,Record) both zero based
recdata = Rs.GetRows
For I = 1 To Rs.RecordCount
Rs.Edit
If Fld7 = "Buy" Then
    If Left([Fld2], 2) = "NQ" Then
        Fld5 = -Fld3 * Fld4 * 20 + fld8
    ElseIf Left([Fld2], 2) = "ZB" Then
        Fld5 = -Fld3 * Fld4 * 1000 + fld8
    End If
ElseIf fl7 = Sell Then
      If Left([Fld2], 2) = "NQ" Then
        Fld5 = Fld3 * Fld4 * 20 + fld8
    ElseIf Left([Fld2], 2) = "ZB" Then
        Fld5 = Fld3 * Fld4 * 1000 + fld8
    End If
End If
'End If
    'if recdata(10,I-1)="Entry" then
    'Go Into Calculation Block
        'if recdata(7,I)="Exit" then
            'if recdata(2,I) = -recdata(2,I-1) then
                'Calculate case ent,x
            'elseif recdata(2,I)+recdata(2,I+1) = -recdata(2,I-1) then
                'Calculate case ent,x,x
            'end if
        'elseif (recdata(7,I)="Entry" and recdata(7,I+1))="Exit" then
            'if recdata(2,I)+recdata(2,I-1)=recdata(4,I+1) then
                'Calculate case ent, ent, x
            'else Calculate case ent, ent, x, x
            'end if
        'end if
    'end if
Rs.Update
Rs.MoveNext
Next I
Set Rs = Nothing
Set db = Nothing
End Function

On a table in image,
Am I on the correct path?

[Gasman]

Just having a quick look at that link, I would say NO!
An array has no concept of fields, just items in the array.
That link states what is identfied as a field and what is identified as a record. You need to use that synatx for your requirements.

I do not even see RecData dimmed?

[bknight]

avarRecords(intField, intRecord)

To get the first field value in the second row returned, use code like the following:

field1 = avarRecords(0,1)

To get the second field value in the first row, use code like the following:

field2 = avarRecords(1,0)

Aside from no Dim it seems to me that the example deals with fields in records.

[Gasman]

So does it work as expected?
After the initial set of your fld(x), they do not appear to be reset?
TBH from this post

1 start loop if Entry is in field, a flag is imitated with value of a different field can be positive or
2 if field is Exit exit loop if flag =0, if not continue loop
3 if field is Exit exit loop if flag =0, if not continue loop
4 if field is Exit exit loop if flag =0, if not continue loop, in this speific case the word Exit will be found.

I am also not sure what you are trying to achieve

[bknight]

Matching entries and exits with quantities summing to 0, then add amounts of all those records 2, 3 or 4, see the spreadsheet, updating a Profit field to that value.

[bknight]

So does it work as expected?
After the initial set of your fld(x), they do not appear to be reset?
TBH from this post

1 start loop if Entry is in field, a flag is imitated with value of a different field can be positive or
2 if field is Exit exit loop if flag =0, if not continue loop
3 if field is Exit exit loop if flag =0, if not continue loop
4 if field is Exit exit loop if flag =0, if not continue loop, in this speific case the word Exit will be found.

I am also not sure what you are trying to achieve

That post was a thought process with no coding that came later, all I'm wondering before attempting to run the code is the syntax correct for the lookup, nit the logic.

[bknight]

Well
If recData(7, I - 1) = "Entry" Then-- works but
If recData(2, I) = -recData(2, I - 1) Then-- both have subscript out of range
If recData is a "copy" of Rs then I have no clue how to fix it or proceed.

[Gasman]

Arrays start at 0

[bknight]

Incorrect typing the negative sign should be positive.

Code: Select all

If recData(2, I) = -recData(2, I + 1) Then

both have subscript out of range
The sub started at record 1 and I'm sure that is why

Code: Select all

If recData(7, I - 1) = "Entry" Then

works. But records 2 and 3 are out of subscript out of range. BTW

Code: Select all

recData(2, I-1)

produces a correct value.

[Gasman]

Walk through your code and use the immediate window to find out how many records are in the array.
Even how many records you have in the recordset.
You also need to keep track of what I is

[bknight]

What code would you use to find the records in the array. I know the records in the record set.

[bknight]

After some research, I find:
Rs.RecordCount=121
recData= Rs.GetRows=18 rows
recData=Rs.GetRows(121) has 18 rows

So now to use the GetRows() method someone needs to explain how to load the entire recordset, Barring that I must abandon GetRows and regroup for another tedious solution.