date/time stamp on user exit

K

Kim

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!
 
J

John W. Vinson

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!
Click to expand...

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.
 
K

Kim

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.
 
J

John W. Vinson

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.
Click to expand...

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).
 
K

Kim

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?
 
J

John W. Vinson

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?
Click to expand...

Exactly.
 
K

Kim

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

John W. Vinson

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

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!
 
Top