date when record is modified

[RossW]

Hi,
I have a field that inserts the current date when a record is added to a table. I would like to be able to audit when records are changed - i.e. update the date field. When I update a record, I would like to have the date field updated to reflect the current date.

any suggestions??

thanks

Ross

 

[Fuga]

maybe if you put this as an after_update event?

Me.yourdatefield = date()

 

[RossW]

Thanks for the rapid response! I should have specified that the updates are applied directly to the table. Does your suggestion work in a form only, or can I do this in a table? If so, do I do this in the build function?

Ross

 

[Fuga]

I´m not sure how to do this if not on a form.

sorry.

Fuga.

 

[ghudson]

You can not do what you want if the data is edited directly in the table. You are also allowing the users the option to alter the data in way that you might not expect. It is a general rule that the users should not have direct access to the database objects, tables, queries, etc. If you used a form to access the data, you could limit who, what, where, when, how your users access the data. When I need to track edits, I store the users NT id with a date and time stamp for each record. This allows the users to know who was the last person to edit the record.

Below is an example of how I do this...

My table has a field named "ModifiedBy". The form has a text box named "tbModifiedBy". In the BeforeUpdate event of the form, I use this code...

Me.tbModifiedBy.Value = GetNTUser & " " & Date & " " & Time

Copy the below function into a new module...

Private Declare Function GetUserName Lib "AdvAPI32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function GetNTUser() As String
Dim strUserName As String
strUserName = String(100, Chr$(0))
GetUserName strUserName, 100
strUserName = Left$(strUserName, InStr(strUserName, Chr$(0)) - 1)
GetNTUser = StrConv(strUserName, vbProperCase)
End Function

HTH

 

[Cosmos75]

RossW,
You said

I have a field that inserts the current date when a record is added to a table.

How do you do that? Set the default value to Date()? (Maybe I just answered my own question!)

 

[RossW]

Hi,
Go to design mode for your table. Select your 'date' field and in default value (on the general tab) enter date()

 

[Datahunter]

Hi everyone!

This is my first post here so my apologies if I haven't followed forum netiquette <g>.

Anyways, I have also been trying to resolve the 'update date field' issue for a bit and the solution presented here seemed to be just the ticket.

But. The field still doesn't update when I change record information. And now when I try to manually change the date, I get an error message about 'Access can't find the macro Date ()'.

Any information and help would be appreciated. Thanks in advance!

Sandra

 

[Datahunter]

Hello Pat!

I've tried your suggestion and I'm still getting the error message, only now it's 'Microsoft Access can't find the macro Me.'.

To top things off, now I can't get out of the record back into the design view! Everytime I try the error message pops up.

Sigh. Where have I gone wrong?

Sandra,
just supposed to be a data-enterer

 

[ghudson]

RossW, I believe you have merged different suggestions above into one.

You will not be able to update the date when the record is modified directly in the table.

If you really want to know when or who updated a record, you will have to use an event from a form.

If you only care about when a new record was added, then key "Date()" (without the quotes) as the Default Value for that field in the design view of the table.

HTH

 

[Datahunter]

For the information of any non-programmer people who stumble on this thread, I finally found the solution to the problem (after having to talk to Microsoft).

While the me.nameofyourfield = date() is correct, that information isn't entered into the form properties exactly. It involved going through those three little dots next to BeforeUpdate and selecting code builder. At the cursor you type in 'me.' and select the name of your field from the drop down menu, then space = space Date(). Close the VB editor window, save the form and Bob's your uncle <g>.

Sandra

 

[OldDoug]

Thank you all for your help. I am a recent inductee to this forum so please be patient. I am also, not (technically) a programer, so even more patience is appreciated.
I found a link for the coding needed to create a log (or tracking field) of changes made to the active table in form view. These instructions untilized a memo field within the table. While this is a great addition to the database, I need a searchable field of the date a change occured, in order to create a weekly report of data updates.
I followed Sandra's instructions as to entering a date for a record change and it works, however I seem to be stuck in a loop with the Update Log previously mentioned. I tried to place the "me." command within the Update log Module and of course got "improper use of me command" errors.
Any suggestions or help is truly appreciated. I have attached my Update Log coding if this helps.
thank you again,
Doug