Look "ahead" in a record set
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Look "ahead" in a record set
I see it now, thanks.
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Look "ahead" in a record set
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
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Look "ahead" in a record set
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.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Look "ahead" in a record set
Similarly, if you want to look at record I-3, you should start the loop at I=3.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Look "ahead" in a record set
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.
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Look "ahead" in a record set
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.
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.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Look "ahead" in a record set
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
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Look "ahead" in a record set
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?
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?
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Look "ahead" in a record set
Correct. You cannot move to a different record within a Rs.Edit ... Rs.Update block. It would make no sense.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Look "ahead" in a record set
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.
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Look "ahead" in a record set
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.
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Look "ahead" in a record set
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.
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
-
- 2StarLounger
- Posts: 120
- Joined: 22 Feb 2022, 09:04
Re: Look "ahead" in a record set
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
2. Not seeing where you are using 3,i, but remember arrays start at 0
Using Access 2007/2019.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Look "ahead" in a record set
Table and for are sorted.
You can't see +recData(3,I)?
You can't see +recData(3,I)?
-
- 2StarLounger
- Posts: 120
- Joined: 22 Feb 2022, 09:04
Re: Look "ahead" in a record set
No, I copied your code into Notepad and then did a find. Not found.?You can't see +recData(3,I)?
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.
Using Access 2007/2019.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Look "ahead" in a record set
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
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Look "ahead" in a record set
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,
That might work on the ElseIf block, but the solution is below that block.
Code: Select all
If I< Rs,RecordCount
-
- 2StarLounger
- Posts: 120
- Joined: 22 Feb 2022, 09:04
Re: Look "ahead" in a record set
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
I would have thought?
Or process the loop until EOF.
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
Or process the loop until EOF.
Using Access 2007/2019.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Look "ahead" in a record set
It has to go through all of the if block to arrive at a solution(in this case the very last elseif).
-
- 2StarLounger
- Posts: 120
- Joined: 22 Feb 2022, 09:04
Re: Look "ahead" in a record set
How is that pertinent to having the correct loop count?
Using Access 2007/2019.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.