Showing Date Stamp on Form

[newt]

I have a date stamp field in a table that, by using an AfterUpdate event
attached to a text box in a form, automatically records the date that that
the field populated by that text box is modified. Now I have two problems:

1) How do I show in the form itself the current contents of the date stamp
field (i.e., the date that the text box was last updated)?

2) How do I stop the date stamp event from firing every time I leave Design
View in the form?

Thanks in advance!

 

[Arvin Meyer MVP]

I have a date stamp field in a table that, by using an AfterUpdate event
attached to a text box in a form, automatically records the date that that
the field populated by that text box is modified. Now I have two
problems:

1) How do I show in the form itself the current contents of the date stamp
field (i.e., the date that the text box was last updated)?

2) How do I stop the date stamp event from firing every time I leave
Design
View in the form?

Thanks in advance!

Bind a textbox to the datestamp, and use the afterupdate event of the
textbox you are modifying to stamp the date/time. It should then fire only
when the textbox changes. You can even make sure that different data is
required before changing the date, like:

If Me.txtWhatever = Me.txtWhatever.OldValue Then
Me.txtDateStamp = Date()
End If

 

[newt]

Thanks Arvin, but I am confused. I need to make sure that when the text box
(let's call it Me.txtNotes) is updated, then (1) the date stamp field in the
table (call it date_updated) is updated, and (2) the date stamp text box on
the form (call it Me.txtStamp) also is updated (i.e., shows the new value in
the date_updated) field.

Does that make sense? Thanks.

 

[Bob Quintal]

Thanks Arvin, but I am confused. I need to make sure that when
the text box (let's call it Me.txtNotes) is updated, then (1) the
date stamp field in the table (call it date_updated) is updated,
and (2) the date stamp text box on the form (call it Me.txtStamp)
also is updated (i.e., shows the new value in the date_updated)
field.

Does that make sense? Thanks.

Yes, it makes sense. And Arvin's answer also makes sense.
To show the datestamp in the form, set its controlsource property to
to the field in the table that holds the date value

To make the date change, you add the following statements to the
After_Update event procedure for the txtnotes box:
If me.txtNotes.value <> me.txtNotes.oldvalue then
me.txtStamp = now()
end if

You can simplify me.txtNotes.value to just me.txtNotes because Access
treats .value as the default property for any textbox.

Q
..

 

[Arvin Meyer MVP]

Make sure that the controlsource of the textbox (txtStamp) is set to the
field on the table. So the code should look something like (untested):

Private Sub txtNotes_AfterUpdate()
If Me.txtNotes = Me.txtNotes.OldValue Then
Me.txtStamp = Date()
End If
End Sub

What that does is to add the date to the txtStamp textbox if the text in
txtNotes is changed. If you start to make a change, but change your mind
while still in the txtNotes textbox, there won't be and date change. You can
use the form's AfterUpdate event instead, if you want to be able to change
your mind at any time during the editing of that record.

BTW, the Me is VBA shortcut for Forms!FormName

 

[newt]

Thanks, but I must be doing something wrong. I did what you said, but when I
change the control source, I get this error: "The expression On Load you
entered as the event property setting produced the following error: Subscript
out of range."

Also, wouldn't I want the afterupdate code to say if . . .<> oldvalue,
rather than if . . . = oldvalue?

Thanks very much - I really appreciate your help.

 

[newt]

Arvin,

I've fixed it now so that the Me.txtStamp field properly displays the
date_notes_updated field of the table. However, I am having trouble actually
populating the date_notes_updated field in the table when a record is updated
with a change in the notes field. That is, I only want the
date_notes_updated field to be populated when the notes field changes, not if
someone just types a change in the form without clicking the button that
updates the record in the table.

I hope that makes sense - I'm kind of new to this. Thanks.