CurrentUser and Now

[Monika Bjurman]

I've read all I can find here in numerous threads about recording current
user and date/time when someone makes a change in a form. I tried using a
timestampfield in a table and a form but when I make a change in the form
absolutely nothing happends eighter in the form field for the timestamp or in
the associated table. Can somebody please give me an idiotproof step by step
explanation on how to do this?

I've made a simple field in my table and namned it TimestampField. After
that I added it to an existing form. What do I do next?

 

[Monika Bjurman]

I am using Access 2003.

 

[Scott Whetsell, A.S. - WVSP]

You fields aren't automatically updating because the information is already
stored in the record. There are two basic methods for how to track changes.

First, if you don't care about keeping an entire history of every time a
record has changed, you could add the following code to your save button's on
click event.

Me.YourUserNameFieldName = CurrentUser()
Me.YourDateTimeUpdatedFieldName = Now()

Second, Allen Browne (one of the MVPs) has an excellent example of how to
keep an audit trail of all changes made in your database on his website.
www.allenbrowne.com

Either of those options should get you towards what you need.

 

[Marshall Barton]

I've read all I can find here in numerous threads about recording current
user and date/time when someone makes a change in a form. I tried using a
timestampfield in a table and a form but when I make a change in the form
absolutely nothing happends eighter in the form field for the timestamp or in
the associated table. Can somebody please give me an idiotproof step by step
explanation on how to do this?

I've made a simple field in my table and namned it TimestampField. After
that I added it to an existing form. What do I do next?

Where do you get the user's identification? Doesn't the
table need a field to record that information too?

In any case, use the form's BeforeUpdate event to set the
field:
Me.TimeStampField = Now
Me.[user ident field] = something

Note that putting those fields in the same table that is
being edited can only record the last change. You would
need a separate table if you want a history of all changes.

 

[Monika Bjurman]

Isn't currentUser getting the login value of each user? I might need to do
something here but Now() doesn't work eighter so there must be something else
that is wrong also.

If I try your solution or Scotts I get a message that the macro Me is
missing. Why?

//Monika

I've read all I can find here in numerous threads about recording current
user and date/time when someone makes a change in a form. I tried using a
timestampfield in a table and a form but when I make a change in the form
absolutely nothing happends eighter in the form field for the timestamp or in
the associated table. Can somebody please give me an idiotproof step by step
explanation on how to do this?

I've made a simple field in my table and namned it TimestampField. After
that I added it to an existing form. What do I do next?

Where do you get the user's identification? Doesn't the
table need a field to record that information too?

In any case, use the form's BeforeUpdate event to set the
field:
Me.TimeStampField = Now
Me.[user ident field] = something

Note that putting those fields in the same table that is
being edited can only record the last change. You would
need a separate table if you want a history of all changes.

 

[Monika Bjurman]

I think that the latest change is enough for this database and I'm not using
a counter as my primary key so Allen Browns example isn't working in my
database. Thanks for the link anyway.

I was looking for something easier than a save button because I suspect that
the other users will forget to press the button. I tried it anyway and got a
message that the Me macro was missing. Why?

//Monika

 

[Marshall Barton]

Isn't currentUser getting the login value of each user? I might need to do
something here but Now() doesn't work eighter so there must be something else
that is wrong also.

If I try your solution or Scotts I get a message that the macro Me is
missing. Why?

You are getting that error because you entered the code I
suggested in the BeforeUpdate property. The property must
contain
[Event Procedure]
The VBA code I posted must be in the BeforeUpdate event
***procedure***. You can get to the event procedure by
clicking on the builder button [...] to the right of the
property.

CurrentUser only has meaning if you are using the workgroup
security feature. Workgroup Security is a very advanced
topic and is no longer recommended because it is complex,
not available with the new acedb format files in A2007, not
really secure, etc.

 

[Scott Whetsell, A.S. - WVSP]

You could tag it to an afterupdate event of a field that you know is going to
change, or on the OnDirty event of the form.

If Me.whatever isn't working you could reference the form explicitly
Forms![yourformname]![yourcontrolname] = Value
Leave the brackets, but replace your form and control names as appropriate.

 

[Monika Bjurman]

Ah, I tried using the event procedure [...] but my version of Access is in
swedish so all the english words are translated to swedish and some I can't
even find in swedish ("me" and "currentuser" for instance). Even though I
sometimes know the correct translation (ex. currentuser=aktuellanvändare) and
the translated word works I still can't find them in the event procedure.

To complicate things even further some of the english syntax is also
translated. A decimal value of 33.3 is only working as 33,3 in my version and
"," has to be changed to ";" in order to work.

Needless to say this is slowly driving me insane.

I restarted Access and suddenly I got a value from both currentuser and now
if I print dem as a standard value but I still haven't figured out how to use
it in Before Update and save the value in my table. Do you have any other
clue?

//Monika

Isn't currentUser getting the login value of each user? I might need to do
something here but Now() doesn't work eighter so there must be something else
that is wrong also.

If I try your solution or Scotts I get a message that the macro Me is
missing. Why?

You are getting that error because you entered the code I
suggested in the BeforeUpdate property. The property must
contain
[Event Procedure]
The VBA code I posted must be in the BeforeUpdate event
***procedure***. You can get to the event procedure by
clicking on the builder button [...] to the right of the
property.

CurrentUser only has meaning if you are using the workgroup
security feature. Workgroup Security is a very advanced
topic and is no longer recommended because it is complex,
not available with the new acedb format files in A2007, not
really secure, etc.

 

[Monika Bjurman]

Now the error message thinks that everything in the update event is a macro (
Forms![My tablename]![My fieldname]=CurrentUser() ). I'm starting to think
that this has to do with the fact that my version is in swedish and that the
syntax for calling a macro is somehow translated to swedish terms. Se my
answer to Marshall Barton.

//Monika

You could tag it to an afterupdate event of a field that you know is going to
change, or on the OnDirty event of the form.

If Me.whatever isn't working you could reference the form explicitly
Forms![yourformname]![yourcontrolname] = Value
Leave the brackets, but replace your form and control names as appropriate.

I think that the latest change is enough for this database and I'm not using
a counter as my primary key so Allen Browns example isn't working in my
database. Thanks for the link anyway.

I was looking for something easier than a save button because I suspect that
the other users will forget to press the button. I tried it anyway and got a
message that the Me macro was missing. Why?

//Monika

 

[Marshall Barton]

Ah, I tried using the event procedure [...] but my version of Access is in
swedish so all the english words are translated to swedish and some I can't
even find in swedish ("me" and "currentuser" for instance). Even though I
sometimes know the correct translation (ex. currentuser=aktuellanvändare) and
the translated word works I still can't find them in the event procedure.

To complicate things even further some of the english syntax is also
translated. A decimal value of 33.3 is only working as 33,3 in my version and
"," has to be changed to ";" in order to work.

Needless to say this is slowly driving me insane.

I restarted Access and suddenly I got a value from both currentuser and now
if I print dem as a standard value but I still haven't figured out how to use
it in Before Update and save the value in my table. Do you have any other
clue?

The **form** BeforeUpdate event can run code like I posted
to set the date field just before the modified record is
saved. Except for your language issue, this is a common and
simple thing to do.

There are any number of ways to mess things up, but it would
be pointless for me to start guessing without any clues
about what is happening. At least post a Copy/Paste of the
procedure as as you now have it.

If you are not using workgroup security, I believe
CurrentUser just returns "Admin", which is useless for your
purpose.

 

[Scott Whetsell, A.S. - WVSP]

I was reading your reply to Marshall a few min ago. I can't say for sure,
but that fact that your version is sweedish may be part of your problem.
Just to reinforce what Marshall told you, all the coding that we have
provided you goes in the actual VBA part of the program by setting your
property to [Event Procedure] then clicking the [...] builder button and
placing the code in the sub.

It's incidental but may help to add " Option Explicit " directly under "
Option Compare Database ".

But I would definately give a chance to anything Marshall comes up with as
he is a MVP. But I'm glad to keep offering ideas.

Now the error message thinks that everything in the update event is a macro (
Forms![My tablename]![My fieldname]=CurrentUser() ). I'm starting to think
that this has to do with the fact that my version is in swedish and that the
syntax for calling a macro is somehow translated to swedish terms. Se my
answer to Marshall Barton.

//Monika

You could tag it to an afterupdate event of a field that you know is going to
change, or on the OnDirty event of the form.

If Me.whatever isn't working you could reference the form explicitly
Forms![yourformname]![yourcontrolname] = Value
Leave the brackets, but replace your form and control names as appropriate.

I think that the latest change is enough for this database and I'm not using
a counter as my primary key so Allen Browns example isn't working in my
database. Thanks for the link anyway.

I was looking for something easier than a save button because I suspect that
the other users will forget to press the button. I tried it anyway and got a
message that the Me macro was missing. Why?

//Monika

:

You fields aren't automatically updating because the information is already
stored in the record. There are two basic methods for how to track changes.

First, if you don't care about keeping an entire history of every time a
record has changed, you could add the following code to your save button's on
click event.

Me.YourUserNameFieldName = CurrentUser()
Me.YourDateTimeUpdatedFieldName = Now()

Second, Allen Browne (one of the MVPs) has an excellent example of how to
keep an audit trail of all changes made in your database on his website.
www.allenbrowne.com

Either of those options should get you towards what you need.



:

I've read all I can find here in numerous threads about recording current
user and date/time when someone makes a change in a form. I tried using a
timestampfield in a table and a form but when I make a change in the form
absolutely nothing happends eighter in the form field for the timestamp or in
the associated table. Can somebody please give me an idiotproof step by step
explanation on how to do this?

I've made a simple field in my table and namned it TimestampField. After
that I added it to an existing form. What do I do next?

 

[Monika Bjurman]

Now I finally got it working.

I misunderstood where you wanted me to write the code. My daughter has kept
me up all night and my head feels like it's full of glue.

This is what finally worked for me. There may be a more clever way to write
the code but at least it works.

-----------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
Uppdateringssignatur.Value = CurrentUser()
Uppdateringsdatum.Value = Now()
End Sub
-----------------------------------------------------------

I'll try to understand how to maintain a descent security tomorrow and hope
for a good nights sleep first (which i unlikely with a daughter that has got
viral croup).

Thanks to both you and Scott for all your help.

//Monika

Ah, I tried using the event procedure [...] but my version of Access is in
swedish so all the english words are translated to swedish and some I can't
even find in swedish ("me" and "currentuser" for instance). Even though I
sometimes know the correct translation (ex. currentuser=aktuellanvändare) and
the translated word works I still can't find them in the event procedure.

To complicate things even further some of the english syntax is also
translated. A decimal value of 33.3 is only working as 33,3 in my version and
"," has to be changed to ";" in order to work.

Needless to say this is slowly driving me insane.

I restarted Access and suddenly I got a value from both currentuser and now
if I print dem as a standard value but I still haven't figured out how to use
it in Before Update and save the value in my table. Do you have any other
clue?

The **form** BeforeUpdate event can run code like I posted
to set the date field just before the modified record is
saved. Except for your language issue, this is a common and
simple thing to do.

There are any number of ways to mess things up, but it would
be pointless for me to start guessing without any clues
about what is happening. At least post a Copy/Paste of the
procedure as as you now have it.

If you are not using workgroup security, I believe
CurrentUser just returns "Admin", which is useless for your
purpose.

 

[Monika Bjurman]

Can I ask a follow up question? When I tried the same Form_BeforeUpdate code
in my subform I get an error saying something that is impossible to translate
with terms that is understandable. I used the same code that I used in the
parent but changed the fieldnames to the ones in one of the subforms tables.
I think that the problem is that one of the fields in the subform collects
its values from another table in a table query.

