populating combo box with last 5 entries

[lgw002]

Nubie question ( first real database)

I'm trying to populate a combo box with the last few entries of a record. I've been looking on SQL Queries and can see a way of using SELECT TOP on the recordset but querying/sorting the whole record every time seems a very clumsey way of doing it (especialy if the recordset gets large).
So is there a more elegant way of getting lastrecord,lastrecord-1,lastrecord-2, etc into my combobox ?.

Note as a suplemental question is there a way of a subform showing records from last to first instead of what seems to be normaly first to last?

Thanks for any help.

 

[BryanT]

Hello lgw002

Second question first. To read the records in reverse order use the "Sort Decending" clause on the appropriate field(s) in your query.

To display the items selected in the combo box you I would probably just set the RowSource of the combo box to the query name and run the requery event (of the combobox) any time I needed to update it.

The other alternative may be to load the results of the query into a recordset and then populate the combobox from there. This would mean that the records were available to manipulate/reload for as long as the recordset object was still in memory without having to rerun the query.

I hope this is some help to you.

 

[lgw002]

Thanks BryanT. As to reverse order I found it within seconds after that. Slight case of Doh! ( smack forehead ), however as an Access nubie trying to learn and pull together Access, VBA and SQL all at once I'm gonna forgive myself that one.

As to the 'last few entries', I see what you are saying and I think that’s where I was heading with the SELECT TOP query (unless I've really got the wrong end of the stick with what you are explaining)

My problem is that I used to do some programming ( back when Fortran and Pascal were all the rage - please don’t count the years) and the idea of the Query statement parsing every record in the table just to identify the last few items rebels against my concept of tight coding, definatly not good for hundreds/thousands records.

I'm thinking of setting up a separate table ( which the combobox would use as list source) populated with the last few items which I would update every time a new record is generated on a stacked FIFO (first in first out) basis.

However, whilst this would be tighter code, this still seems a complicated way of going around a possibly simple process.

Is there not some way of explicitly referencing and obtaining the values of "lastrecord', 'lastrecord - 1', 'lastrecord - 2', etc

Anybody got any ideas?

 

[Sergeant]

Don't know if it's of any use to you, but here's what I did for one project:
In the form's OnCurrent event, I added the PK field and Name to a list box using the AddItem method. This way, the user could quickly access recently accessed records. The list starts out blank at the start of each session, though. If you wanted the values to stay there for the next session, you'd have to save changes to the form itself. You could put it in the form's BeforeInsert event so it would only log most recent additions.

    strRecentEntry = Nz(Me.PersID, " ") & "; " & Nz(Me.FullName, " ")
    Me.lstRecent.AddItem strRecentEntry
    If Me.lstRecent.ListCount > 5 Then
        Me.lstRecent.RemoveItem (0)
    End If

 

[lgw002]

Thanks Sergeant. Thats given me a good pointer to the AddItem method, I might go down that route later on. However I'm still trying to investigate any way of explicitly referencing to the last few items. I'm having a look at the DLast function on the tableID primary key, getting the number, and then getting the actual values by using DLookup on the last ID, 2nd to last ID, 3rd to last ID etc

Now I hav'nt started using/coding with these functions at all yet ( still at the reading Manual, thinking hard and eating Asprin stage ) so can anyone give an opinion if this is the right way to be heading or if there are any better functions/proceedures/methods to investigate

Thanks