Last updated date and by field on form not working (1 Viewer)

[mari_hitz]

Hi,

Hope everyone is fine. Please note that I would like to add a field on my table that contains the last updated by and the last updated date.
As per the internet research that I did, I had added to my table the fields [Last updated Date] and [Last updated by].
Then on the form that I have on the text boxes of each of these fields I have added the following on the "Before Update" event:

For the last updated date:

Me![Last Updated Date].Value = Now()

And for the last updated by I found a function on internet that I had added to a module:

Option Compare Database
Option Explicit

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If
End Function

And then I have written on the line of "Before Update event" "fOSUserName()"

When I change the information on a column for a record these fields are not updated, they are on blank, do you have any idea why?
By the way I am displaying the information of the table as a split form and reviewing and working on the data as a spreadsheet view.

Do you know why this is not working? Thanks!

 

[isladogs]

Hi

I assume you mean datasheet view as Access doesn't have a spreadsheet view.

First of all I suggest you get rid of all spaces in field names
Use LastUpdatedBy & LastUpdatedDate
This makes it easier to read / type & the [] are no longer needed

The datatype of the LastUpdatedDate field needs to be DateTime
You don't need the .Value in the line below and Me. is better than Me! here

Me![Last Updated Date].Value = Now()

So if you want the date & time, I'd use:

Me.LastUpdatedDate = Now()

or if you only want the date , use

Me.LastUpdatedDate = Date()

A simpler way of getting user name is:

Environ("UserName")

Whichever code you use - this should be added to the After_Update event of a particular control.
You MAY also need to refresh the form to show the changes using this line later in the same code event

Me.Requery

 

[mari_hitz]

Hi ridders!

Hope you are good! Thank you for your help, I've done everything you have said and does not work.
The form in which I am working is a "Split Form" and I am reviewing the data as a table and not each record as a regular form.
Do you have any idea why this may be occurring?

The codes that I have set are the following:

Private Sub LastUpdatedDate_AfterUpdate()
Me.LastUpdatedDate = Now()
Me.Requery
End Sub

Private Sub LastUpdatedBy_AfterUpdate()
Me.LastUpdatedBy = Environ("UserName")
Me.Requery
End Sub

 

[isladogs]

Hi

I don't use split forms but hopefully that won't matter with regard to this issue

If you think about it, you can't set a field value in the after update event for the same field.
Its already updated ... or not .. in this case.
REMOVE the code you just added for those 2 EVENTS

Are you updating in the individual form section or the datasheet?

Anyway, you need to use a different field to trigger both changes
Lets call it YourField

Then in the YourField After_Update event you need 3 lines of code to set the values for LastUpdatedBy,& LastUpdatedDate & then requery the form using the code I gave you before

You only need to requery once , if at all.

 

[mari_hitz]

Hi ridders!

Thanks again for your help! Please note that I have done what you have suggested and it worked, however, I have updated data on a field of one of the records (I have 62 records on my form) and it updates the user and date and ALL the records. I would like only this information (Last updated date and last updated by) to be updated only for the field on which I am changing data. Is that possible?

Thanks!

 

[isladogs]

That's not normal behaviour.

I assume you do have a primary key field in the table used as recordsource for the form. If not you need to create one!

I've mocked up a simple database with one table & split form with the 2 fields you want updating automatically.
I have added exactly the code I said.
Turns out Me.Requery wasn't needed

As you can see below ONLY one record is updated each time I alter the Alias field in either half of the split form

My basic db is attached

 

[mari_hitz]

Hi ridders!

You rock! It worked perfectly after I have set the field "SSN" as Primary Key on my main table and works just fine.
Two last questions:
1) the form which I am changing comes from a query which I have made to be filtered based on a criteria. When people access it they have to insert the SSN number and then the form returns the necessary info. Since I have added to the end of the code ".requery" after the changes the pop up asking for the SSN appears. Is there anyway to avoid this?
2) I have several fields that can be updated, should I add the same code on the "After Update" for each field, right?

Thanks!

 

[isladogs]

It worked perfectly after I have set the field "SSN" as Primary Key on my main table and works just fine.
Two last questions:
1) the form which I am changing comes from a query which I have made to be filtered based on a criteria. When people access it they have to insert the SSN number and then the form returns the necessary info. Since I have added to the end of the code ".requery" after the changes the pop up asking for the SSN appears. Is there anyway to avoid this?
2) I have several fields that can be updated, should I add the same code on the "After Update" for each field, right?

1. You may not need the Me.Requery.
It worked fine without that in my test db I uploaded.
Or try Me.Refresh instead.

However I think a better approach is to just modify your query so the SSN is identified from your criteria

2. You could duplicate your code but if the same person is going to update all fields, its unnecessary.
However, if you think different fields will be updated at different times by different people, then add it again if you think its necessary. Bear in mind, it will only save the details for user & date/time for the latest change.

An alternative approach is to use an unbound form but then you have to write code to save each field individually - MUCH more work & rarely worthwhile

P.S. MAKE SURE ALL TABLES HAVE A PK FIELD!!!

 

[mari_hitz]

ridders,

You rock! This is issue is solved! Thank you very much for your help and support on this matter!

 

[isladogs]

You're welcome.
For the benefit of others reading this, can you say how you did solve the second part of this.

Please can you use the Thread tools drop down to mark this as solved.

Perhaps you could also do one of the responses from my signature line.

See you again I expect.