Results 1 to 7 of 7
  1. #1
    emhill57 is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    May 2017
    Posts
    27

    VBA to Check Whether a Command Button has Been Clicked

    Pretty much a novice here but been in software development for over 30 years. Did some minor Access work about 15 years ago and been away from it since. Just gotten the go ahead to get back up to speed. A quick issue that I hope someone can help me with.

    I have a form that has a "Save Changes" button on it. It will run the SaveRecord macro. That works fine. I also have this VBA code if changes have been made to the form and the user tries to exit, go to next record, etc.:

    ===================================
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    ' This procedure checks to see if the data on the form has


    ' changed. If the data has changed, the procedure prompts the
    ' user to continue with the save operation or to cancel it. Then
    ' the action that triggered the BeforeUpdate event is completed.


    Dim ctl As Control


    On Error GoTo Err_BeforeUpdate


    ' The Dirty property is True if the record has been changed.
    If Me.Dirty Then

    ' Prompt to confirm the save operation.
    If MsgBox("Do you want to save your changes?", vbYesNo + vbQuestion, _
    "Save Record") = vbNo Then
    e.Undo
    End If

    End If


    Exit_BeforeUpdate:
    Exit Sub


    Err_BeforeUpdate:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_BeforeUpdate
    End Sub
    ================================================== =

    This works fine as well. But... if the user clicks the "Save Changes" button the dialog comes up as well. I'm thinking that might be a little overkill since the user is explicitly clicking on the "Save Changes" button. What needs to be added to the above VBA to only show that message if the "Save Changes" button has NOT been clicked. The name of the save button is SaveRecord.

    Thanks in advance for any help!!!!!

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    In the on_click event of the save changes button add:
    Code:
    Tempvars!tvSkipCheck = true
    Then in the beforeUpdate:

    Code:
    Dim ctl As Control
    
    On Error GoTo Err_BeforeUpdate
    
    If Tempvars!tvSkipCheck = True then
        Tempvars.Remove "tvSkipCheck"
        exit sub
    end if
    ' The Dirty property is True if the record has been changed.
    If Me.Dirty Then
    
    ' Prompt to confirm the save operation.
    If MsgBox("Do you want to save your changes?", vbYesNo + vbQuestion, _
    "Save Record") = vbNo Then
    e.Undo
    End If
    End If
    
    Exit_BeforeUpdate:
    Exit Sub
    
    Err_BeforeUpdate:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_BeforeUpdate
    End Sub

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure I get the approach. If you're using the BeforeUpdate event at the form level and form controls are bound, it doesn't run unless it's Dirty? Why would it if there's nothing to update?
    Wouldn't this accomplish the same: (P.S. please use code tags if posting more than a few lines)
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    ' This procedure checks to see if the data on the form has
    ' changed. If the data has changed, the procedure prompts the
    ' user to continue with the save operation or to cancel it. Then
    ' the action that triggered the BeforeUpdate event is completed.
    
    
    Dim ctl As Control 'what is this for??
    On Error GoTo Err_BeforeUpdate
    
    If MsgBox("Do you want to save your changes?", vbYesNo + vbQuestion, _
      "Save Record") = vbNo Then
      Cancel = True
    End If
    
    Exit_BeforeUpdate:
    Exit Sub
    
    Err_BeforeUpdate:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_BeforeUpdate
    End Sub
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    emhill57 is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    May 2017
    Posts
    27
    I'm using a Macro in my onclick event. Using the SaveRecord command.
    Attached Thumbnails Attached Thumbnails Capture.JPG  

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Well, that threw me right off guard...

  6. #6
    emhill57 is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    May 2017
    Posts
    27
    The VBA code for the confirm message I got off the world wide webz. Works like I want except I don't want to display the confirm message if the user clicks the save button.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Works like I want except I don't want to display the confirm message if the user clicks the save button.
    I'd say the click action is triggering the BeforeUpdate, so use the tempVars as suggested, or use a variable that remains in scope (at the very top of your form module). The former is probably better for you.
    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: 2
    Last Post: 03-16-2016, 12:33 PM
  2. Saving a Record Edit ONLY if button is clicked?
    By McArthurGDM in forum Forms
    Replies: 3
    Last Post: 06-01-2015, 09:33 PM
  3. Replies: 3
    Last Post: 08-04-2013, 07:11 AM
  4. disabling an enabled button that has been clicked.
    By aaron47 in forum Programming
    Replies: 3
    Last Post: 08-26-2011, 09:49 AM
  5. Replies: 1
    Last Post: 07-27-2010, 02:27 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