Adding a form field that is a "Last Saved by"...

[Jakboi]

Hello,

I was wondering if this was possible. I would like to have a field on my form that states who last saved the entry.

I have a table that has business prospects and people responsible for them. A certain entry my have 2 or 3 people responsible for the prospect. I wanted to record who last edited a particular entry, is this possible?

I couldnt find a "last saved by" function.

Thanks.

 

[Brianwarnock]

You need to use the Before update event of the form and code somethink like

Me.yourcontrol = Environ("username")

Brian

 

[lightray]

You need to use the Before update event of the form and code somethink like

Me.yourcontrol = Environ("username")

Brian

Hi Brian, can you tell me more? what does the username relate to?

 

[subyruby]

RE: Last Saved By

Hi there! This is how I record the user who modified a record using the Windows API function. (This uses the name of the person logged onto the computer, such as jdoe or jsmith.)

First, create two fields in your table:
LastUpdate - Date/Time
LastUpdateUser - Text

Then, add those fields to your form.

Next, create a function in a new module (name it something like modlGetUserName).

Type the following into the module (or copy/paste from here):
_____________________
Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, _
nSize As Long) As Long
Function GetCurrentUserName() As String
'Retrieve name of current user using Windows API
Dim strName As String
Dim lngChars As Long
Dim lngRet As Long

strName = Space(255)
lngChars = 255

