Results 1 to 8 of 8
  1. #1
    charly.csh is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Nov 2014
    Posts
    186

    Type of event Change or AfterUpdate to update a field

    Hi everyone,

    I creating an event to update the status of a field to be "open" or "close" based on a selected date. (The field name is "Status_Complaint")

    If I update the field "DateClosure" the field Me.Status_Complaint should change to "Close" but If I delete the date the field should change to "Open";

    I am not sure if the event would be "Change" or "AfterUpdate".... anyway anyone of my code works as I want

    In fact the issue I have is when I am deleting the "selected date" the field Me.Status_Complaint is not changing to "Open",

    Does someboby could help? What Should I change in my code?




    Private Sub FechaC_Change()

    If Me.DateClosure = "" Then
    Me.Status_Complaint = "Open"

    ElseIf Me.DateClosure <> """ Then

    Dim MessageBox_CloseNC As Integer
    MessageBox_CloseNC = MsgBox("The report " & Me.Code & " will be closed, żDo you want to continue?", vbQuestion + vbYesNoCancel, "Q-Sys Quality Management Tool")

    If MessageBox_CloseNC = vbYes Then


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    Me.Status_Complaint = "Close"

    End If
    End If

    End Sub

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Code:
    Private sub FechaC_AfterUpdate()
        If IsDate(me.FechaC) then
            Me.Status_Complaint = "Open"
        Else
            me.status_Complaint = "Close"
        End If
    End Sub

  3. #3
    charly.csh is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Nov 2014
    Posts
    186
    Excellent idea!!!

    If didn't know the option "IsDate"

    Thank you very much!!!

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I would put the code in the AfterUpdate as the Change event triggers for every key stroke.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Why change the status when you can just report all 'open' cases as those where the date is null? It's not good practice to have data dependent upon other data in the same record IMO. The field should be DateClosed or something similar. If Null, then it is not closed.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    charly.csh is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Nov 2014
    Posts
    186
    good idea! in fact at the end I put into Afterupdates

  7. #7
    charly.csh is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Nov 2014
    Posts
    186
    This is also good idea Micron!
    When I started to do this I didn't think about this option too, just I got involved on what was wrong in the code

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I strongly recommend it, at least for the very reason why you're posting. An update of another field that basically means the same thing is risky as you are seeing. It requires code/queries/whatever that are not necessary.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 3
    Last Post: 04-18-2018, 09:10 AM
  2. afterupdate event
    By angie in forum Access
    Replies: 2
    Last Post: 04-05-2018, 06:06 PM
  3. update/on change event
    By Homegrownandy in forum Access
    Replies: 5
    Last Post: 07-06-2017, 06:57 AM
  4. Replies: 5
    Last Post: 03-14-2017, 03:30 PM
  5. AfterUpdate event help
    By 10 Gauge in forum Forms
    Replies: 11
    Last Post: 09-08-2011, 10:04 AM

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