date/time stamp on user exit

I have a database that logs when a form (RPA) comes into our office. The RPA
will go through a few different hands and will have different information
entered into the database from each person. How do I get a log of when
information is entered and by who?

I am a beginner so a detailed explanation would be helpful. Thank you!

On Wed, 27 Feb 2008 14:26:00 -0800, Kim wrote:

I have a database that logs when a form (RPA) comes into our office. The RPA
will go through a few different hands and will have different information
entered into the database from each person. How do I get a log of when
information is entered and by who?

I am a beginner so a detailed explanation would be helpful. Thank you!

You can put VBA code in the BeforeUpdate event of the Form you're using to
enter the data. If you're not using a form, you're out of luck - tables have
no usable events.

Let's say you have a Date/Time field named DateLogged and a text field
LoggedBy. Be sure that these fields are selected in the form's recordsource
query. Open the Form in design view and view its Properties; on the Events tab
select the Before Update event. Click the ... icon by it and choose "Code
Builder". Enter the code like

Private Sub Form_BeforeUpdate(Cancel as Integer)
Me!DateLogged = Date ' use Now if you want the date and time logged
Me!LoggedBy = CurrentUser ' if you have Access security and each user logs on
End Sub

If you don't have Access security enabled you'll need a bit more code.
--
John W. Vinson [MVP]

Sounds like something I can handle. My database is split...do I do this in
the frontend since that is where the form is located? I thought I wasn't
supposed to make any changes to the frontend.

"John W. Vinson" wrote:

On Wed, 27 Feb 2008 14:26:00 -0800, Kim wrote:

I have a database that logs when a form (RPA) comes into our office. The RPA
will go through a few different hands and will have different information
entered into the database from each person. How do I get a log of when
information is entered and by who?

I am a beginner so a detailed explanation would be helpful. Thank you!

You can put VBA code in the BeforeUpdate event of the Form you're using to
enter the data. If you're not using a form, you're out of luck - tables have
no usable events.

Let's say you have a Date/Time field named DateLogged and a text field
LoggedBy. Be sure that these fields are selected in the form's recordsource
query. Open the Form in design view and view its Properties; on the Events tab
select the Before Update event. Click the ... icon by it and choose "Code
Builder". Enter the code like

Private Sub Form_BeforeUpdate(Cancel as Integer)
Me!DateLogged = Date ' use Now if you want the date and time logged
Me!LoggedBy = CurrentUser ' if you have Access security and each user logs on
End Sub

If you don't have Access security enabled you'll need a bit more code.
--
John W. Vinson [MVP]

On Thu, 28 Feb 2008 08:04:01 -0800, Kim wrote:

Sounds like something I can handle. My database is split...do I do this in
the frontend since that is where the form is located? I thought I wasn't
supposed to make any changes to the frontend.

Is it *your own* .mdb frontend? or a .mde frontend that somebody provides for
you?

Yes, the code can exist only in the frontend (the backend just contains
tables); if you have just one copy of a frontend that other people are using
too, you'll need to make a new frontend and distribute it. It's actually far
more common to make such program enhancements in a frontend than it is to make
design changes in the backend (once the table structures are set, which should
come pretty early in the development process).
--
John W. Vinson [MVP]

It's a .mdb frontend. But now I need to add the fields for the date/time and
logged by in the tables in the backend, correct? Then put the code in the
frontend?

"John W. Vinson" wrote:

On Thu, 28 Feb 2008 08:04:01 -0800, Kim wrote:

My database is split...do I do this in the frontend since that is where the form is located?

Is it *your own* .mdb frontend? or a .mde frontend that somebody provides for
you?

Yes, the code can exist only in the frontend (the backend just contains
tables); if you have just one copy of a frontend that other people are using
too, you'll need to make a new frontend and distribute it. It's actually far
more common to make such program enhancements in a frontend than it is to make
design changes in the backend (once the table structures are set, which should
come pretty early in the development process).
--
John W. Vinson [MVP]

On Thu, 28 Feb 2008 09:29:00 -0800, Kim wrote:

It's a .mdb frontend. But now I need to add the fields for the date/time and
logged by in the tables in the backend, correct? Then put the code in the
frontend?

Exactly.
--
John W. Vinson [MVP]

Is the information going to replace itself each time a new person logs in? If
so, that's not what I want.

"John W. Vinson" wrote:

On Wed, 27 Feb 2008 14:26:00 -0800, Kim wrote:

I have a database that logs when a form (RPA) comes into our office. The RPA
will go through a few different hands and will have different information
entered into the database from each person. How do I get a log of when
information is entered and by who?

I am a beginner so a detailed explanation would be helpful. Thank you!

You can put VBA code in the BeforeUpdate event of the Form you're using to
enter the data. If you're not using a form, you're out of luck - tables have
no usable events.

Let's say you have a Date/Time field named DateLogged and a text field
LoggedBy. Be sure that these fields are selected in the form's recordsource
query. Open the Form in design view and view its Properties; on the Events tab
select the Before Update event. Click the ... icon by it and choose "Code
Builder". Enter the code like

Private Sub Form_BeforeUpdate(Cancel as Integer)
Me!DateLogged = Date ' use Now if you want the date and time logged
Me!LoggedBy = CurrentUser ' if you have Access security and each user logs on
End Sub

If you don't have Access security enabled you'll need a bit more code.
--
John W. Vinson [MVP]

On Fri, 29 Feb 2008 09:53:10 -0800, Kim wrote:

Is the information going to replace itself each time a new person logs in? If
so, that's not what I want.

ok... I misinterpreted, sorry. You're right, the code as stated will do just
that - timestamp only the last entry.

You'll need to create a new table with whatever fields you want to track
(timestamp, userID, what else...?); in your BeforeUpdate code you'll need to
open a Recordset on this table, use the AddNew method, and add the data. It's
not quite clear to me what it is you want logged - you say here "logs in"
which is different from someone updating a record in a table!
--
John W. Vinson [MVP]