Results 1 to 12 of 12
  1. #1
    oakoen is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2009
    Posts
    15

    Cancel new record

    I have a form that has a button that opens another form and adds a record.


    I have a save and cancel button.

    I would like the cancel button to not save the newly added record and close the form with out any further user interaction.

    I have been unable to accomplish this yet.


    What the heck am I missing?

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You need to do a Me.UnDo before closing the form.

  3. #3
    oakoen is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2009
    Posts
    15
    Can you please give a quick example? I have seen that but have been unable to get it to wrap my head around it yet.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    What code do you currently have behind the Cancel button? Can you post all of it starting with Private Sub and ending with End Sub.

  5. #5
    oakoen is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2009
    Posts
    15
    I am a noob. I don't. Nothing I tried worked so as of right now I have an embeded macro closing the form and not saving saving it.

    On the before update I have

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    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."

    ' Display the message box.
    iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")

    ' Check the user's response.
    If iResponse = vbNo Then
    ' Undo the change.
    DoCmd.RunCommand acCmdUndo

    ' Cancel the update.
    Cancel = True
    End If

    End Sub



    So when I press cancel I get the do you wish to save changes message, when No is pressed you get the Access You can't save this record at this time message, press yes to close anyway, then you get the no current record message and finally your form closes with no new record added.

    I need a much cleaner way.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Try the following:
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
       Dim strMsg As String
       '   Dim iResponse As Integer
       ' Specify the message to display.
       strMsg = "Do you wish to save the changes?" & vbCrLf & _
                "Click Yes to Save or No to Discard changes."
       ' Display the message box.
       If MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?") = vbNo Then
          Me.Undo
       End If
       ' Check the user's response.
       '-- You most certainly do *NOT* want to cancel this event!
     
       '   If iResponse = vbNo Then
       '      ' Undo the change.
       '      DoCmd.RunCommand acCmdUndo
       '
       '      ' Cancel the update.
       '      Cancel = True
       '   End If
    End Sub

  7. #7
    oakoen is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2009
    Posts
    15
    Changed to

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    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."

    ' Display the message box.
    iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")



    ' Check the user's response.
    If iResponse = vbNo Then
    Me.Undo
    ' Undo the change.
    ' DoCmd.RunCommand acCmdUndo

    ' Cancel the update.
    ' Cancel = True

    End If

    End Sub

    That got rid of the Access "You can't save this record at this time message, press yes to close anyway" and went straight to the "no new record added" message

    we are getting there, that's one less ugly step, Might be able to live with that.

  8. #8
    oakoen is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2009
    Posts
    15
    BTW I tried exactly what you suggested and got the same result.

    Thanks for helping me out, I really appreciate it.

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Where is the "No new record added" message coming from? I do not see it anywhere in the code supplied.

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    BTW, you can eliminate *all* of the messages when you press the "Cancel" button if you want. No additional questions or anything.

  11. #11
    oakoen is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2009
    Posts
    15
    It's an access error.

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    That is not actually an error, just a notice. Go to OfficeButton>Options>Advanved>Confirm and uncheck a few things and see what happens.

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

Similar Threads

  1. Cancel form navigation event
    By tuna in forum Forms
    Replies: 3
    Last Post: 08-15-2010, 01:46 PM
  2. Cancel Selection Event for ListBox
    By is49460 in forum Forms
    Replies: 2
    Last Post: 08-04-2010, 05:53 PM
  3. Yes No Cancel MsgBox
    By Rick West in forum Forms
    Replies: 5
    Last Post: 04-14-2010, 08:57 AM
  4. Cancel listbox selection
    By vba-dev in forum Access
    Replies: 0
    Last Post: 10-26-2009, 12:18 PM
  5. Replies: 4
    Last Post: 04-01-2009, 11:48 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