show date of changed data on form in Access 2000

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a membership database of around 1400 members. Subs renewals come in
at any time during the year, also change of address, etc.. I'd like to have
a box showing the date that any data was changed on each form.

I had it working on my old Access (Office 98) but can't seem to work it out
this time.

I'm totally self-taught and managed to get some quite complicated actions
working but this one has me beat!

Hope someone can help.
 
At it's simplest, do the following:
1) add a field to the table behind the form called UpdateDate
2) add a textbox to the form called txtUpdateDate
3) In the AfterUpdate Event of the FORM, have the following code:
Private Sub Form_AfterUpdate()
Me.txtUpdateDate = Date
End Sub
4) If you want to store the date AND time the change was made, change the
line to:
Me.UpdateDate = Now()

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
I have a membership database of around 1400 members. Subs renewals come in
at any time during the year, also change of address, etc.. I'd like to have
a box showing the date that any data was changed on each form.

I had it working on my old Access (Office 98) but can't seem to work it out
this time.

I'm totally self-taught and managed to get some quite complicated actions
working but this one has me beat!

Hope someone can help.
Click to expand...

You'll need just a little bit of VBA code. Add a Date/Time field,
WhenChanged, to each table that you want to track in this way. On the
Form which updates the table, put a textbox (which can be invisible or
visible as you please) bound to the field, I'll call it
txtWhenChanged.

In the Form's BeforeUpdate event click the ... icon, choose the Code
Builder, and put the following: Access gives you the first and last
lines for free:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Me!txtWhenChanged = Now
End Sub

John W. Vinson[MVP]
 
Dear Roger Carlson

Many thanks for your instructions (below), but although I've done exactly
what you suggested, it's not working. In the new text box on the form I get
#Name?

The new field is a date/time field, so I can't understand why it's asking
for a name!

Any suggestions?

Ishbel Kargar
 
Dear John Vinson
Many thanks for your instructions (below), but although I've done exactly
what you suggested, it's not working. In the new text box on the form I get
#Name?

The new field is a date/time field, so I can't understand why it's asking
for a name!

Any suggestions?

Ishbel Kargar
 
Dear John Vinson
Many thanks for your instructions (below), but although I've done exactly
what you suggested, it's not working. In the new text box on the form I get
#Name?

The new field is a date/time field, so I can't understand why it's asking
for a name!
Click to expand...

Sounds like you added a textbox to the form, and gave it a control
source, but didn't add the new field to the Table - or perhaps you
added it to the table but not to the Form's Recordsource query.

Try opening the Form in design view; view its Recordsource property on
the Data tab. Is it just a table name? If so, have you in fact opened
this table in design view and added the new field?

If it's not the table name but rather a query name or a SQL string,
click the ... icon to open the Query Editor and add the new field. It
will now be available in your form.

John W. Vinson[MVP]
 
Back
Top