How to Time Stamp entries?

[deko]

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.Modif ied = 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!!

[Peter Russell]

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.Modif ied = 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!!

[deko]

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 ModificationDat e = 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 ModificationDat e 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.RecordsetClo ne
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.b tinternet.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.Modif ied = 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!!

[Pieter Linden]

"deko" <dj****@hotmail .com> wrote in message news:<DA******* **********@news svr27.news.prod igy.com>...

I need to associate a Creation Date and a Modified Date with each entry in
my database. How to do this?

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.

[Peter Russell]

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 ModificationDat e = 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

[deko]

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!E ntry_ID 'get Entry_ID
Me!Entry_ID = lngEid 'put it in txtBox bound to Entry_ID in tblTx
DoCmd.RunComman d 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!D ateModified = 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" <ru***@127.0.0. 1> wrote in message
news:me******** *************** @russellscott.b tinternet.com.. .

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 ModificationDat e = 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

[Peter Russell]

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:

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!E ntry_ID 'get Entry_ID
Me!Entry_ID = lngEid 'put it in txtBox bound to Entry_ID in tblTx
DoCmd.RunComman d 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!D ateModified = 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" <ru***@127.0.0. 1> wrote in message
news:me******** *************** @russellscott.b tinternet.com.. .

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 ModificationDat e = 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

[deko]

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" <ru***@127.0.0. 1> wrote in message
news:me******** *************** @russellscott.b tinternet.com.. .

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:

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!E ntry_ID 'get Entry_ID
Me!Entry_ID = lngEid 'put it in txtBox bound to Entry_ID in tblTx
DoCmd.RunComman d 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!D ateModified = 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" <ru***@127.0.0. 1> wrote in message
news:me******** *************** @russellscott.b tinternet.com.. .

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 ModificationDat e = 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