Go to record n

[bknight]

What is the best code to go to record n. I looked and GoTo doesn't exist except as a sub/function. I looked at seek, but that says the table must have an index which I don't have. Anything else?

[HansV]

If you want to go to a specific record on an open table, query or form, you can use something like:

Code: Select all

DoCmd.GoToRecord acDataForm, "frmProducts", acGoTo, 10

See DoCmd.GoToRecord method (Access).

For a DAO recordset, you can set its AbsolutePosition property to n - 1 (because it is zero-based): Rs.AbsolutePosition = n - 1. The recordset must have been opened with dbOpenDynaset or dbOpenSnapshot.
See Recordset.AbsolutePosition property (DAO).

Alternatively, you can use the Move method. Use MoveFirst to move to the first record, then use Rs.Move n - 1 to move to the n-th record.
See Recordset.Move method (DAO).

[bknight]

Code: Select all

Dim db As DAO.Database
Dim Rs As DAO.Recordset
Set Rs = db.OpenRecordset("Select * From NinjaTrader2024 Order By Time")
Rs.MoveFirst
Rs.MoveLast
Rs.MoveFirst
recData = Rs.GetRows(Rs.RecordCount)
Rs.Move 160 - 1

Partial code that applies to db,and rs.
Field values are for record 160 which is what I wanted. Now I need to add For I = 160 to Rs.RecordCount, thereby not calculating the first 159 records.
All good until the code attempts
If recData(8, I - 1) = "Exit" Then and I promptly get a "subscript out of range"(there are 183 records). Why/ and how to fix the subscript out of range?

[HansV]

Do you want to manipulate recData or Rs itself?

[bknight]

Manipulate the Rs, got to a specific record, and use items from recData(16,I-3)+recData(16,I-2)+recData(16,I-1)+RecData(16,I)

[HansV]

What is the value of I when you receive the error message?
And what is UBound(Rs, 2) ?

[bknight]

160
183
Wait I should have been 160 from I = 160 to Rs.RecordCount, but on checking
I = 0?

[HansV]

Try changing

recData = Rs.GetRows(Rs.RecordCount)
Rs.Move 160 - 1

to

recData = Rs.GetRows(Rs.RecordCount)
Rs.MoveFirst
Rs.Move 160 - 1

[bknight]

No current record.

[HansV]

I'm out of ideas.

[Gasman]

FOR I = I60 to Rs.recordCount?

Your supposedly 160 is capital I and 60? :(

So as I60 does not exist??

Why are you NOT using Option Explicit at the top of EVERY module?
That would stop silly errors like that. :(

[bknight]

Ok, I changed to
Rs.MoveFirst
recData = Rs.GetRows(Rs.RecordCount)
Rs.MoveFirst
Rs.Move 160 - 1 Checked and as Gasman suspected it was I instead of 1
However, no current record

ETA: I reset everything and what I have executes the command If recData(8, I - 1) = "Exit" Then executes without error. Sorry that I thought I did that earlier but I guess I didn't.

[Gasman]

I did not suspect, I could see it clear as day. Typing it back to you was the hard part. :-)
How do you know you have 160 records? or rather 159 records?

Add some debug.prints to confirm what you *think* you have.

[HansV]

FOR I = I60 to Rs.recordCount?

Your supposedly 160 is capital I and 60? :(

So as I60 does not exist??

Why are you NOT using Option Explicit at the top of EVERY module?
That would stop silly errors like that. :(

Good catch!

[bknight]

I did not suspect, I could see it clear as day. Typing it back to you was the hard part. :-)
How do you know you have 160 records? or rather 159 records?

Add some debug.prints to confirm what you *think* you have.

Already done Ubound is 182. Your eyes are way better than mine.

[Gasman]

Think you would have to upload the db or enough to see the problem?
Notice that the capital I only shows in the code, not here typing :-)

[bknight]

Not necessary as there is no error.