creating a last updated field in access

J

John Warren

How do I create a "last updated" field in an access table or form that will
fill in automatically whenever I make edits to an existing record ?
 
R

Rick Brandt

John said:
How do I create a "last updated" field in an access table or form
that will fill in automatically whenever I make edits to an existing
record ?
Click to expand...

You can only do this if ALL edits are performed using a form as there are no
events at the table level for this. Just add a DateTime field and in the
BeforeUpdate event of the form have code.

Me!FieldName = Now()
 
T

Tom Wickerath

Hi John,

Create a LastUpdated field in your table (note: spaces and other special characters are not
recommended). This field should have datatype Date/Time.

If your form is based on a saved query or SQL statement, then add this new field to the query or
SQL statement. If your form is based on one table only, then you don't need to worry about this.

Add a new textbox to your form. Name it txtLastUpdated, and set it's control source to the
LastUpdated field that you just added to your table. You will likely want to set the visible
property for this textbox to false. Then add the following procedure to the form's Before_Update
event procedure. You can do this by clicking on View > Properties in form design view to display
the Properties dialog (if it is not already displayed). Select the Event Tab. Make sure that the
word Form is displayed in the blue title bar of the Properties dialog. Click on the ellipses
button (the button with the three dots) for the Before Update event procedure. If you are
prompted to choose between


Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError

Me!LastUpdated = Date
Me!txtLastUpdated.Requery '<==Suggested line if textbox is visible

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in Form_BeforeUpdate event procedure..."
Resume ExitProc
End Sub

Note that the LastUpdated field in your table will only be updated when you make edits via this
form. If you make edits directly in the table, or in a query that includes the table, then you
will need to update this field manually.

Tom
___________________________________


How do I create a "last updated" field in an access table or form that will
fill in automatically whenever I make edits to an existing record ?
 
S

Steve Huff

Another option is to create add a field such as "DateAdded" to your table and
then create a query and use the Now() function to populate the DateAdded
field automatically and then only use the queries on your forms. An example
is below:

SELECT tblPeople.PK, tblPeople.FName, tblPeople.LName, Now() AS DateAdded
FROM tblPeople;

-Steve Huff
http://www.huffs.us
 
R

Rick Brandt

Steve Huff wrote
:> :
Another option is to create add a field such as "DateAdded" to your
table and then create a query and use the Now() function to populate
the DateAdded field automatically and then only use the queries on
your forms. An example is below:

SELECT tblPeople.PK, tblPeople.FName, tblPeople.LName, Now() AS
DateAdded FROM tblPeople;
Click to expand...

I'm sorry, but huh?

If a DateAdded field were added to the source table with a default value of
Now(), it would automatically tell you when records were *added*, but it would
do nothing to indicate when they were updated which is what the OP asked about.

Your suggestion to run a query to back-fill such a field with Now() makes little
sense unless one takes the position that an incorrect value for DateAdded is
better than no value at all, and then you offer a SELECT query which will always
return Now() for every row in the table and that value will change every time
the query is run. What possible benefit would be provided by that?

If I run it today it will tell me the record for "John Doe" was added on today's
date and if I run the query tomorrow it will tell me that John Doe's record was
added on tomorrow's date.
 
Top