Go to record n
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Go to record n
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?
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Go to record n
If you want to go to a specific record on an open table, query or form, you can use something like:
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).
Code: Select all
DoCmd.GoToRecord acDataForm, "frmProducts", acGoTo, 10
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).
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Go to record n
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
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?
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Go to record n
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)
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Go to record n
What is the value of I when you receive the error message?
And what is UBound(Rs, 2) ?
And what is UBound(Rs, 2) ?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Go to record n
160
183
Wait I should have been 160 from I = 160 to Rs.RecordCount, but on checking
I = 0?
183
Wait I should have been 160 from I = 160 to Rs.RecordCount, but on checking
I = 0?
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Go to record n
Try changing
recData = Rs.GetRows(Rs.RecordCount)
Rs.Move 160 - 1
to
recData = Rs.GetRows(Rs.RecordCount)
Rs.MoveFirst
Rs.Move 160 - 1
recData = Rs.GetRows(Rs.RecordCount)
Rs.Move 160 - 1
to
recData = Rs.GetRows(Rs.RecordCount)
Rs.MoveFirst
Rs.Move 160 - 1
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Go to record n
No current record.
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 2StarLounger
- Posts: 120
- Joined: 22 Feb 2022, 09:04
Re: Go to record n
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. :(
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. :(
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: Go to record n
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.
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.
You do not have the required permissions to view the files attached to this post.
Last edited by bknight on 23 Feb 2024, 23:17, edited 1 time in total.
-
- 2StarLounger
- Posts: 120
- Joined: 22 Feb 2022, 09:04
Re: Go to record n
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.
How do you know you have 160 records? or rather 159 records?
Add some debug.prints to confirm what you *think* you have.
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.
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Go to record n
Already done Ubound is 182. Your eyes are way better than mine.
-
- 2StarLounger
- Posts: 120
- Joined: 22 Feb 2022, 09:04
Re: Go to record n
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 :-)
Notice that the capital I only shows in the code, not here typing :-)
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: Go to record n
Not necessary as there is no error.