Results 1 to 9 of 9
  1. #1
    jmi is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2018
    Posts
    4

    On change or After update

    Hi Everyone,



    I have a form that has fields for contracts that my company has sold. You can sift through these contracts by pressing the over arrow and the form populates with the correct values for the next contract. We can then change fields and save it to the database with the latest values.

    One of the fields in the form is "Status" where there a dropdown to chose from. I want to auto-populate another field called "Status Date" to today's date whenever someone changes the "Status" field for the contract they are viewing. So for example if the current contract you're looking at has a "Status" of "On Hold" with a date of 4/23/2018, but I want to change it to "Canceled", how can I get the "Status date" to auto-populate with today's date? I don't want today's date to auto-populate when sifting through the contracts while pressing the over key though. How can I do this?

    Thanks!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Don't know what 'over arrow/key' is.

    Maybe simply in AfterUpdate event:
    Me.StatusDate = Date()

    You don't need to retain the previous status and dates?

    Advise not to use spaces in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jmi is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2018
    Posts
    4
    Quote Originally Posted by June7 View Post
    Don't know what 'over arrow/key' is.

    Maybe simply in AfterUpdate event:
    Me.StatusDate = Date()

    You don't need to retain the previous status and dates?

    Advise not to use spaces in naming convention.
    Thanks for the reply June7!

    The name is actual StatusDate, so no spaces and no don't need to retain the old status or date. Just need to record the new status and the date it was changed which will always be the date of today.

    When I put "Me.StatusDate = Date()" in After Update in the Status property sheet, and I go into the form and try to change the status of one of the contracts I get the following error:

    "Microsoft Access cannot find the object 'Me.'

    Of 'Me' is a new macro or macro group, make sure you have saved it and that you have typed its name correctly."

    Any idea how to fix that?

    Thanks again!

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Have you the control linked to field [StatusDate] also named StatusDate (Access gives this name by default). I'm not sure this is the cause of problem, and I haven't enough time at moment to check, but anyway is it the good practice to have for controls and fields different names (e.g. rename the field txtStatusDate, and then try
    Code:
    Me.txtStatusDate = Date()

  5. #5
    jmi is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2018
    Posts
    4
    Quote Originally Posted by ArviLaanemets View Post
    Have you the control linked to field [StatusDate] also named StatusDate (Access gives this name by default). I'm not sure this is the cause of problem, and I haven't enough time at moment to check, but anyway is it the good practice to have for controls and fields different names (e.g. rename the field txtStatusDate, and then try
    Code:
    Me.txtStatusDate = Date()
    I'm not sure what you mean. Under Data in the property sheet, the 'Control Source' says Status Date and under Other in the property sheet, the 'Name' says StatusDate

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The code doesn't go on the property sheet:

    http://www.baldyweb.com/FirstVBA.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    jmi is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2018
    Posts
    4
    Quote Originally Posted by pbaldy View Post
    The code doesn't go on the property sheet:

    http://www.baldyweb.com/FirstVBA.htm
    Wow! That was easy. Thanks so much everyone!

  8. #8
    Dehn0045 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    11
    As a fellow novice, take my suggestion with a grain of salt. If somebody mistakenly clicks on the Status dropdown for an item that is already "Closed" but they leave it as "Closed" you probably don't want the date to update. You could do a simple check of the OldValue to determine whether or not to execute update of the date. Also, once an item is closed, you might want to put in some barriers to changing the status to something else, but this will depend on your specific needs.
    Code:
    Private Sub Combo13_AfterUpdate()    
    
        If Me.Combo13.OldValue = "Closed" Then 
            Exit Sub 
        End If
        
        If Me.Combo13.Value = "Closed" Then
            Me.statusdate.Value = Date
        End If
    
    End Sub

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    If Control Source has Status Date - with space - then you are using spaces in naming convention of fields. Advise not to.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Change content with update query
    By sknechte in forum Access
    Replies: 4
    Last Post: 02-07-2018, 05:00 PM
  2. update/on change event
    By Homegrownandy in forum Access
    Replies: 5
    Last Post: 07-06-2017, 06:57 AM
  3. Replies: 1
    Last Post: 06-25-2016, 02:00 PM
  4. update forms color change
    By trevor40 in forum Forms
    Replies: 2
    Last Post: 03-06-2014, 06:22 AM
  5. On Change or Before Update? MessageBox
    By burrina in forum Forms
    Replies: 6
    Last Post: 01-05-2013, 12:09 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