By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,061 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

How do i create a "last updated on" field in Microsoft Access?

P: 5
I just started working with Microsoft Access so I dont know much and Im trying to create a field that updates itself after changes have been made to the data in a table. I would like one that says who did it and at what time it was done.

Assume that I dont know anything. I have tried following this discussion (http://www.thescripts.com/forum/thread635040.html) but I cant get it to work, their explanations are very vague and I dont know much.
May 7 '07 #1
Share this Question
Share on Google+
9 Replies


JConsulting
Expert 100+
P: 603
I just started working with Microsoft Access so I dont know much and Im trying to create a field that updates itself after changes have been made to the data in a table. I would like one that says who did it and at what time it was done.

Assume that I dont know anything. I have tried following this discussion (http://www.thescripts.com/forum/thread635040.html) but I cant get it to work, their explanations are very vague and I dont know much.
The "What time was it done" part is easy. Create a date/time field in your table. et it's default value as (copy and paste this)
=Date()
The user name uses a built in function of Access. So create a text field in your table and set it's default value to (copy and paste this)
= Environ$("username")

That should do it
J
May 7 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
As far as triggering the storage of user and date/time when a record is changed you can use the dirty property. If you need to store this information historically you will need to create a table to log these events and include the name of the table or query that was updated.

If you have a save button on your form you can put this there otherwise just put it in the forms On Change and On Close event.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Close()
  2.    If Me.Dirty Then ' unsaved changes have been made to the record
  3.       ' Change the user and date/time information
  4.    End If
  5. End Sub
  6.  
Mary
May 7 '07 #3

P: 5
thanks for the help but Im still havign problems getting it the way i wanted it.

1. =date() does automatically set a date on the field but only for new data. so if i want to go back and change, the date doesnt change it remains exactly the same.

2. = When i try to use Environ$("username") in the default value for last updated by, i cant save the table. I get an error message indicating the function is unknown in default value.

what can i do about this two issues?
and I want those fields to be updated everytime a change is made, there is no need to keep track of it
May 7 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Try something like this ...
Expand|Select|Wrap|Line Numbers
  1. If Me.Dirty Then ' unsaved changes have been made to the record
  2.      Me![Date]=Date()
  3.      Me!User=Environ$("username")
  4.    End If
  5.  

Mary
May 7 '07 #5

JConsulting
Expert 100+
P: 603
thanks for the help but Im still havign problems getting it the way i wanted it.

1. =date() does automatically set a date on the field but only for new data. so if i want to go back and change, the date doesnt change it remains exactly the same.

2. = When i try to use Environ$("username") in the default value for last updated by, i cant save the table. I get an error message indicating the function is unknown in default value.

what can i do about this two issues?
and I want those fields to be updated everytime a change is made, there is no need to keep track of it
you can also create a small function to use anytime you need it

Function FindUserName() As String
FindUserName = Environ$("username")
End Function

Use it with Mary's Bit.
J
May 7 '07 #6

ADezii
Expert 5K+
P: 8,619
I just started working with Microsoft Access so I dont know much and Im trying to create a field that updates itself after changes have been made to the data in a table. I would like one that says who did it and at what time it was done.

Assume that I dont know anything. I have tried following this discussion (http://www.thescripts.com/forum/thread635040.html) but I cant get it to work, their explanations are very vague and I dont know much.
If you want to keep a little closer control over who made changes, to what, and at which time, and keep the process transparent and external to the Database, you can simply create a Log File which as much information as you require and bury it somewhere where it will not be obvious. If you are interested, follow these steps:
  1. Create a Public Sub-Routine which will actually write the entries to the Log File.
    Expand|Select|Wrap|Line Numbers
    1. Public Sub LogToHistoryFile(strObjectContext As String, strRecordSource As String)
    2. Const PATH_TO_LOG_FILE = "C:\Windows\System32\ODBCLog.txt"
    3.  
    4. Open PATH_TO_LOG_FILE For Append As #1
    5.  
    6. Print #1, CurrentUser() & " modified " & strRecordSource & " via " & _
    7.           strObjectContext & " on " & Format$(Now(), "dddd, mmm dd, yyyy @ hh:mm AM/PM")
    8.  
    9. Close #1
    10. End Sub
  2. Call the Sub-Routine from the Dirty Event of any Object you so desire, passing the Object Name and its underlying Record Source as Arguments.
  3. In the following example, I am writing to the Log File from the Dirty() Event of frmEmployee which has a RecordSource of tblEmployee where the data was actually modified.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_Dirty(Cancel As Integer)
    2.   Call LogToHistoryFile("frmEmployee", "tblEmployee")
    3. End Sub
  4. I buried the Log File in the C:\Windows\System32 Folder and called it ODBCLog.txt to mask its true meaning.
    Expand|Select|Wrap|Line Numbers
    1. Const PATH_TO_LOG_FILE = "C:\Windows\System32\ODBCLog.txt"
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Admin modified tblEmployee via frmEmployee on Monday, May 07, 2007 @ 11:36 AM
May 7 '07 #7

Denburt
Expert 100+
P: 1,356
The "What time was it done" part is easy. Create a date/time field in your table. et it's default value as (copy and paste this)
=Date()
The user name uses a built in function of Access. So create a text field in your table and set it's default value to (copy and paste this)
= Environ$("username")

That should do it
J
FYI, replace the function above with Now() and not Date() that will pick up the time and day the record was changed.

I have used all of the methods mentioned and I particularly like Adezii's solution for security purposes, I store it in it's own folder for logs and it's locked down.

Also if your database has it's own security and if that is how you want to retrieve this info.

You can check the database user like so.

application.CurrentUser


you can check windows or a Domain log in like this:
Environ$("UserName")
May 7 '07 #8

P: 5
Thanks everyone
I appreciate all the code that you guys are giving me but you are forgeting one thing, I mentioned that my knowledge is limited so I have no freaking idea where to put it all !!!!!

Can someone explain step by step on how to get the date updated? For instance, go to the form, open in design view, right click properties on the field, look for xxxxxxx and yyyyy ........copy zzzzzzzz.. thanks
May 7 '07 #9

Denburt
Expert 100+
P: 1,356
go to the form, open in design view, right click properties on the field, look for
You will want to have the form properties available (little black square on the top left, highlighted by default when you first go into design view), then look for

Form_Dirty

in this field change it to read [Event Procedure] then click the little button on the right.

Add the function ADezii posted for the Form_Dirty event in this section then before this Form_Dirty section you can paste the rest of his code and you should be good to go.
May 9 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.