Solved

Add data to table field when record is modified

Posted on 2015-01-13
4
231 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 37

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 85

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

729 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