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
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
ValidateData()
ClearClipboard()
Selecting an Excel Worksheet from Access
Send eMail to Multiple Recipients
Automatically Send eMail Notifications
fFindBookmark()
Looping Records to Send eMail
fxlFindReplace()
fMouseOverCurrent()
fHighlightRequiredControls()
Check if Table Exists
fAmortization()
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].Requery
        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.