Programmatically retrieving field properties

  • Thread starter Thread starter James S. Rice
  • Start date Start date
J

James S. Rice

How do I programmatically retrieve the date that a field
in a record in a recordset was last changed?
 
A recordset is an in memory object.
How long do you keep yours hanging around?

If you mean a table, then I have to ask - do you have a field where you
store that information?
A Date field named Updated or LastUpdated is normal.

Access does not track this information for you.
 
I don't keep the recordset open longer than necessary.

I have a Date field in my table named RecordUpdateDate,
but I currently manually enter a date in this field when I
update any field in the record. Is there a way to set the
field value to the current date automatically whenever any
field in the record changes?
 
Jet does not support triggers so it cannot be done at the datbase level.

You have to ensure users never use tables or queries to change data.
They *have* to use your forms if you want this to work.

Just code the AfterUpdate (or is it BeforeUpdate) event of your form to add
this piece of information "behind the scenes" whenever the record is
modified.

Me![txtRecordUpdated]=Now()
 
Form_BeforeUpdate is right.

HTH,
TC


Joe Fallon said:
Jet does not support triggers so it cannot be done at the datbase level.

You have to ensure users never use tables or queries to change data.
They *have* to use your forms if you want this to work.

Just code the AfterUpdate (or is it BeforeUpdate) event of your form to add
this piece of information "behind the scenes" whenever the record is
modified.

Me![txtRecordUpdated]=Now()
--
Joe Fallon
Access MVP



James S. Rice said:
I don't keep the recordset open longer than necessary.

I have a Date field in my table named RecordUpdateDate,
but I currently manually enter a date in this field when I
update any field in the record. Is there a way to set the
field value to the current date automatically whenever any
field in the record changes?
Click to expand...
Click to expand...
 
Worked perfect. Thank you.
-----Original Message-----
Jet does not support triggers so it cannot be done at the datbase level.

You have to ensure users never use tables or queries to change data.
They *have* to use your forms if you want this to work.

Just code the AfterUpdate (or is it BeforeUpdate) event of your form to add
this piece of information "behind the scenes" whenever the record is
modified.

Me![txtRecordUpdated]=Now()
--
Joe Fallon
Access MVP



I don't keep the recordset open longer than necessary.

I have a Date field in my table named RecordUpdateDate,
but I currently manually enter a date in this field when I
update any field in the record. Is there a way to set the
field value to the current date automatically whenever any
field in the record changes?
Click to expand...


.
Click to expand...
 
Cool!

You are welcome.

Thanks TC for the clarification.
--
Joe Fallon
Access MVP



James S. Rice said:
Worked perfect. Thank you.
-----Original Message-----
Jet does not support triggers so it cannot be done at the datbase level.

You have to ensure users never use tables or queries to change data.
They *have* to use your forms if you want this to work.

Just code the AfterUpdate (or is it BeforeUpdate) event of your form to add
this piece of information "behind the scenes" whenever the record is
modified.

Me![txtRecordUpdated]=Now()
--
Joe Fallon
Access MVP



I don't keep the recordset open longer than necessary.

I have a Date field in my table named RecordUpdateDate,
but I currently manually enter a date in this field when I
update any field in the record. Is there a way to set the
field value to the current date automatically whenever any
field in the record changes?

-----Original Message-----
A recordset is an in memory object.
How long do you keep yours hanging around?

If you mean a table, then I have to ask - do you have a
field where you
store that information?
A Date field named Updated or LastUpdated is normal.

Access does not track this information for you.
--
Joe Fallon
Access MVP



"James S. Rice" <[email protected]>
wrote in message
How do I programmatically retrieve the date that a field
in a record in a recordset was last changed?



.
Click to expand...


.
Click to expand...
Click to expand...
 
"Before update" or "After update": boy that question has caused me huge
heartaches over the years, so say nothing of "On Exit"! And no book I've
ever read or Help I've found has been any help.

I think I can say confidently that if you want to change the contents of
another bound field in a form depending on the contents of the field you're
changing, you should use "After update". "BeforeUpdate" will give you an
error. (i.e., on "AfterUpdate", change the "ModifiedDate" field.)

Kevin
 
The differences are fairly simple.

BeforeUpdate fires *before* the edited value of the field has been accepted
by Access. The purpose of that event is to let you perform your own
validation. If you issue a message then set the Cancel parameter True,
Access keeps the focus in that field, so the user can change the value & try
again.

Some important caveats for BeforeUpdate:
- you can not change the focus from within that event;
- you can not change the value of that field, from within that event;
- you should not do anything in BeforeUpdate, which assumes that the edited
value >is correct<.

AfterUpdate fires *after* your, and Access'es, validations have all been
completed for that field.
- you >can< change the focus from within that event;
- you >can< change the value of that field, from within that event;
- you should use AfterUpdate for things that require that the edited value
is< correct.
Click to expand...

HTH,
TC
 
Kevin Witty said:
"Before update" or "After update": boy that question has caused me
huge heartaches over the years, so say nothing of "On Exit"! And no
book I've ever read or Help I've found has been any help.

I think I can say confidently that if you want to change the contents
of another bound field in a form depending on the contents of the
field you're changing, you should use "After update". "BeforeUpdate"
will give you an error. (i.e., on "AfterUpdate", change the
"ModifiedDate" field.)
Click to expand...

I think this may require clarification. The BeforeUpdate and
AfterUpdate events of a *control* both fire before the BeforeUpdate and
AfterUpdate events of the *form* that contains the control. You can
safely modify the values of bound controls on a form up until the point
when the form's record is saved. That means that you can *always* do
this in the AfterUpdate event of a control or the BeforeUpdate event of
the form; you can *never* do it in the AfterUpdate event of the form;
and you can *generally* do it in the BeforeUpdate event of a control,
but with this exception: you can't change the value of the control
whose BeforeUpdate event is being processed.

In other words, if a control's modified value hasn't yet been saved, you
can't change it; if a form's modified record *has*been saved, you
mustn't try to change it. Doing so will lead to an endless loop of
Form_BeforeUpdate -> record is saved -> Form_AfterUpdate -> record is
dirtied -> Form_BeforeUpdate -> record is saved -> Form_AfterUpdate ->
record is dirtied ...

So if a form's record has a ModifiedDate field that is supposed to
contain the date on which the record was last modified, that field's
value should be set in the form's BeforeUpdate event.
 
Back
Top