Save UserName & Date-time of When Updated to a form table.

[PaulClough]

Many systems need to record who & when created a record and updated a record as an audit trail. But access seems to make this simple need hard to accomplish. I am using ACCESS 2007

I have researched this but seem to be missing something elemental.
I have found code for getting the user name from the system and can get it to run in a field on the form. But I can't get the data to save to the table.
This is actually a subform but I need to accomplish this also for the main form.

I can put =Now() in the Data control source of a text box and get the date & time, but how do also put in the Data control source the table.form.field.name,ie.[ctCurExpTranx].[ctWhenUpdated] or
[ctCurExpTranx].[ctWhoUpdated] so that the system knows to put the user name or date-time into these fields so they can get saved to the underlying table.

I also realize that somehow I need to do this update only if some field(s) on the form is actually changed and not just viewed.

I also have two similar fields for when the record is created and I would like to use the Default field to have this data automatically updated.

Since VBA for access seems rather complicated still, I am tring to use other methods than long VBA programs to accomplish such a simple task. For this simple application this simple function is fine because of low security and users barely know simple formulas. I just want to know who may need some help due to data entry problems & who has forgotten to make their updates.

Public Function GetUserName() As String
GetUserName = Environ("UserName")
End Function

I put this in a module and put =GetUserName() in the data control source, but again how does this data get to my table.

I look at event procedures & macro events but AGAIN, I must be missing something elemental. The user name and date-time are not in the table when I check it, even though they display on the form screen.

The solutions I have seen have all left out the elemental point I am missing. I can type data into a form field & it saves to the table fine, but If I use a function or something to get the data & don't know how to make the system think I typed it in so it will save the data to the field.

Your help is appreciated,
Paul

 

[G37Sam]

I wouldn't blame it on Access just yet

When exactly do you want the timestamp to be made? You can use the before insert Event for new records, or the Before update event for updated records.

 

[BecoNorte]

Using a form you can add your GetUserName function when saving the record:
txt_UserName = GetUserName()
DoCmd.RunCommand acCmdSaveRecord

Marco

 

[PaulClough]

Thank You G37Sam, I found the Before Update Event for the whole subform. But what exactly to I put in the event Sub??
[tCurExpTranx].[ctWhenUpdated] = Now() or some Form![reference] or what?

![field]
There are so many ways to reference fields, what is the correct way here.
The Now() date-time format is fine for now. I just need the value saved to the table.
Thank you, Paul

 

[boblarson]

Using a form you can add your GetUserName function when saving the record:
txt_UserName = GetUserName()
DoCmd.RunCommand acCmdSaveRecord

Marco

Actually, the form's BEFORE UPDATE event would be the place to use it.

Private Sub Form_BeforeUpdate(Cancel As Integer)
   Me.txt_UserName = GetUserName()
   Me.txt_SaveDateTime = Now
End Sub

That is good for both new and changed records because it will only save the date/time when the record is actually committed.

So what I sometimes do is to have 4 fields

OrigSaved - Date
OrigSavedBy - Text
LastModified - Date
LastModifiedBy - Text

And then the code in the Before Update is like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If Me.NewRecord Then
      Me!OrigSaved = Now
      Me!OrigSavedBy = GetUserName
   Else
      Me!LastModified = Now
      Me!LastModifiedBy = GetUserName
   End If   
End Sub

 

[PaulClough]

Thank You BobLarson, Ok, but how does Access know that the data in Me!OrigSaved should be written to a particular field in a particular table. I don't think that Me!OrigSaved is the exact field name in the access table. What is the cross reference between the form field and the table field?
Again, my table name is [tCurExpTranx] & field name is [ctWhenUpd] AND my form field name is [WhenUpdated] How do I cross reference these & where? Does the me! somehow create this reference?

For clarity: I create a text box on my form and select the field from the drop down filed list ctWhenUpd for the Data Control Source and give it the Other'Name of WhenUpdated. Now I want Now() to be save in this record when I update it or Insert it. So exactly what syntax do I use in this routine?

I noticed in the 1st code you used me.form name and the second you used me!formname. Does it matter?

Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.NewRecord Then Me!OrigSaved = Now Me!OrigSavedBy = GetUserName Else Me!LastModified = Now Me!LastModifiedBy = GetUserName End If End Sub


Thank you, Still confused.

 

[boblarson]

I noticed in the 1st code you used me.form name and the second you used me!formname. Does it matter?

Nobody used me.form name or me!formname.

If you are talking about my code (boblarson, not G37Sam) I used the dot first which in my usage is going to refer to the CONTROL on the form (ME). ME is a shortcut which refers to the current class object, in this case the current form. And then the usage I was using the BANG (!) you wouldn't need to have a control on the form as long as those fields were in the form's Record Source.

 

[PaulClough]

thank you BobLarson, I got it working - I think the systax is sinking thru. Me is the current class object a '.' is used for direct reference and a '!' is used for a more general reference with the field may not be directly visable. I have been trying so many ways that didn't work.

 

[PaulClough]

thank you BobLarson, For clarity and understanding:
My table name is [tCurExpTranx] & field name is [ctWhenUpd] AND my form field name is [WhenUpdated].
Would these give the same results?

Me.WhenUpdated = Now()
[tCurExpTranx]![ctWhenUpd] = Now() if I had the correct syntax - what is the correct syntax on the 2nd one? Or is the Me. always used for form fields.

 

[boblarson]

Me.ctWhenUpd = Now

(you will probably note that the parens disappear when using them in VBA, which is why I didn't use them)

 

[KiEESH]

Hi!

Trying to do the same thing.

I tried this and got "You Can't Assign A Value To This Object."

My public function works fine. The form displays the username.

I added the priavate sub on my form's BeforeUpdate event, and that's where the issue lies.

Table Field name: LAST_EDITED_BY
Form Text Box Control name: LAST EDITED BY (no underscore)

Please help!

Public Function GetUserName() As String
 
GetUserName = Environ("UserName")
End Function
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
   
   Me.LAST_EDITED_BY = GetUserName()
 
End Sub

 

[KiEESH]

The username still isn't visible on the associated table.

 

[KiEESH]

Also...my table is linked.

VBA tells me:

"The expression you entered refers to an object that is closed or doesn't exist"

Not sure if that has anything to do with it, even though I'm able to edit other fields in the linked table with no issue.

 

[KiEESH]

Figured it out. Geesh!! This thing gave me grief for two weeks!!

I still have no idea when to use certain types of syntax.
But, I played around with it until it worked.


Private Sub Form_BeforeUpdate(Cancel As Integer)

Me![LAST_EDITED_BY] = GetUserName()

End Sub