Form: Scrolling through records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form for recording information on a daily basis with the date being
one of the records stored in a table. On the bottom of the form is the
records scroller. In the past I've been able to scroll through my records in
order, but now it is not in order. To correct, I opened the table and sorted
ascending but when I go back to the form it stilled scrolls non-sequentially.
 
For the form's RecordSource, instead of the table itself, use a sorted query
which orders the rows by date, e.g.

SELECT *
FROM YourTable
ORDER BY YourDate;

or if you want the latest dates first:

SELECT *
FROM YourTable
ORDER BY YourDate DESC;

If you do have the latest dates first (at the top of a continuous form)
you'll still enter new rows at the bottom. If you want a new row to
immediately move to the top (and any rows where you change the date to
immediately move to their correct position) then put the following code in
the form's AfterUpdate event procedure:

Me.Requery

This will requery the form and move the record pointer back to the top of
the form again.

Ken Sheridan
Stafford, England
 
If my data is stored sequentially in the table shouldn't it appear on my form
sequentially?
 
No, tables are sets and as such have no intrinsic order. Unlike in file
systems the concept of sequential storage is quite foreign to the relational
database model. While records (and fields) in a file could be identified by
position that is not the case with a table. When you sort the datasheet view
of a table you are simply arranging how you see the rows, not changing them
physically. The best way to impose a perceived order on a set of rows is
with a query. The exception to this is with a report, however, as reports
use there own internal sort mechanism which you set in the sorting and
grouping dialogue in report design view. An ORDER BY clause in a report's
underlying RecordSource will be ignored by the report and only slow things
down.

Ken Sheridan
Stafford, England
 
Back
Top