Update Date in a table.

[Guest]

Hello everyone,
I have a database with a table with about 500 records in it. Every day I add
another 3 to 4. I also update some of the old records in the same table. I
want to add a field in the table that will track the dates I make changes in
the rows in the table. I dont want to manually enter this information, but
each time I make a change in one row in the field, the date field
corresponding to that column showd automatically update itself. That way if I
want to sort all the rows to see the ones in which changes were made most
reccently, I can do so. I will also be able to run queries to see the rows
that were updated last wekk or last month or any date for that matter.
Please help.
I greatly appreciate this.

 

[Armen Stein]

Hello everyone,
I have a database with a table with about 500 records in it. Every day I add
another 3 to 4. I also update some of the old records in the same table. I
want to add a field in the table that will track the dates I make changes in
the rows in the table. I dont want to manually enter this information, but
each time I make a change in one row in the field, the date field
corresponding to that column showd automatically update itself. That way if I
want to sort all the rows to see the ones in which changes were made most
reccently, I can do so. I will also be able to run queries to see the rows
that were updated last wekk or last month or any date for that matter.
Please help.
I greatly appreciate this.

If you are updating your records using the table or a query directly,
there is no way to automatically update a date/time field in Access.

However, if you use a form to update your records, you can use the After
Update event of each field to update the corresponding date/time field
in the same record, something like this:

In Field1 After Update Event:

Me.Field1ChangedDateTime = Now()

 

[Larry Daugherty]

First create the new field in your table with a meaningful name such
as "ChangedDate" and a Date/Time datatype.

On the form(s) that is(are) used to add and enter data, create a new
textbox control named something like "txtDateChanged". The data
source for that control is the field you just created in your table.
In the Before_Update event of your form(s) place a line of code:

me!txtDateChanged=today()

I recommend that you set the visible property of txtDateChanged and
its label to False.

The effect of the above is that new data or data that has been changed
will get today's date. Data that is viewed without change will keep
the date it had.

HTH

 

[Jamie Collins]

If you are updating your records using the table or a query directly,
there is no way to automatically update a date/time field in Access.

At the engine level, the very least the OP should do is to add a
validation rule to ensure the 'last modified date' column is maintained
e.g.

=NOW()

Better, would be to provide a 'helper' procedure (parameter query) to
UPDATE the column values and maintain the date e.g.

CREATE TABLE Test (
col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
col3 INTEGER NOT NULL,
last_modified_date DATETIME DEFAULT NOW() NOT NULL,
CHECK (last_modified_date = NOW())
)
;
CREATE PROCEDURE ProcTest (
arg_col1 INTEGER = NULL,
arg_col2 INTEGER = NULL,
arg_col3 INTEGER = NULL
) AS
UPDATE Test
SET col1 = IIF(arg_col1 IS NULL, col1, arg_col1),
col2 = IIF(arg_col2 IS NULL, col2, arg_col2),
col3 = IIF(arg_col3 IS NULL, col3, arg_col3),
last_modified_date = NOW()
;

Jamie.

--

 

[Guest]

I have been having trouble adding a “last modified†date to my Access
Database form. I appreciated your detailed step by step instructions in a
post of the same question back on August 18th. I used your instructions (I
included them below) but when I tested the Form I continually got this error:

Microsoft Access cannot find the macro “me!txtDateChanged=today()â€

I know just enough about Access to barely get by. Do you have any
suggestions? I’m pulling my hair out!

Thanks!

First create the new field in your table with a meaningful name such
as "ChangedDate" and a Date/Time datatype.

On the form(s) that is(are) used to add and enter data, create a new
textbox control named something like "txtDateChanged". The data
source for that control is the field you just created in your table.
In the Before_Update event of your form(s) place a line of code:

me!txtDateChanged=today()

I recommend that you set the visible property of txtDateChanged and
its label to False.

The effect of the above is that new data or data that has been changed
will get today's date. Data that is viewed without change will keep
the date it had.

HTH