Results 1 to 5 of 5
  1. #1
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136

    How do I code a "Last Modified Date" control in form?


    Hi all,
    I have an Access form linked to a SQL table with a field I'd like to populate with the time and date of the last time the record was saved/edited. I have googled it, but I've been unable to get it to work. Does anyone know how to code this?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I have googled it, but I've been unable to get it to work.
    What have you tried?

    I would expect something like this in the form beforeupdate event would work

    me.timestamp=now()

    If this doesn't work, you need to provide a detailed explanation of the relevant tables, forms and and what you mean by a 'SQL table'

  3. #3
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    Quote Originally Posted by CJ_London View Post
    What have you tried?

    I would expect something like this in the form beforeupdate event would work

    me.timestamp=now()

    If this doesn't work, you need to provide a detailed explanation of the relevant tables, forms and and what you mean by a 'SQL table'
    I will try my best. I have an Access form with a field named Last_Modified. I want this field to update to the current date/time every time a record is created and every time a record is changed. This form is populated by a query I built that uses a SQL table and is sorted by Last_Modified. The field in the SQL table (Last_Modified) is set up like below.

    It may have something do with the fact that it's a form/sub-form relationship. The main form has an unbound box with a list of products that populates the sub-form.


    Click image for larger version. 

Name:	download.png 
Views:	10 
Size:	25.4 KB 
ID:	49122


  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    can't really help unless you provide the other information requested

  5. #5
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    The SQL server setting for a default value will automatically set the date and time for any NEW records, but that is all it does, it doesn't affect edited records.

    You would need to do that in the forms before update as per CJ's advice, or (and I don't advise doing this) you could set a trigger on the table in SQL to set the field on Update.
    Triggers are not the best answer to be honest. They can cause more grief than they solve.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-14-2019, 11:30 AM
  2. Replies: 2
    Last Post: 12-23-2015, 09:32 PM
  3. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  4. Replies: 3
    Last Post: 12-06-2014, 03:59 AM
  5. Replies: 5
    Last Post: 09-05-2014, 12:59 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums