Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Thumbs up Unanswered: Store date & time of when Database changes where made??

    I have a basic database with 2 main tables with various queries and reports comming off them.

    Is it possible to have some code or expression on the database front end form to show when any data was last entered,altered or deleted? Just a basic date and time will do so I can keep track of things. I dont really need info of what it was that I did.

    I have already got the database to record the last time it was shutdown, by storing the date and time into a third table on exit, but I really need some code to track when any changes were made to the data and say when it was done.

    Any ideas would be greatly appreciated!

    Tom
    Last edited by moss2076; 09-27-04 at 12:49.

  2. #2
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Hi Tom,

    I have an old database that had a macro to do the date stamping for a modified record. What I did was as a field to the table called DateModified and set it to Date/Time.
    Place that in your query and later to your form.
    Now in DesignView of the Form, NOT THE CONTROL. Go to the Form Properties and click on BeforeUpdate. Go to the MacroBuilder and create this macro:

    MacroName = Form_BeforeUpdate
    Action = SetValue
    Below in the Arguments place this in Item: [DateModified] Which is the name of the new control (textbox) that you placed on the form.
    In the Expression part of the argument place this: Date()

    Save it and close it and you're done. Now, each time a field is changed in your form that Date and Time will change also. You might want to ReQuery the form also. Oh, for the mentioning, you can also take that Macro and Convert it to Code which will be in a Function. I have since gotten away from Macros in favor of VBA Code. There is a Wizard in Access to convert Macros to Code.

    hope this works for you
    have a nice one,
    Bud

  3. #3
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Hi Tom,

    A variation on Bud's solution that avoids macros is to add, as Bud suggests, a column to the table of type date. Include a bound text box on your form for this new column but make it invisible.

    Then again as the BeforeUpdate event handler for the form simply code:

    Me.DateModified = Now()

    Voila (assuming your text box is named DateModified and your system clock is correct)!

    This works well for forms bound to a single table. When a form is bound to a query that joins many tables you must determine how many dates you want and which tables are to be date stamped.
    Rod

    fe_rod@hotmail.com

  4. #4
    Join Date
    Aug 2004
    Posts
    364
    Thanks guys, that seems to work a treat.

    However, if I uncheck a checkbox on one record, then scroll through the other records in the table, it is datestamping all the other records with unchecked checkboxes - even though I havent modified them!


    Any way round this because im getting modified stamps when nothings been modified!
    Last edited by moss2076; 09-26-04 at 15:01.

  5. #5
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by moss2076
    Thanks guys, that seems to work a treat.

    However, if I uncheck a checkbox on one record, then scroll through the other records in the table, it is datestamping all the other records with unchecked checkboxes - even though I havent modified them!


    Any way round this because im getting modified stamps when nothings been modified!
    Hi again and was glad to help. However, the situation that you have now I really have no idea why that is happening. I just checked my program and whenever I make a change in my form, it only changes the one record that I am on. How do you actually have the DateModified field set up in your Table and Form???

    Bud

  6. #6
    Join Date
    Aug 2004
    Posts
    364
    Ive managed to sort it. I put the Macro event in the After Update. Its working a treat now!!

    Many thanks

    Tom

  7. #7
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by moss2076
    Ive managed to sort it. I put the Macro event in the After Update. Its working a treat now!!

    Many thanks

    Tom
    You're welcome a plenty, just glad to be able to help some.

    have a nice one,
    Bud

Posting Permissions

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