Create a date stamp when record is saved 1

[MaDmiX]

I have been trying to create a field in my form that will update my table with the current date everytime the form is saved (a way of keeping track of the most recent change date). I have tried the Date()expression in the before and after update properties but it doesn't do anything, and if I use it in the default value property it updates everytime the record is accessed. Can anyone help me understand what I'm doing wrong?

Thanks,

Ken

PS I would like to do the same thing with the CurrentUser () but I guess once I know how to do one, the other will be similar.

 

[jrbarnett]

In your save routine, update the control value then save the data.

Something like:

Private sub cmdSave_click

Me!timestamp = date
me!user = CurrentUser()

DoCmd.RunCommand acCmdSaveRecord

End sub
----

The CurrentUser() function in Access returns the Access/Jet username - not the Windows username. Third party functions are available on access development websites to get the windows username and workstation name if this is what you wanted.
John

 

[GingerR]

i put it in the form's BeforeUpdate event and it works fine. you can't see it until you move to a diff record, then come back.
something else to do is put in each control that user might change, put in OnChange event the same thing.

windows username is merely an environment variable available in access

=environ("Username&quot

also if you want date+time use now() instead of date()

 

[MaDmiX]

I tried using the following code as an event procedure in the before update event of the form, but I got a "sub or function not defined error".

Private Sub Form_BeforeUpdate(Cancel As Integer)
SetValue [TQC_Sheet]![Last_Update_by] = CurrentUser()
SetValue [TQC_Sheet]![Last_Update_date] = Date
End Sub

The problem is I would like to update both the current date and the user fields. Can access have more than one expression in the before update event?

Ken

 

[GingerR]

it can have all the expressions you want.
set value is incorrect.


Private Sub Form_BeforeUpdate(Cancel As Integer)
me.[Last_Update_by] = CurrentUser()
me.[Last_Update_date] = Date
End Sub

 

[MaDmiX]

Thanks GingerR,

I thought it might not be the correct use of that action. Thanks for your help. I had to substitute Now() for the "Date" expression in your code because I kept getting a runtime error that Access couldn't find the field called "Date". Hopefully I will still be able to run my query the way I want to.

Thanks again,

Ken

 

[Jack58]

You can also try this,

Sub insertDate()
ActiveCell.Value = Date
End Sub

Let me know if this helps.


Jack

 

[Jack58]

Another tip would be

DateAdd("h",Now())

 

[GingerR]

or try Date()

 

[apkohn]

GingerR,

Just scrolling through the line of posts and stumbled across your first post in this thread, with regard to environ("Username&quot

I have been using a function pulled from

http://www.mvps.org/access,

called fOSUsername() to get the username. This function uses API calls.

Now the environ function works about 50 times faster, which for my purposes is really useful (I have a few queries that necesitate thousands of calls to this result (I know a stored variable would be better, but there are readability/maintainability/design issues that are important.

Does anyone know a list of all the commonly accessible environ variables, or a method to show the entire environ string.

 

[GingerR]

i used that function for a coupla years, then when i worked on a little project at MS a coupla years ago, someone there taught it to me. lost all that time...

check out this post in tektips:
thread222-468422

 

[MaDmiX]

Hi GingerR,

I tried using it as Date(), but the strangest thing... everytime I would save the form, Access changes the expression to Date (removing the "()&quot. I don't know if this has anything to do with it, but prior to adding your code, I had a field in my table which I had named "Date" (OK, so I'm still learnin!!!).

Access would run the code but return a whatever value was in that field (instead of the current date). I have since renamed the field to "QC_Date" but I think Access may still be trying to get the value from that "Date" field. If that is the case, I don't know I would fix it.

Ken

 

[mac318]

Perhaps this will help. Instead of using Autonumber to create unique record numbers, I use the following function:

RecordID = Format(Now,"yymmddhhmmss&quot

You can use this function in the form's BeforeUpdate procedure to record the date and time of any changes. You can use the str() functions to parse the dates for data retrieval with the date ranges. I have used this method for many years and have not yet received a duplicate key entry error. The field to hold the formatted date is a string with length of 12.

mac

 

[jrbarnett]

Madmix

When you renamed the field in your database, did you rename any bound controls called Date as well?

John

 

[GingerR]

i have replicated your problem. how odd: i have never seen this. i corrected it only by using Now() instead of Date. it kept erroring "Cannot find field 'Date'" which is dumb cause i'm not calling me.Date. i think tho i always use Now(). so use Now()instead. if all you want to see is the date (not time) on reports and such, you can always format it as ShortDate or "mm/dd/yy" or whatever you want. will this work?

 

[psemianonymous]

About environment variables.


to see all environment variables available to your system, go to DOS (or the COMMAND.COM shell, whatever you want to call it). Easiest way is Start->Run, and then type CMD (NT-based systems) or COMMAND (95/98/Me). You're in DOS.


Now type

SET <ENTER>

. It will display all environment variables. If the list is too long for one viewing, or if you just want to, type

SET >output.txt

and it will push the output to the text file.


Environment variables shouldn't be relied upon because what exists in one brand of Windows may not exist in another.


Now, about the whole "need environment variable lookup for efficiency", why not make a wrapper function myFindUsername() which calls the API function...once...and then every time after that, retrieves the value from a global string? That way it's clean, and it's efficient.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[MaDmiX]

Hi GingerR

Using Now() might work fine if I can format it as "mm/dd/yy". I'm using it in a query and the user inputs dates from a form to set the criteria. I tried Now() without the formating and the query didn't like that. Right now I have it working via a workaround (but I hate workarounds because they are like an admission of defeat... not to mention they clutter your code).

jrbarnett

Yes, I renamed the bound controls in my form too.

Thanks,

Ken