Look "ahead" in a record set

[bknight]

I see it now, thanks.

[bknight]

I'm running into a subscript out of range again, but this time it is below a lower bound. In my three cases I need to look back 2 rows. With the current row always I - 1, that makes the look back I - 3. Below the first couple of rows not an issue, however at the beginning of the routine(when compiling I guess) I'm getting an out of subscript error. Any suggestions?

Code: Select all

        If recData(7, I - 1) = "Exit" Then
            If recData(2, I) = -recData(2, I - 1) Then
                'Calculate case ent,x
                dblCurAmt = recData(15, I - 1) + recData(15, I)
                Fld6 = dblCurAmt
            ElseIf recData(2, I) + recData(2, I + 1) = -recData(2, I - 2) Then
                'Calculate case ent,x,x
                dblCurAmt = recData(15, I - 1) + recData(15, I) + recData(15, I + 1)
                Fld6 = dblCurAmt
            End If
        ElseIf (recData(7, I - 3) = "Entry" And recData(7, I - 2) = "Entry") Then
            If recData(2, I - 3) + recData(2, I - 2) = -(recData(2, I - 1) + recData(2, I)) Then
                'Calculate case ent,ent,x
                dblCurAmt = recData(15, I - 2) + recData(15, I - 1) + recData(15, I)
                Fld6 = dblCurAmt
            'else Calculate case ent, ent, x, x
            Else: dblCurAmt = recData(15, I - 3) + recData(15, I - 2) + recData(15, I - 1) + recData(15, I)
                Fld6 = dblCurAmt
            End If
        End If

[HansV]

If you want to look at record I-2, you should start the loop at I=2. If you start it at I=0 or I=1, I-2 will initially be negative, causing an error.

[HansV]

Similarly, if you want to look at record I-3, you should start the loop at I=3.

[bknight]

That presumes that 1 and 2 do not fit any case does it not? Looking at the data the start at 3 should be ok, luckily. There may another issue I may start at 3 but the recordset still starts at 1. To keep orderly there should be Rs.MoveNext twice prior to the for loop, correct? in all cases the routine could be started on the second record as the first record can never be an Exit, but the second could be.

[HansV]

recData starts at record 0, but you don't need to start looking at it with I = 0.
If you need to start at I = 1, for example, you could add a check
If I >=2 Then
if you want to look at record I - 2, and add a check
If I >=3 Then
if you want to look at record I - 3.

Similarly, to avoid problems at the end of the recordset/array, add a check
If I <= UBound(RecData, 2) - 1 Then
if you want to look at record I + 1
etc.

[bknight]

Fixed with a different logic scheme, I hope, and different code.

Code: Select all

Rs.MoveFirst
Rs.MoveNext
Rs.MoveNext
For I = 3 To Rs.RecordCount
dblPrevAmt = 0
Rs.Edit
        If recData(7, I - 2) = "Exit" And recData(7, I - 3) = "Entry" Then
                'Calculate case ent,x
                dblCurAmt = recData(15, I - 1) + recData(15, I - 2)
                Fld6 = dblCurAmt
        ElseIf recData(7, I - 1) = "Exit" And recData(7, I) = "Exit" And recData(7, I - 2) = "Entry" Then
            'If recData(2, I) = -recData(2, I - 1) Then
                If recData(2, I - 1) + recData(2, I) = -recData(2, I - 2) Then
                'Calculate case ent,x,x
                dblCurAmt = recData(15, I - 1) + recData(15, I) + recData(15, I - 2)
                Fld6 = dblCurAmt
                End If
        ElseIf (recData(7, I - 3) = "Entry" And recData(7, I - 2) = "Entry") Then
            If recData(2, I - 3) + recData(2, I - 2) = -(recData(2, I - 1) + recData(2, I)) Then
                'Calculate case ent,ent,x
                dblCurAmt = recData(15, I - 3) + recData(15, I - 2) + recData(15, I - 1)
                Fld6 = dblCurAmt
            'else Calculate case ent, ent, x, x
            Else: dblCurAmt = recData(15, I - 3) + recData(15, I - 2) + recData(15, I - 1) + recData(15, I)
                Fld6 = dblCurAmt
            End If
        End If
Rs.Update
Rs.MoveNext
Next I
Set Rs = Nothing
Set db = Nothing
End Sub

[bknight]

This has been the most difficult logic puzzle in my entire foray into coding. I have run into another "snag". I have an Rs.Edit that resides before an If block. Now in one of the choices I have a rs.MoveNext followed by an update to a field followed by a Rs.MovePrevious, then out of the if block to a Rs.Update and I get an error message, Update without ad new or edit.
Does the Rs.edit get negelected when the pointer moves? If so I guess I need a Rs.Edit immediately prior to the update, but will that be negated by a move to previous record?

[HansV]

Correct. You cannot move to a different record within a Rs.Edit ... Rs.Update block. It would make no sense.

[bknight]

In essence I added an Rs'Edit and Rs.Update around each filed update, remove the first Rs.Edit and the last Rs.Update befor and after the if block. Seems odd that I have 4 pairs of them now in the if block, but if that is how it has to be, it is done. I haven't tested yet although the alterations are in the code now.

[bknight]

Well I screwed up today by having more than one instruments open at the same time. The scheme that I have coded won't work across multiple instruments entering at different times and exiting at different times. This will require records back more than 3 records back. I need to think about this new situation.

