Solved

Add data to table field when record is modified

Posted on 2015-01-13
4
227 Views
Last Modified: 2015-01-13
I have two field in a table...  LastModUser (short text field) and LastModDate (short date field).  When the record is either added or modified I want the LastModUser  field to be populated with the user's login and I want the LastModDate field to be populated with the current date (short date).

How can I do this?  I assume it will be in the beforeupdate event of the record?  Or?

--Steve
0
Comment
Question by:SteveL13
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
PatHartman earned 250 total points
ID: 40546719
The code goes into the BeforeUpdate event of the Form.
In my apps, the code is always:
    Me.ChangeBy = Forms!frmLogin!txtEmpID
    Me.ChangeDT = Now()

Open in new window

Because I keep the login form open (but hidden).  Where you get the LoginID depends on how the user logs in and what you do with the information he provides.

PS - the datetime data type is a double precision number with the integer being the number of days since 12/30/1899 and time being the time of day.  Formatting is a visual thing and is done when the date is displayed on a form or report.  I use Now() because I prefer to include time of day in my "timestamps" but the Date() function will just return the current date so you can use that if you prefer.  TOD is especially important in my current app because we have to extract updates and send them off to the State of Connecticut.  Our extract runs at 8 PM and selects anything that was updated after the last extract.  So if something is updated at 8:05 it doesn't get sent until the following day.
0
 

Author Comment

by:SteveL13
ID: 40546726
Pat,

I should have been more specific regarding the LastModUser field.  I meant the computer logic name.  There is no database login form.  How would I do that?
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 40546864
You can grab the computer's login name with this:

Environ("UserName")

Or use the API:

https://support.microsoft.com/kb/161394/en-us
0
 

Author Closing Comment

by:SteveL13
ID: 40546971
Both very helpful.  Thank you.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question