Recording time spent on a record

[johnb]

Hi All

I need to record the amount of time a user spends on any record displayed in
an Form (A2003). And if the record is revisited record that time too.

What is the best approach to this?

TIA
johnb

 

[Allen Browne]

Create a table to log the records. Typically it would have fields for the
form name, primary key value, user, and date/time the user started viewing
that record.

In the form's Current and Close events, write a record to the logging table.
You can then use a subquery to calculate the number of seconds between when
the user arrived at that record and when they left (either arriving at
another record or closing the form.)

To get the user name from Windows, see:
http://www.mvps.org/access/api/api0008.htm

To calculate the difference between records, see:
http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[johnb]

Allen

The Close Event triggers when the form closes but not when the user
navigates to the next/previous record. I've tried various Form Events but
without sucess. Which event fires when a user moves to another record on the
same Form?

johnb

 

[Keith Wilby]

Which event fires when a user moves to another record on the
same Form?

Current.

Keith.
www.keithwilby.co.uk

 

[johnb]

Keith

Cheers. So obvious. You get so close to the wood you loose sight of the trees!

johnb

 

[Allen Browne]

There is no event that fires when the user leaves a record.

In any case, you don't need to record both the time the user arrives at a
record and also the time they leave it. The time they leave *is* the time
they go to another record, unless they close the form (which I why you want
to log that too.)

So the logging table will have fields like this:
VisitID AutoNumber primary key
DocName Text (64) name of the form
UserName Text who visited the record.
VisitDateTime Date/Time when they visited the record
KeyValue Text primary key value of the record
visited
IsClosed YesNo False unless fired from Form_Close

KeyValue will be Null when they move to the new record, and Null when they
close the form. It will contain a number (if the primary key is a numeric
field), or text (if the primary key is a Text field), or a delimited string
(if the primary key consists of multiple fields.)

As explained, you can then use a subquery to get the VisitDateTime of the
next record for the same UserName + DocName. Filter the main query where
IsClosed = False.

 

[johnb]

Hi Allen

Thank you for the full explanation.

johnb