Results 1 to 13 of 13
  1. #1
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    247

    Date and Time Stamp Question

    I have a form that users fill in data. I want to add a date, time, and user field to the table so I know when it was last modified and by who. I need the time stamp due to split shifts. Would it be better to code it into VBA for form or put Date() and Time() into table default properties? Also if the record gets modified will it change the info in these fields to current time and date?


    Thank you for your help.



    Walker

  2. #2
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,949
    This is usually done at the Form-level in the Form_BeforeUpdate event, which fires anytime a new or edited Record is saved. If you used the Default Value, at the Table level, it would only apply to a New Record, and you'd still have to do it at the Form-level, in order to change it for an edited Record.

    BTW, if you use Now(), you'll get both the Date and the Time element in a single field.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    247
    Ling,
    Thank you for the reply. I definitely don't want to have to change it in both places so I will just make a BeforeUpdate event on the form.
    Would it be better to separate them if I wanted to do queries based upon date modified? Or is it easy to pull just the dates out of the Now()

    Thank you,
    Walker

  4. #4
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,949
    Not really! You use Date() to pull the date component. The fact is, if it's defined as a DateTime field, both Date and Time components are actually stored, in the underlying Table, whether you use Date() or Now() to populate the Field! If you use Date() to populate a Field...the Date part is stored with a Time component of Midnight.

    Some developers like to always use Now(), on the off chance that the need for knowing the Time part may arise, in the future, but I've never felt the need.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    247
    Oh ok Thank you that's good to know. I think I will use Now on this one. I had already made 2 fields a time and a date but I will fix that to just a single field. Thank you very much for your help.

  6. #6
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,949
    Glad we could help!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    247
    One more quick question on this please. If I change the table default to Now() in the DateAdded Field is that going to add DateTime stamp for all new records without having to go and recopy the FE to all users machines. I am thinking this will happen and I can add the form controls on the next Rev. I am currently the only person that edits previous data.

  8. #8
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,949
    Quote Originally Posted by NightWalker View Post

    ...If I change the table default to Now() in the DateAdded Field is that going to add DateTime stamp for all new records without having to go and recopy the FE to all users machines.
    Sure...if it's done at the Table-level, it'll be there without anything being done to the Front End...yours or those of your users.

    Quote Originally Posted by NightWalker View Post

    ...I am thinking this will happen and I can add the form controls on the next Rev. I am currently the only person that edits previous data.
    Makes sense, as long as you make the next revision before anyone else starts sharing the editing duty!

    BTW, here's a hack for the easy distribution of Front Ends:

    Auto Front End Updater

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    247
    I really wish I could use that auto updater. We are restricted by the IT department on what software we are allowed to have. We don't have rights to make any changes to the software on our machines. Corporate and IT decide what software is acceptable and that is all we are allowed to have. Makes getting job completed harder sometimes. The way it is right now I have to go to each user have them login and delete their previous copy then put a new copy onto their system. For this reason I try to minimize how many revisions I make to the whole database. Thank you for all your help.

  10. #10
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,949
    Quote Originally Posted by NightWalker View Post

    ...We are restricted by the IT department on what software we are allowed to have...
    The sad thing about that is that the IT/Network Admins almost never have a clue about MS Access, what it requires or how it works! There are hacks out there that can facilitate FE replacement entirely from within Access, but they are somewhat complicated.

    Basically, it involves

    1. Placing a copy of the latest version of the db on a shared drive
    2. Having the database check, each time it's opened, to see if the version on the shared drive is a later version than the one being currently run by the user
    3. If it is a newer version, copying it to the user's machine, deleting the current version

    If you're interested, let me know, and I'll try to dig up a copy of the directions this weekend (busy most of today with family business) and post them.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  11. #11
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    247
    I am very interested. Whenever you have time. Thank you.

  12. #12
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,949
    Sorry to be so long getting back to this...family business ended in a funeral.

    Go to Managing Front-End Databases on Each Desktop

    here

    http://www.fmsinc.com/MicrosoftAcces...ter/Index.html

    Or for a number of suggestions go to

    Distribute the front-end database

    here

    https://support.office.com/en-us/art...B1D73498CC#bm4

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  13. #13
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    247
    Sorry to hear about your family. I was in no hurry anyway. I am just thankful for your help. I really appreciate it.

    Walker

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

Similar Threads

  1. Stamp date, time and user?
    By ricanos in forum Modules
    Replies: 1
    Last Post: 04-06-2016, 08:41 AM
  2. Replies: 1
    Last Post: 04-01-2015, 01:29 AM
  3. Date and Time Stamp
    By zoooza84 in forum Access
    Replies: 2
    Last Post: 08-06-2011, 04:53 AM
  4. Question about Button and Time Stamp
    By euroclyde in forum Access
    Replies: 2
    Last Post: 06-16-2010, 09:01 AM
  5. Replies: 4
    Last Post: 01-30-2010, 05:22 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 - Senior Forums