Results 1 to 5 of 5
  1. #1
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496

    cancel / undo / old value ...possibly on the wrong event (?)

    This is about code that I've put behind several controls on a form to determine if the user wants' an edit to a contact's info detail to stay local to the specific project, or if the related record in the MDb needs to also be updated...

    Click image for larger version. 

Name:	2021-06-01_22-37-07.jpg 
Views:	32 
Size:	50.8 KB 
ID:	45392

    i had the code in the AfterUpdate event, and it undid the user action as intended, but it was not proving an accurate .OldValue for evaluation (which makes sense in hindsight);


    However having moved the sub to the BeforeUpdate event, while the .OldValue returns accurate info, .Undo returns and error

    a bit lost (any advice / direction will be greatly appreciated in advance),
    m.

    Code:
    Public Sub UpdateContact(strControl As Control, frm As Form)
        Dim strClient_Group As String
        Dim blnUpdate As Boolean
        blnUpdate = vbFalse
    
        If strControl.OldValue <> strControl Then ' check if the entry is an update to the existing entry
        
            ' there are multiple similar controls on the form: cboClient_Contact, cboEE_Contact
            ' all contacts are in the same table (any contact can be either "Client", "EE",...
            ' extract from the control name which contact is being edited / evaluated in the Mdb table (tblClientContacts)
            If InStr(1, CStr(strControl.Name), "Client", vbTextCompare) Then
                strClient_Group = "Client"
            Else
                strClient_Group = "EE"
            End If
            
            intstart = InStr(1, strControl.Name, "_", 3)
            intLen = Len(strControl.Name)
            strField = strClient_Group & "_" & Mid(strControl.Name, intstart + 1, intLen - intstart + 1)
            cntrlContactControl = "cbo" & strClient_Group & "_Contact"
            strContact_Name = Forms![frmMainMenu].Controls(cntrlContactControl)
    
            'if the initial entry was blank, add the entry to the MDb as default
            'else prompt to leave the entry as is for this project (ONLY), or to cancel and put pack the original information
            If Len(Nz(strControl.OldValue, "")) = 0 Then
                blnUpdate = vbTrue
            Else
                gsMsgText = "You have just changed a detail of this person's contact information:" _
                    & vbCrLf _
                    & strControl.OldValue & " was chaged to be: " & strControl _
                    & vbCrLf _
                    & vbCrLf _
                    & "Do you want to make this change in the MDB?"
                gsMsgTitle = "CONTACT INFO UPDATE"""
                Response = MsgBox(gsMsgText, vbYesNoCancel + vbQuestion, gsMsgTitle)
                
                Select Case Response
                Case Is = vbNo
                    Cancel = True
                    
    '*** this next line is where i'm hung up:
                Case Is = vbCancel
                    '---> none of these seem to be effective:
                    'frm.strControl.Undo
                    'frm.strControl.Value = strControl.OldValue
                    'frm.Dirty = False
                    
                Case Is = vbYes
                    blnUpdate = vbTrue
                End Select
                
            End If
            
            If blnUpdate Then
                Set gsDbs = CurrentDb
                Set gsRst = gsDbs.OpenRecordset("select * " & _
                    "FROM tblClientContacts " & _
                    "WHERE [Client_CompanyName] = '" & Forms!frmMainMenu.cboClient_CompanyName & "' " & _
                    "AND [Client_Contact] =  '" & strContactName & "';")
                gsRst.Edit
                gsRst.Fields(strField) = strControl
                gsRst.Update
                Set gsDbs = Nothing
                Set gsRst = Nothing
            End If
        End If
    End Sub
    Attached Thumbnails Attached Thumbnails 2021-06-01_22-37-07.jpg  

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    In BeforeUpdate event, should not need to Undo the record. Cancel=True will abandon the record commitment. Can use Undo on each control if you want to null them for new input. Cancel = True means nothing in your UpdateContact procedure unless you have declared a public variable.

    https://docs.microsoft.com/en-us/off...reupdate-event
    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
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    correct, the field does not update; however,
    the problem is that entry on the form is not being restored to what it had been

    i added the code lines:

    Code:
                    Cancel = True
                    frm.strControl = strControl.OldValue
    but it returns the error:
    Run-time error '2465'
    application-defined object defined error
    (?)
    even if i only add:
    Code:
     Cancel = True
    i get an error

  4. #4
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You can't add a cancel event to any general sub, they are only available in the "Before" form or control events, as after that has passed you are too late.

    That is why Cancel = True is giving you that error in your Sub.

    Move your validation to the Forms Before_Update event as per Junes advice.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    SamL is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2021
    Location
    NJ
    Posts
    20
    '*** this next line is where i'm hung up:
    Case Is = vbCancel
    '---> none of these seem to be effective:
    'frm.strControl.Undo
    'frm.strControl.Value = strControl.OldValue
    'frm.Dirty = False


    Try using the bang symbol (!)

    frm!strControl.Undo
    frm!strControl.Value = strControl.OldValue

    I don't think that forcing the Dirty property will work unless the Undo is effective. See the help file for the Undo method and the Dirty property for details.

    Good luck,
    Sam



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

Similar Threads

  1. Option Group Control: Cancel click / update event
    By ironfelix717 in forum Programming
    Replies: 7
    Last Post: 04-26-2021, 02:30 AM
  2. Esc key not triggering Undo Event
    By GraeagleBill in forum Programming
    Replies: 7
    Last Post: 01-20-2015, 10:41 AM
  3. Replies: 1
    Last Post: 08-15-2011, 03:20 PM
  4. Cancel form navigation event
    By tuna in forum Forms
    Replies: 3
    Last Post: 08-15-2010, 01:46 PM
  5. Cancel Selection Event for ListBox
    By is49460 in forum Forms
    Replies: 2
    Last Post: 08-04-2010, 05:53 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