How to Time Stamp entries?

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!!

re: How to Time Stamp entries?

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:
[color=blue]
> 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!!
>
>
>[/color]

re: How to Time Stamp entries?

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" <rusty@127.0.0.1> wrote in message
news:memo.20031017085545.1456A@russellscott.btinte rnet.com...[color=blue]
> 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:
>[color=green]
> > 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!!
> >
> >
> >[/color]
>[/color]

re: How to Time Stamp entries?

"deko" <dje422@hotmail.com> wrote in message news:<DALjb.1638$6V6.1395@newssvr27.news.prodigy.c om>...[color=blue]
> I need to associate a Creation Date and a Modified Date with each entry in
> my database. How to do this?[/color]

RTFM.
Creation Date: Default Value (table level) = Date() or Now()
Modified Date: Set in the BeforeUpdate event of a form... can't be
done at table level.

re: How to Time Stamp entries?

deko previously wrote:
[color=blue]
> 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)[/color]

You've lost me.

How can you be changing (through the UI) a field in tblMain if the form is
not based on that table? And if you're not changing a record in tblMain
why would you want to update its modification date?

Regards

Peter Russell

re: How to Time Stamp entries?

good question.

I have a table (tblTxJournal) which holds transactions for each Entry. Each
Entry's Entry_ID is in tblTxJournal (I think this is called a Foreign Key?)
with a One-To-Many relationship with tblMain (tblMain is the "one" side).
The UI is a tabbed interface, so when the user goes to enter/add a
transaction on frmTx, frmMain remains open, and the Entry_ID is pulled from
frmMain when editing/saving a transaction:

Private Sub cmdRecord_Click()
lngEid = Forms!frmMain!Entry_ID 'get Entry_ID
Me!Entry_ID = lngEid 'put it in txtBox bound to Entry_ID in tblTx
DoCmd.RunCommand acCmdSaveRecord
End Sub

Whenever an Entry's transaction is modified/added, the DateModified field
(in tblMain) needs to be updated. One way to do this would be adding this
line to the above sub on frmTx:

Forms!frmMain!DateModified = Now

This would set the current date on frmMain (and the underlying RecordSource
tblMain) -- without the user actually being in that form.

But problems arise when the user goes back to frmMain and makes changes --
or goes to frmTx while frmMain is Dirty. This is why I'm thinking a
separate table might be best -- simply to avoid problems.

UPDATE tblUtility SET Modified = Now()
WHERE Entry_ID=Forms!Tx!Entity_ID;

What do you think?


"Peter Russell" <rusty@127.0.0.1> wrote in message
news:memo.20031017143048.1456E@russellscott.btinte rnet.com...[color=blue]
> deko previously wrote:
>[color=green]
> > 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)[/color]
>
> You've lost me.
>
> How can you be changing (through the UI) a field in tblMain if the form is
> not based on that table? And if you're not changing a record in tblMain
> why would you want to update its modification date?
>
> Regards
>
> Peter Russell
>
>
>[/color]

re: How to Time Stamp entries?

I don't know if I'm getting somewhere near it yet.

My understanding now is that you have a main table and a transactions
table.

The transaction are all timestamped.

What you want is the form for the main table to display the latest
timestamp on any related transaction.

If this is the case then don't keep the timestamp data on the main table
at all. Just look it up using DMax. You can requery the field after each
transaction.

Am I missing the point here?

Regards

Peter Russell






deko previously wrote:
[color=blue]
> good question.
>
> I have a table (tblTxJournal) which holds transactions for each Entry.
> Each
> Entry's Entry_ID is in tblTxJournal (I think this is called a Foreign
> Key?)
> with a One-To-Many relationship with tblMain (tblMain is the "one"
> side).
> The UI is a tabbed interface, so when the user goes to enter/add a
> transaction on frmTx, frmMain remains open, and the Entry_ID is pulled
> from
> frmMain when editing/saving a transaction:
>
> Private Sub cmdRecord_Click()
> lngEid = Forms!frmMain!Entry_ID 'get Entry_ID
> Me!Entry_ID = lngEid 'put it in txtBox bound to Entry_ID in tblTx
> DoCmd.RunCommand acCmdSaveRecord
> End Sub
>
> Whenever an Entry's transaction is modified/added, the DateModified
> field
> (in tblMain) needs to be updated. One way to do this would be adding
> this
> line to the above sub on frmTx:
>
> Forms!frmMain!DateModified = Now
>
> This would set the current date on frmMain (and the underlying
> RecordSource
> tblMain) -- without the user actually being in that form.
>
> But problems arise when the user goes back to frmMain and makes changes
> -- or goes to frmTx while frmMain is Dirty. This is why I'm thinking a
> separate table might be best -- simply to avoid problems.
>
> UPDATE tblUtility SET Modified = Now()
> WHERE Entry_ID=Forms!Tx!Entity_ID;
>
> What do you think?
>
>
> "Peter Russell" <rusty@127.0.0.1> wrote in message
> news:memo.20031017143048.1456E@russellscott.btinte rnet.com...[color=green]
> > deko previously wrote:
> >[color=darkred]
> > > 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)[/color]
> >
> > You've lost me.
> >
> > How can you be changing (through the UI) a field in tblMain if the
> > form is
> > not based on that table? And if you're not changing a record in
> > tblMain
> > why would you want to update its modification date?
> >
> > Regards
> >
> > Peter Russell
> >
> >
> >[/color]
>
>
>[/color]

