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


"ladybug via AccessMonster.com" <u21071@uwe> wrote in message
news:9a0e50394c940@uwe...
> 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!
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...oding/200908/1
>

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" wrote:

> 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

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" <(E-Mail Removed)> wrote in message
news:BD2F892F-9387-4A55-ADBD-(E-Mail Removed)...
> 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" wrote:
>
>> 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

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" wrote:

> 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

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" <(E-Mail Removed)> wrote in message
newsD582DD7-B8C3-49D0-B437-(E-Mail Removed)...
> 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" wrote:
>
>> 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

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

"Jeanette Cunningham" wrote:

> 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