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

    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 offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,312
    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
    115
    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
    1,390
    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 offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,070
    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.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  6. #6
    charly.csh is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Nov 2014
    Posts
    115
    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
    115
    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 offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,070
    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.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

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 - Senior Forums