Software optimize

Let Access keep track of the date and time of the last record update

In certain situations, you may need to query your data by the last date modified. Mary Ann Richardson shares a simple trick for adding a field that logs the date and time a record is changed, making such queries a snap.

Do you need to query your data by the last date modified? For example, say you would like to include donors whose records show no activity during the last six months in a special fund-raising appeal. How do you know which donors are to be included? Follow these steps:

  1. Add a field to your Donors table called Date Modified and assign it a Date/Time data type. Then, open the form used to update the table in Design view.
  2. Open the form's property sheet.
  3. Click in the Before Update property box in the Event tab.

  1. Click the Build button and select Code.
  2. At the prompt, enter the following code:

Me![Date Modified].Value=NOW()

  1. Press Alt + Q.

Now each time a user changes a record, Access will enter the date and time from the system before the changes are updated. When the record is accessed again, the Date Modified field will contain the date and time of the last modification. You can query that field to determine which records have not been updated within the last six months.


Miss an Access tip?

Check out the Microsoft Access archive and catch up on other Access tips. Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.
5 comments
luhamabwamooka
luhamabwamooka

How do i count field/count content without counting duplicate details? I want to count say employees positions once in a report / query. I have tried severally to no avail in a crosstab query, i suspect am missing a point somehow, could some one help.

wasted_email
wasted_email

I can't get this to work for me. I am assuming it is because my db is not named Me. My database is named "daily" and my field is called [Last User Date]. Should it then be: Private Sub Form_BeforeUpdate(Cancel As Integer) daily![Last User Date].Value = Now() End Sub When I use that, I get an error: Run-time error '424': Object required

john.a.wills
john.a.wills

You should really submit this as a new question, not mix it into a discussion of a different subject. Just by the way, I do not have an answer for you.

lucy.dejesus
lucy.dejesus

Hi did you make it to work? I get the same error message; please email me at lucy.dejesus@nyls.edu thanks!