How to make a record appear as the top record on the screen?

[ND Pard]

I have a small program that appends data to a table sorted by date.

Prior to appending, I store the record count to a variable via a recorset's
recordcount property.

At the end of the program, I open the sorted table and go to the record with
today's date, ie. the first record I added today with the following
statements:

DoCmd.OpenTable "Adj_Mstr", acViewNormal, acEdit
DoCmd.GoToRecord acTable, "Adj_Mstr", acGoTo, intRecCount

This takes me to the desired record.

How can I make that record the top record on the screen using VBA?

Right now it is generally the last or near the bottom of the screen. I do
not want to set a filter as I often scroll up too.

Your help will be appreciated. Thank you.

 

[Dorian]

Your code is not reliable because there is no guarantee that the recordcount
record will be the one you want.
In a relational database, the sequence of records is random. There is no
guarantee that an added record will be the last one in physical storage.
If you really need the sort to be in the order you added them, you'll need
to add a column for the date/time and sort on that column.
And if you want 'todays records' at the top with the first added at the top,
you'll need an additional column for 'todays records' so you can sort
ascending but not get yesterday's records above. This means that when you
start out with todays records you will need to turn off all the 'todays
records' indicator for yesterdays records.
Seems like a complex arrangement, is there not a better design?

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[ND Pard]

Sorry Dorian, but I have absolutely no problem finding the record, and,
because it is sorted, my methodology is and has been reliable for many years
of use.

Right now, the selected record may or may not be visible on the screen. If
it is not visible, by touching the arrow up or down button once, the record
immediately comes into view ... but not at the top of the screen. If it is
visible, it most often is the last or second to the last record on the screen.


My only question is how, when I goto the record, can I make that record
appear at the top of the screen?

 

[JimBurke via AccessMonster.com]

If you're viewing the table via a form in datasheet mode, and that table is
sorted by that field in descending order (you can set the table's sort
property by going into the table and sorting by the desired field and order,
then saving that change when you exit), then you should be able to create an
After insert event proc where you just say Me.Requery. You can also set the
table's sort order via VBA, but I've never done that. Not sure if I'm
interpreting what you're doing correctly.

 

[David C. Holley]

Dorian's point is very valid. What if another user deletes a record or
inserts a record? Just because you haven't run into any problems with your
approach [does not] mean that problems could arise due to the design. The
Space Shuttle Columbia disaster is an excellent example of how design issues
may not result in an immediate failure.

It is common practice within Access development to rely on sorting as the
only guaranteed means by which a records can be retrieved in any specific
order. Adding a Date/Time Created field is a valid approach and then sorting
on it. You can even do sub sorts where by the records are sorted by Month
Added followed by Date added.

 

[ND Pard]

David,

Again, my database is sorted. I do have a date/time field and an ascending,
unique ID field in my table. It is because it is sorted on these fields that
I know I can trust my methodology and why it has worked for years.

All I want to know is: How can I make the table's selected record the top
record on the screen using VBA and without setting a filter?

Thanks.

 

[John W. Vinson]

David,

Again, my database is sorted. I do have a date/time field and an ascending,
unique ID field in my table. It is because it is sorted on these fields that
I know I can trust my methodology and why it has worked for years.

You've been lucky.

Access will present records in whatever order it finds convenient. The order
of records displayed on a table datasheet might be the same as that displayed
on a form, in fact it probably usually will; but there is NO GUARANTEE. If you
want to be sure that records are sorted in some particular order (a selected
record at the top) then you must - no option, no choice - base the form on a
Query specifying that order.

All I want to know is: How can I make the table's selected record the top
record on the screen using VBA and without setting a filter?

Without more information in the table to set the order, I can't imagine any
way to do so. Access doesn't distinguish "the selected" record from any other
record.

 

[RD]

David,

Again, my database is sorted. I do have a date/time field and an ascending,
unique ID field in my table. It is because it is sorted on these fields that
I know I can trust my methodology and why it has worked for years.

All I want to know is: How can I make the table's selected record the top
record on the screen using VBA and without setting a filter?

Thanks.

I think you and the others are working at cross purposes, here.

You want the table to act like an in-page hyperlink, as in a webpage,
where when you click on the link the browser navigates to that
location and positions it at the top of the page.

Your sorting works fine but the short answer to your question, "How to
make a record appear as the top record on the screen?" in an Access
table is: You can't. That's just the way Access works.

I suppose you could add a sort order field to the table and then
dynamically re-assign the order in a form. Seems like a lot of work to
just have the convenience of having the searched for record appear at
the top.

Is there a business reason for this or just a preference?

RD

 

[ND Pard]

Thanks RD.

Your answer is appreciated.

All of the other responses said I need a sort order ... WHICH I HAVE AND
USE!!!

I think if I use a FORM instead of the TABLE, Allen Browne has a procedure
that will accomplish what I'm trying to do.

Anyway, thanks for the response.