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

    varUndoSave = "Save"
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Close acForm, "frmAddNewAssociate"
    varUndoSave = "Cancel"
    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"
    DoCmd.Close acForm, "frmAddNewAssociate"
On Error GoTo Err_cmdClose_Click
    Exit Sub
    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.
        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!
Tips (Main)
Creating a Multi-Value field using Alphabet
Copy Fields Down from above Record
Loop thru records and OutPutTo seperate .RTF or .PDF
Modified Spell Check
Code Snippets
Lock\Unlock Bound Controls
Loop while renumbering two columns
Create a Table with Dynamic Field Names
Snippets for Exporting to Excel
Log Field Changes
Log Record Deletions
Check for Duplicate Values
Selecting an Excel Worksheet from Access
Send eMail to Multiple Recipients
Automatically Send eMail Notifications
Looping Records to Send eMail
Check if Table Exists
Insert (or Remove) Blank Line
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] = Me.txtAssociateID
    End If
End If
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.