Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2009
    Posts
    7

    Exclamation Unanswered: Automatically inserting current date and time into a field

    Hi

    I'm still learning about Access and have created a Jobs database for our helpdesk. One of the fields is Status which is a combo box that has open and closed options. Another field is called Resolution and I want that field to show the current date and time when I choose closed from the status box.

    I had written some code but I keep getting a runtime error 2465. It says that Microsoft Office Access can't find the field "Closed" referred to in your expression.

    This is the code that I've written.

    Private Sub Status_Change()
    If Me([Status]) = "Closed" Then
    Me([Resolution_ETA]) = Now()

    End If

    End Sub


    It's probably a simple thing for someone who's good with Access but I just can't think what to do next.

    I'd appreciate some help.

    Thanks

    Kathryn

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    If Me.[Status] Like "Closed" Then
    Me.[Resolution_ETA] = Now()
    End If

    is it Resolution_ETA or Resolution ETA ?? down't throw in an underscore if it isn't in the actual name of the field/control

  3. #3
    Join Date
    Jan 2009
    Posts
    7
    Hi

    It's Resolution_ETA.

    Thanks

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    or better yet don't set a value in the form
    but set a default value in the table to now() and set the validation rull to be non null
    then either don't include the control int he update, or set it to NULL
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2009
    Posts
    3
    I have a question some what related to above. Please do not judge my extreme novice abilities. I appreciate the help greatly. I am setting up a form that I want time stamped. I have used the default value as Now(), which works great, except when one goes back to a form, with the subform in it, the current time changes from the time the record was added to the current time and date. I would like a time stamp which does not change. Second issue to that is with the auto number field, it creates a new blank record when the previous one is being added, therefore that time stamp would be invalid because the actual new record is created prematurely. Any suggestions?

    Thanks!!!

  6. #6
    Join Date
    Nov 2009
    Posts
    3
    I believe I spoke too soon, it appears that once certain records are added, it appears to save the current time, and the new records time continues to change until other fields our added. So that is fine. I would like to write a code that also stamps the user id with the current date and time, as well? Any suggestions? If this is an inappropriate place for this question, please advise. Thanks again

  7. #7
    Join Date
    Jan 2009
    Posts
    7
    Thanks for helping me. Finally got it sorted. The error was in the code and it's now been adjusted to read like this:

    Private Sub Status_Change()
    If Me![Status] = "Closed" Then
    Me![ResolutionETA] = Now()

    End If

    End Sub

    Everything works fine now and I'm happy. :-)

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you want the user then you have to choices
    theres the currentuser function wh9hc returns the userid of the person who opened the Access db, if you didn't logonm it will be admin

    OR

    the network logon retrieved from the the operating system. to get that you need to copy some cxode into a common module and call a function. Im pretty certain pkstormy has some code for this in the Access code bank on this forum. however I'd alwasy reccoemd going back to a/the source.. do a goolge ofr Dev Ashish API and you will find a raft of useful functions.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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