Solved

Add data to table field when record is modified

Posted on 2015-01-13
4
230 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 36

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

730 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