Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2004
    Location
    LONDON, Gtr London, England
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Modification date in record (Access 2003)

    Subject edited by HansV - Access wasn't very informative

    I want to have a DATE field in table which ACCESS updates to current date if record has been edited and altered. How can I achive this ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Modification date in record (Access 2003)

    You can create a Date/Time field and set its Default Value property to =Date() if you want the current date, or =Now() if you want the current date and time.
    Whenever you create a new record, the field will be filled in automatically.

    Access does not have triggers to update the field at the table level. You will have to do this from a form based on the table, and prevent users from editing records directly in the table itself (or in a query based on the table). Create a Before Update event procedure for the form:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.ModificationDate = Date ' or Now
    End Sub

    where ModificationDate is the name of the date/time field. Again, use Date for the current date, or Now for the current date and time.

  3. #3
    New Lounger
    Join Date
    Sep 2004
    Location
    LONDON, Gtr London, England
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modification date in record (Access 2003)

    Thank you for your prompt reply. How can one stop users altering data, either directly at table level or via queries.
    As it neccessary to check the dirty property of record that is being edited to see if changes took place?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Modification date in record (Access 2003)

    A first measure to prevent users from opening tables and queries directly is to set options in Tools | Startup:
    - Set a startup form.
    - Hide the database window
    - Other options as desired
    You (and other users!) can bypass the startup options by holding down Shift while opening the database. This is sufficient if you only want to prevent users from accidentally opening a table; it won't keep someone intent on it from doing so.

    For more secure protection, you must apply user-level security. <!profile=WendellB>WendellB<!/profile> has a short tutorial on his website, with many useful links: The Secrets of Security.

    You don't have to check the Dirty property of the form - the Before Update event will only occur if the current record has been modified, not if the user merely moves to another record without having changed anything.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •