Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto enter date in a field (Access 97 Sr2)

    How would I do this? The user wants to have a date field in his form that will automatically put the current date in the field. I know how to insert the current date on a form, but what this guy wants is to have a field with the current date in it.

    For example, he opens the database and every record he creates during that session has the current date in the date field. When he closes the form, the current date for those records is in the base table, but they turn into a static date after he closes the database. He will enter approximately 500 records every time he opens the database, and he doesn't want to have to type the date every time. But, he needs the date to be a static date so he can do calculations in queries.

    I am very very clueless when it comes to code, so if you have code suggestions, please tell me what to do with it.

    Thanks.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Auto enter date in a field (Access 97 Sr2)

    I think what you want to do is set a default value for a field in a table. You can use either Date() or Now() to get just the date, and the date and time respectively. All you have to do is put it in the Default Value property for the field (in the table design view). If records are always put in using a form, you can set the default value for a control that is not visible to Date or Now, and bind the control to the date field. In either case, the value is put into the table and stays there. We use that approach in nearly all tables we design, and we usually capture who put the data in with CurrentUser(). We also often capture the date/time when a record was last edited, and who edited it. (If your tables are in an ADP or you are using SQL Server the functions to get the current date/time and user are a little different - post and I'll give you those function names). Hope this makes it easier - and you don't need to worry about code unless you want to capture the Edit info.
    Wendell

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Auto enter date in a field (Access 97 Sr2)

    By putting either Date() or Now() as the default value for this field in the table, the field is filled whenever a new record is created. YOu don't have to even bother with a control on a form (unless you want to view the information).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto enter date in a field (Access 97 Sr2)

    Yes! That's what I want! Thanks.

  5. #5
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto enter date in a field (Access 97 Sr2)

    A slight twist on Melanie's situation:

    How do I set the default to the Date in "the previous record"? I tried setting the default for the field in the OnExit event, but that didnt work.

  6. #6
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto enter date in a field (Access 97 Sr2)

    For this sort of thing I use the afterUpdate event of the control to write the control value to the Tag property of the control and then in the new record I populate from the tag values. I normally use a hot key to do the triggering, as we don't need the same details every record but when we do there are usually 6/7 common fields to fill in

    HTH

    Peter

  7. #7
    New Lounger
    Join Date
    Apr 2002
    Location
    California
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto enter date in a field (Access 97 Sr2)

    You mention adding something to indicate the date when a record was last amended in Access 97. I know about date() but that only seems useful for when a particular record was created. How can I setup a field to show when a record was last amended?

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

    Re: Auto enter date in a field (Access 97 Sr2)

    Add a field to your table to hold the "last amended" date. I'll call the field DateAmended for demonstration purposes.
    (If you also want to keep track of the user who last amended the record, add a field for the user name, for instance UserAmended)

    You need to use a form to set the last amended date (and/or user). Make sure that DateAmended is in the recordsource of this form.
    Add code to the AfterUpdate event of the form:

    Private Sub Form_AfterUpdate()
    DateAmended = Date
    ' Any other code you want goes here
    End Sub

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Auto enter date in a field (Access 97 Sr2)

    HansV has it right if your tables are stored in Access - you need to use a form. In SQL Server (or an ADP using the MSDE), you can use a trigger to do the same thing whether the record is modified on a form, by a query/view or directly from the table view.
    Wendell

  10. #10
    New Lounger
    Join Date
    Apr 2002
    Location
    California
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto enter date in a field (Access 97 Sr2)

    Hi
    That worked fine. Many thanks for the help

  11. #11
    New Lounger
    Join Date
    Apr 2002
    Location
    California
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto enter date in a field (Access 97 Sr2)

    Actually, I was incorrect. The =Now() only seems to input the DTG into the Amended field when creating a new record NOT after changing something on an existing record. I already have a field for the date the record was created. What I need is a field to show when the last update/change/modification was made to an existing record. I have the field already setup in the master table with the name "Amended". I have that field in the form I'm using. I just can't figure out the coding to make it work correctly. I've tried various things in the After Update (and Before Update and On Change) but nothing I've tried seems to work.

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto enter date in a field (Access 97 Sr2)

    It has to be in the before update event of the form.
    The field has to be on the form, eventually set the Visible property to False.
    <pre>Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.Amended = Now()
    End Sub</pre>

    You have to do it in every form changing the data. Data changed in Table view or Query view can not bee triggered
    Francois

  13. #13
    New Lounger
    Join Date
    Apr 2002
    Location
    California
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto enter date in a field (Access 97 Sr2)

    Hi again. I just realized the reason it doesn't work is my own stupidity. Somehow I imagined that putting the code in the "Amended" field would do the trick. Of course, it won't. When I enter the code into each record I want to track, it works fine. My apologies for my stupidity in not realizing this from the get-go.

Posting Permissions

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