Create history list for text entry

J

Justin

I'm trying to create a drop-down history list for a search field in a form.
When a user makes an entry in this field, I want a list of the prior 10
'searches' that have been made in this field. It does not have to be the
prior 10 entries from only that user, but all users.


Example,

An individual enters LVA10845 as a search. The user can type a new search
in the field, or hit the down arrow (like in a combo box) and see the prior
10 searches performed.

Does anyone know how I can go about doing this?

Thanks,
Justin
 
D

Dirk Goldgar

Justin said:
I'm trying to create a drop-down history list for a search field in a
form. When a user makes an entry in this field, I want a list of the
prior 10 'searches' that have been made in this field. It does not
have to be the prior 10 entries from only that user, but all users.


Example,

An individual enters LVA10845 as a search. The user can type a new
search in the field, or hit the down arrow (like in a combo box) and
see the prior 10 searches performed.

Does anyone know how I can go about doing this?

Thanks,
Justin
Click to expand...

You'd need a SearchHistory table, in which you'd store the search key
and date/time performed. So it would have fields SearchKey (text) and
LastSearched (date/time). In the AfterUpdate event of the combo box,
you'd perform three actions:

1. Insert a new record in the SearchHistory table, if this key doesn't
exist, or update the record for this key with the current date/time
(from the Now() function. Something like

With CurrentDb
.Execute _
"UPDATE SearchHistory SET LastSearched = Now() " & _
"WHERE SearchKey = '" & Me.SearchKey & "'", _
dbFailOnError
If .RecordsAffected = 0 Then
.Execute _
"INSERT INTO SearchHistory (SearchKey, LastSearched) " &
_
"VALUES ('" & Me.SearchKey & "' Now())", _
dbFailOnError
End If
End With

2. Delete all but the top 10 records from the table, as ordered by
LastSearched descending. SQL would be something like this:

DELETE FROM SearchHistory
WHERE SearchKey Not In
(SELECT TOP 10 SearchKey FROM SearchHistory
ORDER BY LastSearched DESC)

3. Requery the combo box. The combo box, of course, would be based on a
query like this:

SELECT SearchKey FROM SearchHistory
ORDER BY LastSearched DESC

and would have its LimitToList property set to No.
 
Top