Re: Date/Time Stamp On Click Event

Discussion in 'Microsoft Access Form Coding' started by Jeanette Cunningham, Aug 3, 2009.

  1. 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/Forums.aspx/access-formscoding/200908/1
    >
     
    Jeanette Cunningham, Aug 3, 2009
    #1
    1. Advertisements

  2. 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
     
    Clifford Bass, Aug 3, 2009
    #2
    1. Advertisements

  3. 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" <> wrote in message
    news:...
    > 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
     
    Jeanette Cunningham, Aug 4, 2009
    #3
  4. 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
     
    Clifford Bass, Aug 4, 2009
    #4
  5. 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" <> wrote in message
    news:D...
    > 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
     
    Jeanette Cunningham, Aug 5, 2009
    #5
  6. 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
     
    Clifford Bass, Aug 5, 2009
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. david

    date time stamp a memo field

    david, Sep 16, 2003, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    390
    Kevin
    Sep 16, 2003
  2. Brian

    Date/Time Stamp

    Brian, Jan 14, 2004, in forum: Microsoft Access Form Coding
    Replies:
    1
    Views:
    165
    Sandra Daigle
    Jan 14, 2004
  3. Joe

    File date time stamp

    Joe, Apr 9, 2004, in forum: Microsoft Access Form Coding
    Replies:
    4
    Views:
    584
  4. Guest

    Access Time Stamp for Date of Modification

    Guest, Jan 11, 2006, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    170
    Guest
    Jan 11, 2006
  5. John W. Vinson

    Re: return to first button click event after another button click event

    John W. Vinson, Dec 4, 2009, in forum: Microsoft Access Form Coding
    Replies:
    0
    Views:
    259
    John W. Vinson
    Dec 4, 2009
Loading...

Share This Page