re: How to Time Stamp entries?

well, I've prototyping the separate table solution and it seems to be
working.

what I have is an append query that inserts creation date in tblUtility for
new entries, and an Update query that writes modification dates to that same
table. The key was to know where to fire off the Update query -- your
suggestion of on BeforeUpdate is a winner.

I have many tables that are all linked on Entry_ID with many forms that use
these tables as recordsources. The solution I've designed will allow me to
update the modification date for a particular Entry when related data for
that Entry -- in any of the related tables -- is changed. And because
tblUtility is not used as a recordsource for any form, I don't have to worry
about write conflicts and other problems that I've experienced when I tried
to use tblMain for this.

thanks for the time on DMax... may come in handy elsewhere

appreciate your comments very much!!


"Peter Russell" <rusty@127.0.0.1> wrote in message
news:memo.20031017164713.1456F@russellscott.btinte rnet.com...[color=blue]
> I don't know if I'm getting somewhere near it yet.
>
> My understanding now is that you have a main table and a transactions
> table.
>
> The transaction are all timestamped.
>
> What you want is the form for the main table to display the latest
> timestamp on any related transaction.
>
> If this is the case then don't keep the timestamp data on the main table
> at all. Just look it up using DMax. You can requery the field after each
> transaction.
>
> Am I missing the point here?
>
> Regards
>
> Peter Russell
>
>
>
>
>
>
> deko previously wrote:
>[color=green]
> > good question.
> >
> > I have a table (tblTxJournal) which holds transactions for each Entry.
> > Each
> > Entry's Entry_ID is in tblTxJournal (I think this is called a Foreign
> > Key?)
> > with a One-To-Many relationship with tblMain (tblMain is the "one"
> > side).
> > The UI is a tabbed interface, so when the user goes to enter/add a
> > transaction on frmTx, frmMain remains open, and the Entry_ID is pulled
> > from
> > frmMain when editing/saving a transaction:
> >
> > Private Sub cmdRecord_Click()
> > lngEid = Forms!frmMain!Entry_ID 'get Entry_ID
> > Me!Entry_ID = lngEid 'put it in txtBox bound to Entry_ID in tblTx
> > DoCmd.RunCommand acCmdSaveRecord
> > End Sub
> >
> > Whenever an Entry's transaction is modified/added, the DateModified
> > field
> > (in tblMain) needs to be updated. One way to do this would be adding
> > this
> > line to the above sub on frmTx:
> >
> > Forms!frmMain!DateModified = Now
> >
> > This would set the current date on frmMain (and the underlying
> > RecordSource
> > tblMain) -- without the user actually being in that form.
> >
> > But problems arise when the user goes back to frmMain and makes changes
> > -- or goes to frmTx while frmMain is Dirty. This is why I'm thinking a
> > separate table might be best -- simply to avoid problems.
> >
> > UPDATE tblUtility SET Modified = Now()
> > WHERE Entry_ID=Forms!Tx!Entity_ID;
> >
> > What do you think?
> >
> >
> > "Peter Russell" <rusty@127.0.0.1> wrote in message
> > news:memo.20031017143048.1456E@russellscott.btinte rnet.com...[color=darkred]
> > > deko previously wrote:
> > >
> > > > 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)
> > >
> > > You've lost me.
> > >
> > > How can you be changing (through the UI) a field in tblMain if the
> > > form is
> > > not based on that table? And if you're not changing a record in
> > > tblMain
> > > why would you want to update its modification date?
> > >
> > > Regards
> > >
> > > Peter Russell
> > >
> > >
> > >[/color]
> >
> >
> >[/color]
>[/color]