SELECT [Monikas - Kurser1].[Utbildning] FROM [Monikas - Kurser1] ORDER BY
[Utbildning];

How do I give the information that a change in the field Utbildning should
change UppdateringssignaturKurs and UppdateringsdatumKurs in the table
[Monikas - Vilken elev går vilken kurs] and not in [Monikas - Kurser1]?

I know that the names of the tables are way to long but this is just a
trialround.

My BeforeUpdate code:
-----------------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
UppdateringssignaturKurs.Value = Environ("USERNAME")
UppdateringsdatumKurs.Value = Now()
End Sub
-----------------------------------------------------------------

As you can se I changed the CurrentUser to Environ("USERNAME"). That seems
to work better.

//Monika

Ah, I tried using the event procedure [...] but my version of Access is in
swedish so all the english words are translated to swedish and some I can't
even find in swedish ("me" and "currentuser" for instance). Even though I
sometimes know the correct translation (ex. currentuser=aktuellanvändare) and
the translated word works I still can't find them in the event procedure.

To complicate things even further some of the english syntax is also
translated. A decimal value of 33.3 is only working as 33,3 in my version and
"," has to be changed to ";" in order to work.

Needless to say this is slowly driving me insane.

I restarted Access and suddenly I got a value from both currentuser and now
if I print dem as a standard value but I still haven't figured out how to use
it in Before Update and save the value in my table. Do you have any other
clue?

The **form** BeforeUpdate event can run code like I posted
to set the date field just before the modified record is
saved. Except for your language issue, this is a common and
simple thing to do.

There are any number of ways to mess things up, but it would
be pointless for me to start guessing without any clues
about what is happening. At least post a Copy/Paste of the
procedure as as you now have it.

If you are not using workgroup security, I believe
CurrentUser just returns "Admin", which is useless for your
purpose.

 

[Marshall Barton]

Can I ask a follow up question? When I tried the same Form_BeforeUpdate code
in my subform I get an error saying something that is impossible to translate
with terms that is understandable. I used the same code that I used in the
parent but changed the fieldnames to the ones in one of the subforms tables.
I think that the problem is that one of the fields in the subform collects
its values from another table in a table query.

SELECT [Monikas - Kurser1].[Utbildning] FROM [Monikas - Kurser1] ORDER BY
[Utbildning];

How do I give the information that a change in the field Utbildning should
change UppdateringssignaturKurs and UppdateringsdatumKurs in the table
[Monikas - Vilken elev går vilken kurs] and not in [Monikas - Kurser1]?

If the field is not in the form's record source table/query,
then you can not modify it using that kind of code.

I might be able to suggest something, but I would have to
know how you are getting its value into the subform.

Note that if the subform's record source is a query that
joins two tables, you might(?) be able to update the fields
in both tables if you also include each table's primary key
in the query.

 

[Monika Bjurman]

Hi, and sorry for my late reply. I redid the form and this time everything
worked. I think that my prior problem was in the sql statement for the entire
subforms data source.

I think that what worked for me was the same thing you was talking about in
your last message.

Thanks!
//Monika

Can I ask a follow up question? When I tried the same Form_BeforeUpdate code
in my subform I get an error saying something that is impossible to translate
with terms that is understandable. I used the same code that I used in the
parent but changed the fieldnames to the ones in one of the subforms tables.
I think that the problem is that one of the fields in the subform collects
its values from another table in a table query.

SELECT [Monikas - Kurser1].[Utbildning] FROM [Monikas - Kurser1] ORDER BY
[Utbildning];

How do I give the information that a change in the field Utbildning should
change UppdateringssignaturKurs and UppdateringsdatumKurs in the table
[Monikas - Vilken elev går vilken kurs] and not in [Monikas - Kurser1]?

If the field is not in the form's record source table/query,
then you can not modify it using that kind of code.

I might be able to suggest something, but I would have to
know how you are getting its value into the subform.

Note that if the subform's record source is a query that
joins two tables, you might(?) be able to update the fields
in both tables if you also include each table's primary key
in the query.