[bknight]

A few days of redoing logic and then recoding. Then about7 hours today trying to debug the code only to keep encountering a skip of records because of an import sort order(?)
Question 1: I'm importing from a csv converted to excel spreadsheet. If the records are not sorted correctly oldest to youngest when imported, does access sort them automatically or must the table be shut down and reopened?
Question 2: The code works well until the last record when the code has recData(3, I and that is subscript out of range. The recData is within an if statement that must be evaluated (and found false in this case) before going to the next if statement. Is there an error trapping way to "skip" evaluating the if statement and jumping to the next if statement when it encounters an out of subscript error?
Partial code.

Code: Select all

Rs.MoveFirst
recData = Rs.GetRows(Rs.RecordCount)
Rs.MoveFirst
Rs.MoveNext
strPrevCon = Fld2
Rs.MoveNext
For I = 3 To Rs.RecordCount
'If I = 3 Then
strCurCon = Fld2
'End If
dblCurAmt = 0
    If recData(1, I - 1) = strPrevCon Then
        If recData(8, I - 1) = "Exit" And recData(9, I - 1) = "-" Then
                If recData(8, I - 2) = "Exit" And recData(8, I - 3) = "Entry" And recData(8, I - 3 - 1) = "Entry" And (recData(3, I - 1) + recData(3, I - 2) + recData(3, I - 3) + recData(3, I - 3 - 1)) = 0 Then
                    'Calculate case ent, ent, x, x
                    dblCurAmt = recData(16, I - 1) + recData(16, I - 2) + recData(16, I - 3) + recData(16, I - 3 - 1)
                    'Rs.MoveNext
                    Rs.Edit
                    Fld6 = dblCurAmt
                    Rs.Update
                    'Rs.MovePrevious
                ElseIf recData(8, I - 2) = "Entry" And recData(8, I - 3) = "Entry" And (recData(3, I - 3) + recData(3, I - 2) + recData(3, I - 1)) = 0 Then
                    'Calculate case ent, ent, x
                    dblCurAmt = recData(16, I - 3) + recData(16, I - 2) + recData(16, I - 1)
                    'Rs.MoveNext
                    Rs.Edit
                    Fld6 = dblCurAmt
                    Rs.Update
                    'Rs.MovePrevious
                ElseIf recData(8, I - 2) = "Exit" And recData(8, I - 3) = "Entry" And recData(8, I + 1) = "Entry" And (recData(3, I - 2) + recData(3, I - 1) + recData(3, I)) = 0 Then ''' Fails here
                    'Calculate case ent, x, x
                    dblCurAmt = recData(16, I - 1) + recData(16, I - 2) + recData(16, I - 3)
                    'Rs.MoveNext
                    Rs.Edit
                    Fld6 = dblCurAmt
                    Rs.Update
                    'Rs.MovePrevious
                ElseIf recData(8, I - 2) = "Entry" And (recData(3, I - 2) + recData(3, I - 1)) = 0 Then
                    'else Calculate case ent, x
                    dblCurAmt = recData(16, I - 2) + recData(16, I - 1)
                    'Rs.MoveNext
                    Rs.Edit
                    Fld6 = dblCurAmt
                    Rs.Update
                    'Rs.MovePrevious
                End If
        End If
    End If
    strPrevCon = Fld2
Rs.MoveNext
Next I

[Gasman]

1. How is Access meant to know how you want it sorted?
2. Not seeing where you are using 3,i, but remember arrays start at 0

[bknight]

Table and for are sorted.
You can't see +recData(3,I)?

[Gasman]

You can't see +recData(3,I)?

No, I copied your code into Notepad and then did a find. Not found.?

Regardless, as the array starts at 0 and the record pointer at 1, you are always going to be 1 too many in the array, unless you take that into account.

[bknight]

You can't see +recData(3,I)?

No, I copied your code into Notepad and then did a find. Not found.?

Regardless, as the array starts at 0 and the record pointer at 1, you are always going to be 1 too many in the array, unless you take that into account.

I believe you did the copy wrong because it is there, if MS had line numbers I would give you the line, but what I can do is to publish it
ElseIf recData(8, I - 2) = "Exit" And recData(8, I - 3) = "Entry" And recData(8, I + 1) = "Entry" And (recData(3, I - 2) + recData(3, I - 1) + recData(3, I)) = 0 Then

[bknight]

now let's get off that issue and get back to solve the problem. When I = Rs.RcordCount which it will at the last record, what would be the code to check this I don't remember from 20 years ago. Is it RS.EOF or something of that nature? I guess a trap might be,

Code: Select all

 If I< Rs,RecordCount

That might work on the ElseIf block, but the solution is below that block.

[Gasman]

It is not rs which is the issue, if you have 121 records, then you can go to record 121. Trying to move further than that will give you EOF, so you can test for that. You do not appear to be moving past last record though, just trying to use an subscript value which is out of range, because of the different starting values.
As already mentioned several times, by Hans as well, arrays start at 0.

So you really should be using

Code: Select all

For I = 2 To Rs.RecordCount -1

I would have thought?

Or process the loop until EOF.

[bknight]

It has to go through all of the if block to arrive at a solution(in this case the very last elseif).

[Gasman]

It has to go through all of the if block to arrive at a solution(in this case the very last elseif).

How is that pertinent to having the correct loop count?