Static Auto dates

[jayme27]

I have a field within a row on the form. Call this field [Date of Entry] This date should not be a running (dynamic) date, rather a static date that shows the last time that particular row was updated/changed/etc by a user. Is this possible without the user being able to manipulate the field?

A. If something like this is possible, what is the correct code I would use and where would I need to put it to work properly?

 

[lorraine]

I dont know if this covers exaclty what you want but if you put Now() in the default value of the text field on the form holding your date, the date will automatically pop up as the date the record was added and this could be amended if you want.

 

[jayme27]

Putting =Now() into the Default of the data field will not be a running date that changes daily? If it displays the current date that the change was made to the given row then stops, it will be what I am looking for. Otherwise, the whole column will always read todays date which is not necessarily when a change, update or new record was added.

?

 

[AlanS]

For the DateOfEntry field on the form, set the Locked property to Yes (or the Enabled property to No). In the BeforeUpdate event procedure for the form, add the following code:

DateOfEntry = Now()

That code will execute only when the record is updated, and the field will remain visible but not available for editing by the user.

 

[jayme27]

In reference to Topic in ACCESS World Forums. Putting the following code into the BEFOREUPDATE event procedure for the form did not produce any results when the record was updated. It stayed blank. I tried combinations of the following code which didn't do anything.

DateOfEntry=Now()
or
=[DateOfEntry]=Now()
or
[DateOfEntry]=Now()

What else do you think the problem could be? THanks.

 

[Rich]

Try Me.DateofEntry=Date
HTH

 

[jayme27]

I entered that exactly as you specify on the screen into the Before Update, then I tried it in the After Update, then I tried it in the Change and still nothing happened when the record was updated, changed or added. Nothing displays, just a blank field. Why isn't it working?

 

[mrabrams]

Just my 2 cents.
In your original post, the field was called [Date of Entry]
Is that the actual name of the field (with the spaces?)

Michael Abrams

 

[jayme27]

No spaces between characters.

Any ideas?

 

[mrabrams]

This sounds like something I do in one of my dbs.

I have a field called DateCreated. It is formatted as a Short Date (mm/dd/yyyy) in the table and on the form.

The code I have written is on the After_Update Event of a specific textbox.
The code is:
[txtDateCreated] = Format(Date, "mm/dd/yyyy")
This gives me the date the record was created. (Static as you called it)

Try it and let us know.

Michael Abrams

 

[Pat Hartman]

The answer posted by AlanS yesterday is correct. Make sure you have put the code in the BeforeUpdate event of the FORM. EVERY time the record is updated or a new record is inserted, this event will execute.

mrabrams, FYI - date/time fields in a relational database are NOT stored as text strings. The Format() function you posted has NO effect on how the date is stored. As long as Access recognizes a string as a "correct" date, it will convert it to its own internal serial number format for storage. Formats ONLY affect how a date is displayed. Look up some of the Y2K responses from last year for a complete explaination of how this data type works including the pivot year explainations which will tell you how Access determines what the century is when you only enter a two digit year.

 

[mrabrams]

Ms. Hartman,
Thank you for your expertise.
It is much appreciated.

As usual, I am a bit confused.
In my table, the field "Date_Created" I have as a Date data type, Input Mask as 99/99/0000;;_ and Format set as mm/dd/yyyy.
On my bound form, I have an invisible textbox called txtDateCreated (Control Source is Date_Created) with Input Mask as 99/99/0000;;_ and Format set as mm/dd/yyyy.

On this form, there is a combo box with my user's initials. In the After_Update event of this combo box is where I have the Highlighted code from above post.
I thought what I was doing was saying, "After the user puts his/her initials in the combo box, behind the scenes I have a text box bound to the field Date_Created, which is receiving the Current Date in (Y2K) short date format." The field is a Date data type, so I was under the impression that I was storing it as a Date data type !!

I apparently stand corrected :}}

Thanks again for sharing your knowledge.

Michael Abrams

 

[jayme27]

I'm sorry, but Alan S's solution does not work and I've even tried different variations.

Here's the breakdown:

a. Name: DateOfEntry
b. Control Source: DateOfEntry
c. Format is in date format
d. BeforeUpdate: [DateOfEntry]=Now()

Tried the following action:
1. Changed an entry to the row/record
2. Added a new row/record

Result
1. No date or anything displays after the action.

What is missing or incorrect?

 

[Rich@ITTC]

Hi Jayme27

I wonder whether your problem lies in having the control [DateOfEntry] having the same name as your control source[DateOfEntry]. It is always better to name the actual control with an L&R prefix, so that the two objects - the control and the field from your table - have different names.

a. Name: txtDateOfEntry
b. Control Source: DateOfEntry
c. Format is in date format
d. BeforeUpdate: [txtDateOfEntry]=Now()

This might just do the trick

HTH

Rich Gorvin

[This message has been edited by Rich@ITTC (edited 04-25-2001).]

 

[jayme27]

SUCCESS!!!

For a simple person like myself here is the steps:

1. In the Data field on the Form, go to the event procedure properties. Do NOT type in [Nameofdatafield] = Now(). You must click on the 3 dots next to the BEFOREUPDATE Property of the FORM (Not the data field}. CLICK CODE and Paste the following [Nameofdatafield] = Now() between the two lines of code.

2. Exit and you will notice "EVENT PROCEDURE" in the BEFOREUPDATE Property of the FORM.

3. Good to go.

Appreciate the all the input.