Access Database problem

[andyclift]

I am new to Access and have built a simple database to record club records and handle mailings. All is working OK and I can produce various reports on age, locations, etc but with my limited knowledge there is one thing I cannot get to work.

Each record on a form which updates the main table has fields for name, address, etc. and a "date created" and "date updated" field. When a new record is added the "date created" field is recording the current date correctly but if I modify any of the fields on a form I want the "date updated" field to show the current date but I am buggered if I can find a way to do it apart from entering manually.

I am guessing that I need an expression in each of the fields, e.g. "telephone", "email", etc which, if anything is changed in them, will enter the date in the "date updated" field. Any resident Access experts know how?

 

[GSmonkey]

There's a few ways to do it. My normal way to approach this:

1. Add the "date updated" field to the form. Generally I set the property "visible" to NO, so that the user doesn't know its there - but the data is recorded in the data tables.
2. Go to the form properties and select the event "Before Update"
3. Click on the "Builder" i.e. the icon with the three dots ... Then select "Code Builder".
4. Insert the expression "Me.[date updated] = Now()" Where "date updated" is the name of the control on the form which links to the field "date updated".

Run the form and it should dump in todays date / time whenever the record is updated.

SORTED

 

[andyclift]

GS Monkey.

Thanks for that, worked a treat, although I used Date() as I don't need the time. I must get a book on it, I'd spent ages trying to find the solution but trial and error is not too efficient.

 

[andyclift]

GS Monkey

Still struggling a little on something that is not vital but neater for my purposes. Ideally I only want the "Update" date to be entered if an original entry has been changed as it's an easy way to identify records that have been modified but it now shows an update date as soon a a new record is created.

If I was doing this in BASIC I would be looking at something like:

If "today's date" <> "Date Created field" THEN Me.[date updated] = Now()

Having spent an hour going through the Access Help files and then guessing on possible ways to do it, all I get is a Code Error report.

Any suggestions would be appreciated. So far the only solution I can think of is having separate forms, one for making a new record (without an Update field) and a second for modifications.

 

[Clive]

If "today's date" <> "Date Created field" THEN Me.[date updated] = Now()

If (Me.Created_Date <> Date) Then
Me.Updated_Date = Date
End If

 

[andyclift]

Thanks Clive.

 

[exeter_lad]

Just the job

Just to add my thanks to GS Monkey and Clive.

I was presented with exactly the same question in work today and knew exactly where to look.

So cheers guys