Auto-generate recently visited record-list

[AA Arens]

Hi,

I have a database with 2 main forms. Contacts and companies. I share
the base with two others via LAN. On the companies form I have buttons
to navigate throught the records
(>400). We are mostly handling let say 5 companies. Every time I have
to navigate or choose the find record button to get the right company.

I am looking fo a feature to have listed in a combo list the last 5
visited records ("recently visited records").

I realize that the records visited by the two other persons are also
listed and..., when I revisit a record, there might be two same records
in the list.

Anybody knows how to make that feature?

Bart

[Allen Browne]

Use the Current event of the form to add a record to a history table that
logs the records visited. You can then use this table joined to your real
table to limit and sort the RowSource of your combo.

The history table would need these fields:
LogID AutoNumber primary key
FKID Number records the primary key value of the visited
record.

In the Current event of your form, append a record. Either Execute an Append
query statement, or AddNew. It you want to limit it to the top 5, you will
also need to Execute a Delete query to remove the others. Then Requery the
combo so the newly added record shows up.

Use the AfterUpate event of the combo to move to the desired record, using
code like this:
http://allenbrowne.com/ser-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"AA Arens" <bartva...@gmail.com> wrote in message
news:1149728859....@i39g2000cwa.googlegroups.com...

[AA Arens]

Thank you for your help.

I sussesfully planted the record search combo box as discribed in your
link.
I got problems with you query solution, mainly because of lack of
knowledge.

I have two forms: contacts and companies
On the company form I added a combo list CompanyName ORDER BY Inserting
Date DESC. Inserting Date is one of the autofilled field autofilled
after I start to insert a new company.

>From this combo box I tried to appoach my idea to get the most recent
companies on top of the list, however not all companies recently added
("inserting date") are approached again after that.

But, on each form we also have a Notes-field where we add the
progression. Below this field there is a button on that form inserting
the present date on the note:

Private Sub InDate_Click()
If Len(Me.Notes & "") > 0 Then
Me.Notes = Me.Notes & vbCrLf & Format(Date, "dd-mm-yyyy") & "
------ "
End If
If Len(Me.Notes & "") = 0 Then
Me.Notes = Me.Notes & Format(Date, "dd-mm-yyyy") & " ------ "
End If
Notes.SetFocus
Notes.SelStart = Len(Notes.Value)
End Sub

Sample of Notes contents:
01-05-2006 ------ Send email with brochure
03-05-2006 ------ Management meeting planned

I made a history table as you wrote to me with LogID and FKID.
Now I would like to perform the following:

How to have added the CompanyID after I click on the InDate button.
Because inserting a date in the Notes field (to add new info) means
this record is recently updated and I want to have this added to the
history list.

I have tried with AddNew, RecordSet but it was complicated.

With the added combo box I then can then create a list of companies
from the history table who are recently approached.

Bart.

[Allen Browne]

You are inserting the date and message into a memo field?

To achieve what you want with any efficiency at all, you need to redesign
this table so that separate notes are added instead of appending to one
note.

The fields will be:
NoteID AutoNumber
CompanyID foreign key to Company.CompanyID
NoteDate date/time this note was added.
TheNote Memo the actual comment.
You will now have a subform where the notes are entered.

Regarding the first part of your question, I understand that your Company
table has a primary key (perhaps an Autonumber called CompanyID), and that
you are adding a record to your Log table recording this CompanyID in a
field name FkID (foreign key to Company.CompanyID.) After adding a record to
this, you want to reassign the RecordSource of your combo so that it shows
the last 5 companies you visited. Something like this:

strSql = "SELECT DISTINCT TOP 5 Company.CompanyID, Company.CompanyName
FROM Company INNDER JOIN Log ON Company.CompanyID = Log.FkID
ORDER BY Log.LogID DESC, Company.CompanyID;"

Me.cboHistory.RowSource = strSql

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"AA Arens" <bartva...@gmail.com> wrote in message

news:1149930231....@u72g2000cwu.googlegroups.com...

[AA Arens]

Basically, to do the modification step-by-step, I first would like to
know how to perform a add-record. Modifying the Notes table is
something I can do in the future, I am too afraid the database become
"unrecognizable" ;)

1.
I have:
Form Company with CompanyID as primary key and autonummered /
CompanyName / other company related info;
Table History with LogID and FkID

On Form Company I added a button and: What is the add a
companyID-command (from the active record) to have this ID added in the
record FkID from table History?

With the select command as part of the combo-box properties I can make
a list based on the companyID's added by the button I placed on Form
Company.

(The mentioned button also places a date in the Note memo field)

2.
I am aware the combo-box list become bigger and bigger. What is the
command to maximize the list to 5. I think the table has to be
re-ordered by the time to have the latest added CompanyID on top and
the bottom of the list limited to 5 records.

It's a simple solution and it's all not so efficient, but otherwise
it's all complicated as I am not an expert. Thank you.

Bart

[Allen Browne]

You will need to work through the details of how to implement this.

To create the new record in the log table, you need to execute an append
query statement. It will look like:
Dim strSql
strSql = "INSERT INTO ...
dbEngine(0)(0).Execute strSql, dbFailOnError

To get the SQL string, mock up a query using your specific table and some
literal values, and then switch it to SQL View (view menu in query design).

You can put that code into the click event of your button if that's when you
want it inserted.

You have already been given the details of the SQL statement to limit the
combo to 5 records.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"AA Arens" <bartva...@gmail.com> wrote in message

news:1149955013....@f6g2000cwb.googlegroups.com...

[AA Arens]

Hi Allen, I have tried a more easier way to get the recentlist, see
http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/41d5467606053634/2126e9222568b5df?lnk=st&q=aa+arens&rnum=1#2126e9222568b5df

Thanks for your help.