Results 1 to 9 of 9
  1. #1
    cantord is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    15

    Y N cancel message box


    I am looking for a generic YN Cancel message box that will work on any form.

  2. #2
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Something like this?

    Code:
    Dim Resp As Long
    
    Resp = MsgBox("Your Prompt Message Goes Here", vbYesNoCancel + vbQuestion, "Message Box Title Goes Here")
    
    Select Case Resp
    
       Case vbYes
         'Code for Yes Response
       
       Case vbNo
          'Code for No Response
       
       Case vbCancel
          'Code for Cancel Response
    
    End Select


    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    cantord is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    15
    ok so if the user clicks on yes I want to save the current record
    if the user clicks no it will delete current record and cancel to edit the current record.
    How do i code this?

  4. #4
    cantord is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    15
    this is what I have
    Code:
     Dim strMsg As String
       Dim iResponse As Integer
     
       ' Specify the message to display.
       strMsg = "Do you wish to save the changes?" & Chr(10)
       strMsg = strMsg & "Click Yes to Save or No to Discard changes or Cancel to go back."
     
       ' Display the message box.
       iResponse = MsgBox(strMsg, vbQuestion + vbYesNoCancel, "Save Record?")
        
       ' Check the user's response.
       If iResponse = vbNo Then
        
          ' Undo the change.
          DoCmd.RunCommand acCmdUndo
     
          ' Cancel the update.
          Cancel = True
          
       ElseIf iResponse = vbCancel Then
       
       'Cancel Close, continue editing
    Cancel = True
        
       End If
       
    End Sub

  5. #5
    cantord is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    15
    but this is not working for me

  6. #6
    Subwind is offline Can Only Learn
    Windows XP Access 2010 32bit
    Join Date
    May 2012
    Location
    Portsmouth, UK
    Posts
    61
    Code:
    Private Sub cmdClose_Click()
    
        If Me.Dirty = True Then 'Check for data changes
                Select Case MsgBox("Do you want to save changes to this record?", vbYesNoCancel, "Save Changes")
                    Case vbYes 'Save changes, close form
                         DoCmd.Close
                    Case vbNo 'Undo changes, close without saving
                        Me.Undo
                        DoCmd.Close
                    Case vbCancel 'Cancel close, continue editing
                        DoCmd.CancelEvent
                End Select
        Else 'No data changes, close form
            DoCmd.Close
        End If
    
    End Sub
    Try this. I have this attached to all the Close buttons on my forms.

    ~Matt

  7. #7
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    You cannot Cancel a Command Button's action! Code asking whether or not to Save a Record or changes to a Record simply has to be in the Form_BeforeUpdate event, otherwise it will not work!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Subwind is offline Can Only Learn
    Windows XP Access 2010 32bit
    Join Date
    May 2012
    Location
    Portsmouth, UK
    Posts
    61
    Quote Originally Posted by Missinglinq View Post
    You cannot Cancel a Command Button's action! Code asking whether or not to Save a Record or changes to a Record simply has to be in the Form_BeforeUpdate event, otherwise it will not work!

    Linq ;0)>
    The code I posted above does work. I use it on all my forms. If you edit data and press close, you can cancel the close action. You can undo the changes (main form not any subforms) or you can save and close.

    ~Matt

  9. #9
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    When you hit 'Cancel,' the code

    DoCmd.CancelEvent


    is not doing anything, because you cannot Cancel the OnClick event of a Command Button! If you could, the Sub Header, instead of being

    Private Sub cmdClose_Click()

    would be

    Private Sub cmdClose_Click(Cancel As Integer)

    But that's OK! It works for you, because when you select 'Cancel' from your Messagebox, Access does nothing, and that is exactly what you want to happen when you hit 'Cancel!' You're not 'canceling' the 'Close event,' because the Close Command has never been issued! It's only issued if 'Yes" or 'No' is selected!

    And yes, in this case, you can use the OnClick event, because you're not actually 'canceling' an Update (which does have to be in the Form_BeforeUpdate event); you're actually not 'canceling' anything! You're just leaving everything status quo, which works for you, here.

    The only thing I'd think about, with this code, is making sure the users clearly understand the three options here! You wouldn't want them to make a dozen changes to a Record, hit 'Close,' see the message "Do you want to save changes to this record?" then realize that they left out one change they meant to make, and hit 'No,' only to see the dozen changes already made disappear! Of course, that all depends on the computer savvy of your users! Sadly, all end-users are not created equal! Or maybe that should be 'happily,' all end-users are not created equal!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

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

Similar Threads

  1. Replies: 9
    Last Post: 09-26-2012, 12:20 PM
  2. Yes/No/Cancel Message Box
    By lynnmc26 in forum Access
    Replies: 0
    Last Post: 04-01-2011, 07:26 PM
  3. VBA to cancel a macro
    By GraemeG in forum Programming
    Replies: 1
    Last Post: 03-26-2011, 04:50 PM
  4. Cancel new record
    By oakoen in forum Forms
    Replies: 11
    Last Post: 12-18-2010, 09:26 AM
  5. Yes No Cancel MsgBox
    By Rick West in forum Forms
    Replies: 5
    Last Post: 04-14-2010, 08:57 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