lngRet = GetUserName(strName, lngChars - 1)
If lngChars > 0 Then
GetCurrentUserName = Left(strName, lngChars)
Else
GetCurrentUserName = "Unable to retrieve name."
End If
End Function
________________________
Now, code the BeforeUpdate event of your form like this:
______________________
Private Sub Form_BeforeUpdate(Cancel As Integer)
'When info is updated, date and user automatically inserted into fields
Me.LastUpdate = Now()
Me.LastUpdateUser = GetCurrentUserName() 'refer to modlGetUserName
End Sub
___________________________
This will work if you keep the field and function names exactly the same as I've typed them here. But, if you understand the basic concept, you can change them to fit your needs.
On my form, I made the LastUpdate and LastUpdateUser fields locked, and not enabled so that the user can't change the data, but can visually see who made the last changes.
(I modified this code from "Automating Microsoft Access with VBA" by Susan Sales Harkins and Mike Gunderloy, in the Business Solutions series. It's a great book!)
I hope this helps!

 

[boblarson]

Brian's code is one line and does exactly the same as subyruby's. I would go with it (I used to use the longer code too, but why when it is so easy)

Me.YourBoundControlNameHere = Environ("username")

Lightray:
username is one of the Environment variables that are set by your computer when you start Windows/Log on.

 

[LLB]

Similar Problem

I am trying add an update date to my form as well.

I have used this code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Checks to see if the entered update date is valid. If not valid, sets it to current date.
If Me.UPDATE < Now() - 1 Or IsNull(Me.UPDATE) Then
If MsgBox("@THE UPDATE DATE FOR THE REPORT IS DIFFERENT THEN TODAY'S DATE.@ WOULD YOU LIKE TO HAVE IT SET TO TODAY?", vbYesNo, "DCF Database") = vbYes Then
Me.UPDATE = Date
End If
End If
End Sub


I want it to update only if changes were made and the form is closed or if moved to another record. I want it to ask so that it does not update if the user was just in looking at the data. Because I'm adding this new field to an existing database there are many null fields and I want it to ask on those as well.

Do I have this in the wrong event property? It does nothing. From everything I've researched it should work. Any help would be appriciated.

Thanks

 

[LLB]

I can't even get the simplest code to work.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.UPDATE = Now()
End Sub

It does not work for Before or After update event.

 

[boblarson]

What you need is a date field in your table and you just put a text box on the form (it can be set to Visible=False if you don't want it viewed). Then, in the Before Update event of the FORM (not the control), you set it by doing

Me.YourTextBoxNameHere = Now()

Now, if you have a field named 'Update' and a text box named 'Update' you now have two objects that have names that are using an Access/VBA RESERVED key word, which is something you do NOT want to do.

 

[Keith Nichols]

Hello,

I was wondering if this was possible. I would like to have a field on my form that states who last saved the entry.

I have a table that has business prospects and people responsible for them. A certain entry my have 2 or 3 people responsible for the prospect. I wanted to record who last edited a particular entry, is this possible?

I couldnt find a "last saved by" function.

Thanks.

I have a less foolproof solution to this than offered by the other gents that may be suitable to your needs.

Various comment recording tables in my database have a 'CommentBy' field which is required. When the user makes a new comment via the subform, he/she must select an identifier (their own!) from the listbox.

Of course, this does not record the person who edited records, but it would be relativley simple to have a 'LastEditedBy' field that a user sets via a drop down list. With a little jiggerypokery, a form could be set up that opens when the main form closes and shows the current time and the list box of personell entitled to edit the main form. Thus, the user would always have to go through this form to exit editing records.

The posible advantage of this is there is no need to create new tables (assuming your employees who can edit the data are already in the database ) to relate the information returned by environs into an actual name or identifier.

Hope this was of some interest.

 

[LLB]

Bob, thanks for the reply.

I pulled my original code from another one of my databases that I inherited and it works there. But I did take your advise and rename the control, but it still does not work even in a simple form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.txtUserUpdateDate = Now()
End Sub

What am I doing wrong? It just does nothing, no error. Will it only work on new records and not the existing records where I've added the new field?

Truly stumped,

Lori

 

[boblarson]

It sounds to me like the recordsource for the form isn't updateable. Did you bind the control to the field? Can you manually type a value in? If you still can't get it to work, can you strip down the database and post it so I can take a look? (I would need A2K format as that's what I have at work).

 

[LLB]

Sorry Bob, I only have A97 at work. Let me see if I can explain this without fumbling too much....(newbie here)

You are rigth, the recourdsource is not updateable. I don't know how it got that way. This is a form with a sub form. The main form fields are locked out because they are entered by the administrator and are not to be modified by the user. The subform holds the fields that are updated by the user. I've put this new update field on the main form but I have removed the lock from it and it is bound to the field on the main table. If I open the query for this form it will not let me enter the data manually there either. I attempted to switch the event to an onclick event to the subform but got the error that the recordsource was not updatable. This is a database that I created from scratch and if I locked out the recordsource I didn't know I was doing it.

Any thoughts on how I make the record source updatable?

Thanks, Lori

 

[boblarson]

Three thoughts come to mind. 1 - One, or both of your tables don't have primary keys defined. 2 - Your underlying query is joined incorrectly. 3 - Your subform is not linked to the main form correctly.

 

[Brianwarnock]

Hi Bob, sub forms are not a strong point, what is , but can you update a control on the main form from an event in a sub form?

Brian

 

[boblarson]

It is possible, depending on the timing of the update and what it is. The syntax from the event of the subform would be:

Forms!YourMainFormName.YourControlName = whatever you need here

 

[Brianwarnock]

Ah! I notice that LLb is using Me.controlname I take it that won't work.

Brian

 

[boblarson]

Brian -

If you use Me as the base keyword, it assumes the current form, which is the subform and as the control is on the main form, and not the subform, you can't refer to it with Me.

 

[Brianwarnock]

That's what I thought Bob , therefore LLB needs to change his code , he may well have the other issues you raised also, as I said sub forms are not a strong point for me.

Brian

 

[boblarson]

The one piece though is that their forms weren't updateable which is the main problem, I believe.

 

[LLB]

I have a main administrator interface with a similar data entry form (no sub form there) to which I added the update filed with my original code and it works great.

This is what I used in the "before update" event:
If Me.txtUserUpdateDate < Now() - 1 Or IsNull(Me.txtUserUpdateDate) Then
If MsgBox("@THE UPDATE DATE FOR THE REPORT IS DIFFERENT THEN TODAY'S DATE.@ WOULD YOU LIKE TO HAVE IT SET TO TODAY?", vbYesNo, "DCF Database") = vbYes Then
Me.txtUserUpdateDate = Date
End If
End If


I would like it to work the same in the user forms but I still can't even get a simplified version of update to work. I tried changing the user form (which contains the sub form) to include the main form name in the code instead of ME:

Forms!frmDataEntryUser.txtUserUpdateDate = Now()


Still I get nothing. The query itself will not update records. I tried to create a new query with the same fields and it is also not updateable.

I have two fields in the query that are calculated, will that lock the other fields out from being updateable?