Access Diva Cancel Save in a Bound Form

The difficult I do immediately, the impossible takes a little bit longer.

Cancel Save in a Bound Form...

You go to add new and then change your mind. In an UNBOUND Form this is not a problem but in a BOUND Form it presents an issue, problem solved!

Create your Form and add a cmdSave, cmdCancel, cmdClose and remove the Control Box from view. You need complete control when the Form closes and the built-in Close button will not respect your code.

In a seperate Module (not behind the Form) place...

Function fUndoSave(myForm As Form)
 
    fUndoSave = False
 
    Select Case varUndoSave
        Case "Cancel"
            If MsgBox("Do you want to Undo this save?", vbQuestion + vbYesNo, "Save or Cancel?") = vbYes Then
                'Cancel = True
                fUndoSave = True
            Exit Function
            End If
        Case "Save"
                fUndoSave = False
                'Cancel = True
            Exit Function
        Case Else
            'MsgBox "Please press the Save or Cancel button!", vbInformation + vbOKOnly, "Save or Cancel?"
            Exit Function
    End Select
 
End Function

Then go to Design View of the Form and place the below code in the Event Procedures of cmdSave, cmdCancel and cmdClose

cmdSave

    varUndoSave = "Save"
    DoCmd.RunCommand acCmdSaveRecord
    DoEvents
    DoCmd.Close acForm, "frmAddNewAssociate"

cmdCancel

    varUndoSave = "Cancel"
    DoEvents
    DoCmd.Close acForm, "frmAddNewAssociate"

If your Form includes Subforms, you must delete the data from those tables first, i.e.

    strSQL = "DELETE * " & _
                "FROM tblAssociateSkills " & _
                    "WHERE asAssociateID=" & Me.txtAssociateID & ""
              CurrentDb.Execute strSQL, dbFailOnError
 
    varUndoSave = "Cancel"
    DoEvents
    DoCmd.Close acForm, "frmAddNewAssociate"

cmdClose

On Error GoTo Err_cmdClose_Click
 
    DoCmd.Close
 
Exit_cmdClose_Click:
    Exit Sub
 
Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click

Private Sub cmdAddNew_Click()
 
    Dim intResponse As Integer
 
    intResponse = MsgBox("Do you want to add a new Associate?", vbYesNo + vbQuestion + vbDefaultButton2, "Add New")
 
        If intResponse = vbYes Then
            DoCmd.OpenForm "frmAddNewAssociate", acNormal, , , acFormAdd
            Forms!frmAddNewAssociate!cmdClose.Visible = False
            Forms!frmAddNewAssociate!cmdSave.Visible = True
            Forms!frmAddNewAssociate!cmdCancel.Visible = True
        Else    'User chose No.
            DoCmd.CancelEvent
        End If
 
End Sub

Now when you open the Form from and Add New button you can hide the Close command button but when opening just to show you can hide the Save and Cancel button.

All done, enjoy!

VBA

Tips (Main)

Home

When adding new from as a pop-up, once going back to the Main Form to get to the record you just added add the below to the Form's On_Close event procedure.

If varUndoSave = "Save" Then
    If IsLoaded("frmAssociateProfile") Then
        Forms![frmAssociateProfile]![lstAssociateID].Requery
        Forms![frmAssociateProfile]![lstAssociateID] = Me.txtAssociateID
    End If
End If