date/time last updated field

  • Thread starter Thread starter KumbiaKid
  • Start date Start date
K

KumbiaKid

I'd like to have a field in a table that records the date/time that the
record was last updated. I'd like this field to be automatically updated
whenever any other field in the table is changed (or a new record is created
-- that part is easy). Any way I can do that in the table? I'd prefer to do
it there so I don't have to repeat code for different forms or queries that
allow updates to the table. I'm using Access 2000, but may be able to use
Access 2003 and I'll soon be updating to Access 2007, so if there's a
solution that works with all of these versions, that would be ideal.
 
No. Access lacks triggers, so you cannot do that at the table level.

You can do it at the form level. Use the BeforeUpdate event procedure of the
form.
 
No. Access lacks triggers, so you cannot do that at the table level.
Click to expand...

Did you mean to say, "Jet lacks triggers..."?

At the very least the OP should put a validation rule in the table to
ensure the 'timestamp' is maintained e.g. column-level rule

= NOW()

Jamie.

--
 
As Allen says, this must be done at the form level, but there is very little
code to duplicate across multiple forms. Place a control (e.g. textbox) on
each form, make its data source the timestamp field of your table. You could
make this hidden if users are not to see the timestamp - if visible, it
should be locked. The BeforeUpdate event is one line of code:
txtboxTimeStamp=Now()

For queries that update this table, insert TimeStamp=Now() into the SET
clause of each query.
 
You are accurate, but a bit nit picky. I am sure Allen said Access to avoid
confusing the OP. You would be surprised at how many users do not understand
the distinction between Access and Jet.

Should we educate? Sure, but it takes baby steps. As in teaching music, you
don't introduce a E# min Add 9 to someone who barely understands a C major
code.
Yes, I said E#. Yes it is really F, but it is taught as a reading exercise.
 
You are accurate, but a bit nit picky. I am sure Allen said Access to avoid
confusing the OP.
Click to expand...

It's more about being specific (do you really think the OP would have
been confused by the use of the term 'Jet'?)

I considered my point worthy because in a sense Access has triggers
(i.e. Form events) whereas Jet does not.

Jamie.

--
 
Thanks all. Although I'm an old timer at database design, I'm very green at
this sort of coding and at application development in Access/VB etc. I've
created an event procedure for the BeforeUpdate event with the following code
(the name of the field is DateLastModified):

Private Sub Form_BeforeUpdate(Cancel As Integer)
DateLastModified = Now()
End Sub

Visual Basic started with "Option Compare Database" above the code section
-- I don't know what that's about, but presume it should be there.

This makes no change in the DateLastModified field when I make a change in
another field. What am I missing?
 
Thanks all. Although I'm well experienced in database design, I'm really a
newbie at application development using Access/VB, etc. I've tried to code an
Event Procedure in a form using TedMi's suggestion. The textbox control is
already a part of the form. The name of the field is DateLastModified and
I've put the following code in an Event Procedure attached to the Before
Update Event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
DateLastModified = Now()
End Sub

There's a line above the code section in the VB window that says "Option
Compare Database" which I presume should be there. When I make a change in
another field in the form, no errors are generated but the DateLastModified
field is not updated. What am I missing?
 
Thanks all. My reply posts seem to not be making it to the discussion, so I
hope this one does. I guess I was in the wrong group (should have been in
Forms Coding where I found the full solution).
 
Thanks all. With a little more help on the exact code from the Forms Coding
group I have what I need. Your assistance is appreciated.
 
in teaching music, you
don't introduce a E# min Add 9 to someone who barely understands a C major
code.
Click to expand...

I can't decide whether you meant "a C major chord" or "C# code" :)

PS do a youtube search for Gilmour French and Saunders.

Jamie.

--
 
--
Dave Hargis, Microsoft Access MVP


Jamie Collins said:
It's more about being specific (do you really think the OP would have
been confused by the use of the term 'Jet'?)
Click to expand...

Very possibly.
I considered my point worthy because in a sense Access has triggers
(i.e. Form events) whereas Jet does not.
Click to expand...

Never thought of a form event as a trigger, but I do see the correlation.
Interesting point worthy of thought.
 
Klatuu said:
C flat is a half step down and is the same as B.
Click to expand...

<picky>
Only in even temperament. In pure tuning, C flat is slightly lower in pitch
than B.
</picky>
 
Doh!
Why didn't I get that?
Actually, very funny.
But then would anyone understand if I said Db.Net
(Where b = Flat)
 
<picky>
Only in even temperament. In pure tuning, C flat is slightly lower in pitch
than B.
</picky>
Click to expand...

Don't be sharp!
Don't be flat!
Just be natural.

John W. Vinson [MVP]
 
Back
Top