How do I get the AbsolutePosition of a specific record?

[agc1976]

Here's what I do:

1. The user enters the Primary Key of a record and clicks on a "search" button.

2. I search for the details of that record using a recordset that will only return that specific record (using the WHERE clause in the SELECT statement).

I need to know the AbsolutePosition (in the table) of the record returned by the recordset. I want to avoid any type of sequential searches because it takes way too much time on a table with a lot of records...

Can I do that?
Thanks...

 

[Tuan3249]

There is a .AbsolutePosition that you can use but before you get the absolute position, you will still have to do a sequential search. After that, you can store that absolute position somewhere and access it again any other time after that.

Hope that helps.

 

[petermeachem]

hang on a mo, if you are searching by primary key, why would there be any sort of sequential search. It ought to get it straight away. Thats how these things work. Shouldn't matter a scrap how many records you have. Unless it's Access of course. Peter Meachem
peter@accuflight.com

 

[agc1976]

Well, what I want to be able to do is:

1. Search for a record (the way I explained earlier)
2. Keep the position of that record in a variable. This will allow me to move to the next or previous record by adding or subtracting 1 to the AbsolutePosition I just captured.

I am not using the data control or any type of bound controls... just a basic recordset that I create with 1 record when the user wants to search for it. I close it once it's done searching but I need to know on what position that record was.

 

[stoolpigeon]

If your cursor supports bookmarks that would do exactly what you want. Like this

dim varBkmrk as Variant

objRec.MoveFirst
varBkmrk = objRec.Bookmark

Now move around, add, delete whatever you want. When you want to go back to where you were:

objRec.Bookmark=varBkmrk

You can also use the bookmark as your starting point for a move. Say you want to move 3 from the bookmark record

objRec.Move 3, varBkmrk

 

[argusy]

Hold the fortress!!
Doing your search is OK
storing the absolute position is OK
Adding or subtracting 1 is NOT ok, if you think that the next or previous ABSOLUTE record is the next or previous IN SEQUENCE.
The very nature of access is to store records in whatever way it can, and the absolute position holds no meaning at all for any indexing or even the basic natural sequence when adding records (Access will jumble them all up to gain any advantage in reducing the size of a database)

Try it. set up a program that stores the absolute position.
step through it and look at the absolute position.

THEN add a record. You'd be lucky if the absolute position stays the same.
Same for 'adding' or 'subtracting' 1 from the absolute position - you will probably find the data is not the same as before, if any records are added (or deleted).

Play safe and use bookmarks

Argusy

 

[MichaelRed]

In addition to the above, YOU state that you " ... recordset that I create with 1 record ... ".

Doing an add/subtract to the record identifier will rather obviously place you at BOF/EOF any way, NOT at ANY record position.

IMHO, YOU need to review what you are doing, why you are doing it, why you are doing it "THAT WAY", fall back severl yards and do the old proverbial PUNT.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over