Show last record in a form

[Carrie]

I have a table where I log data.

I also have a form that when opened must show the most
recent (=last) record in this table.

I can do that by
- have the "raw" table as record source and then moving to
the last record on open
- have the record source be sorted with the last record
first and then just open the form

OR

- I believe I can somehow make the table store records in
reversed order. BUT HOW? I have tried to set the "Order
By" property but without any success. I sort by "key DESC"
where key is my autonumber assigned to each new record.

How do I specify this on the table level?

 

[Wayne Morgan]

First, don't try to "sort" the table. The table is just a bucket that holds
the data. If you need to sort the data, use a query or the OrderBy property
of the form. You can sort Ascending or Descending, whichever you prefer. If
you sort Ascending, then to show the "last" record in the form as you open
the form you would need to issue a command to the form to move to the last
record. By "last record" you do mean the last one with data, not a New
Record, correct?

To have the form move to the last record, in the form's Load event try this
command:

DoCmd.GoToRecord acDataForm, Me.Name, acLast

 

[Carrie]

Thanks.
I just thought there were a way to force a certain sort
order on the data. Like in DB2 you can force a sort order
of the stored data. That makes sense if you have a not so
frequent data insert, and a very data heavy process that
has to read all data in a certain order. By having data
already sorted that way, you are much better off.

 

[David C. Holley]

The query for the form's recordSource can be as simple as SELECT * FROM
tblTransports ORDER BY lngTransportID DESC

I'm running a DB on a PPC where I need to always see the last record
entered and that query does the trick quite nicely.

 

[Wayne Morgan]

What you are speaking of sounds like an Index. If there is a field you sort
on frequently, set an Index on that field in the table design. When you sort
or search on that field in a query, the index will be used to speed up the
process.

 

[jcridge]

In my solution I wanted a form to periodically update to show the latest records being added to a system log table.
My solution may be considered crude, but it does ensure that the most recently added record is always visible and at the bottom of the form

In form design view
1) data>Order By> [my table date time field]
2) event>Timer Interval>[I chose an update interval of 500ms]
3) event>On Timer> [see code below]

Private Sub Form_Timer()
Me.Requery
With Me.Recordset
.MoveLast
End With
Me.Repaint
End Sub

Interested in can anyone advise if this is a reasonable solution