Date/Time Stamp On Click Event

[ladybug via AccessMonster.com]

Currently I have a form called "frm_routing_new" with a combo box called
"queuecd". Users make a selection and then clicks button "btn_submit." The
On Click Event has the following code:

DoCmd.RunSQL ("INSERT INTO dbo_sds_dir_route (DIRId, QueueCd, OrderNo,
RouteComp) SELECT [FORMS]![frm_routing_new]![DIRId], [FORMS]![frm_routing_new]
![QueueCd], iif(isnull(a.maxOrderNo),0,a.maxOrderNo)+1, ""N"" FROM (SELECT
max(OrderNo) as maxOrderNo FROM dbo_sds_dir_route WHERE DIRId = [FORMS]!
[frm_routing]![DIRId]) a;")
DoCmd.Close
[Forms]![frm_routing]![sfrm_route_work_outs].Requery

Now I have created a new field called "RouteDateCr" to table
"dbo_sds_dir_route." I would like when "btn_submit" is clicked, the current
date and time is entered in table "dbo_sds_dir_route" Can someone help me
modify the code above to make this occur? Thank you in advance!

 

[Jeanette Cunningham]

Hi ladybug,
one easy to do this doesn't use a query at all.
Open the table dbo_sds_dir_route in design view and go to the field
RouteDateCr.
Set the default value for this field to Now()

Every time a new record is added to the table, the current date and time
will automatically appear in RouteDateCr.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Clifford Bass]

Hi Jeanette and ladybug,

One thing to consider: What happens when someone decides to update
that field? Is that okay? Or must it remain the same no matter what? I
presume in SQL Server that you can specify that a user can update only
certain fields of a table or view. Another method, again if possible in SQL
Server, might be to create a before-update trigger on the table that always
makes sure the date/time remains the same, even if sa tries to change it.

Clifford Bass

 

[Jeanette Cunningham]

Hi Clifford,
In my apps I have one field for the record creation date and another for the
record updated date. For some reason I assumed that Ladybug would have a
similar setup.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Clifford Bass]

Hi Jeanette,

There are actually several possible configurations. In some it is just
a last-modified when no one cares about the creation. In others it is both.
And others it is just a creation date. But when a creation date is needed,
one has to ensure that the creation date can ONLY be set automatically and
that it cannot be changed once it has been set. So, the user should not be
able to override the value. Even if one deals with this at the form level,
as long as someone can get to the data in another way, one needs to protect
that value at the table/database level such as through security and/or
triggers.

With an Access table there is an additional problem when using Now()
for the default as it most likely will produce faulty data since the value is
set as soon as a new record is displayed. So if I even just open a form and
it displays a new record on say 8/4/2009 at 10:11 am, and if I then go away
for ten minutes and then come back and start entering information, and
finally save at 10:25 am, the creation date/time will still show 8/4/2009
10:11 am. One might argue between 10:21 am (the start of the data entry) and
10:25 am (the end of the entry) as valid values, but neither would be saved.
With a form, at least the before update event can be used to deal with this.
However, again, if the user can get to the data in some other fashion and can
add rows or modify rows, it needs to be protected at the table/database level.

Just my $0.02 worth of things to think about,

Clifford Bass

 

[Jeanette Cunningham]

I agree, if the absolute time of the creation of that record is critical. I
have yet to write an app where that degree of accuracy is absolutely
demanded and can't be anything less.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Clifford Bass]

Hi Jeanette,

Too true, the need dictates the degree of acceptable accuracy. I deal
with applications with deadlines so a high degree of accuracy with the
creation and/or modification date/times is important.

Clifford Bass