hmmm... sounds like a plan... BUT:
1) sometimes I'm in a subform, or popup form, or other form that holds data
associated with the entry in question. This other form does NOT use tblMain
as a RecordSource. So, when a change is made, the Modification Date needs
to be written... the only way I know how to do this is:
DoCmd.RunSql ("UPDATE tblMain SET ModificationDate = Now() WHERE
Entry_ID = " & Me!Entry_ID)
After I do this, there are conflicts when I go back to frmMain (The form
that DOES use tblMain as a RecordSource) and try to make additions/edits
AFTER writing the Modification Date to the underlying table.
Do I need to requery the main form every time the ModificationDate is
changed? If so, This means I have to also move frmMain back to the record
it was on before the write (or else the user has to manually go back).
Here's how I do it in code:
Dim rst as DAO.recordset
Set rst = Me.RecordsetClone
rst.FindFirst ("Entry_ID=" & lngEid)
Me.Bookmark = rst.Bookmark
rst.Close
Set rst = Nothing
(does this code look okay, by the way?)
2) ALSO -- if frmMain is Dirty, then does it have to be requeried BEFORE the
Modification Date is written? I've gotten errors when this is the case...
3) ALSO -- what about the size of tblMain? Will all these dates cause it to
grow too fast? Wouldn't it be better to have all the dates in a separate
table -- AND would this avoid the conflicts and requery/move-back issues
mentioned above??
Thanks again for the reply and advice!
"Peter Russell" <ru***@127.0.0.1> wrote in message
news:me***********************@russellscott.btinte rnet.com...
I don't believe you have any choices here.
You have to run the code in every place where you allow updates to take
place.
But I can see no point at all in making it so difficult by putting the
fields into a separate table.
If the fields were in the same table as the data then
the creation date could be entered automatically with a default value of
Now. You would just need a me.lastmodified = Now statement in the form's
beforeupdate event wherever you allow changes.
Regards
Peter Russell
deko previously wrote:
I need to associate a Creation Date and a Modified Date with each entry
in
my database. How to do this?
Here's what I've tried:
1) Use a separate table (tblUpdate) with Created, Modified, and
Entry_ID
fields that has a One-To-Many relationship with the main table (tblMain
-
the "one" side of the relationship) which holds all the names,
addresses,
etc.
2) Have an update query run every time an entry is added, or modified:
UPDATE tblUpdate SET tblUpdate.Modified = Now()
WHERE (("Entry_ID"=[Forms]![frmMain]![frmCn].[Form]![Entry_ID]));
But how do I get every Entry_ID from tblMain into tblUpdate? Do I have
to
write it every time? What if the Entry_ID does not yet exist? Does
that
mean I also need an Append Query for new entries in tblMain?
If possible, I want to avoid having any form associated with tblUpdate
because I'll be writing to it from several different forms and subforms
within the database. Does this mean I'll be updating it exclusively
with
SQL statements rather than Jet? Is there a performance penalty for
doing
this?
Has anyone done this before? Is there a better way?
Thanks in advance!!