Save changes in form based on temp table using native close button

Question

I have a form where users can edit multiple different pieces of data. This form is based on data pulled from several underlying tables and placed into a temporary table. When a user makes a change and hits the "save" button, any changes made in the form get updated/appended to the underlying data tables and everything in the temp table gets dumped. This all works great.

My problem is with the native close button on the form. If a user makes a change, forgets to hit save, and instead just closes the form, nothing gets saved. I think it's because I need to run my update and append queries (which is what happens with my save button) but I'm unsure how to incorporate them in so they run with the native close button. When I try to add them to the Form_Close event, I get an error message that something in the BeforeUpdate event is preventing Access from saving the data. I know I could remove the close button from the form, but if possible, I would rather leave it in.

Form_BeforeUpdate

txtOld = Nz(Me.Document.OldValue, "")

If Not (Me.NewRecord) Then
    If MsgBox("Changes have been made to this record. Save changes?", vbYesNo, "") = vbNo Then
        DoCmd.RunCommand acCmdUndo
    End If
End If

cmdSave_Click

On Error GoTo Error_Handler
DoCmd.RunCommand acCmdSaveRecord

'check to see if new book field has changed and run append query if it has
If txtOld <> Me.Document.Value Then
    DoCmd.OpenQuery "qryUpdateNewBookList"
End If

'This update query should run every time 
DoCmd.OpenQuery "qryEditUpdate"

DoCmd.Close acForm, Me.Name, acSaveNo

Form_Close

DoCmd.RunSQL ("DELETE tblEditTbl.* FROM tblEditTbl")

user_5514747 · Answer

One quick tips. You can use below code to through a message that data is not saved.

Private Sub cmdClose_Click()
    If Me.Dirty = True Then
        MsgBox "Data is not saved. Save data first then try to close", vbExclamation
        Exit Sub
    Else
        MsgBox "Data saved successfully."
        DoCmd.Close
    End If
End Sub

Edit: If you want to check it before update then you have to use a variable to check if data is saved or not. Declare a variable at top of form code module like

Option Compare Database
Private blnSaveRecord As Boolean

Use below codes to Save button.

Private Sub cmdSave_Click()
    blnSaveRecord = True
        DoCmd.RunCommand acCmdSaveRecord
    blnSaveRecord = False
End Sub

And finally write below codes to Form_BeforeUpdate event.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
 
    If Not blnSaveRecord Then
        Cancel = True
        strMsg = "Please save the record.," & _
                 vbNewLine & "or press ESC from keyboard to cancel the operation."
        MsgBox strMsg, vbInformation, "Save Record"
    End If
End Sub

user_13951021 · Answer

I solved my problem. Harun24HR got me thinking in the right direction. In addition to my existing code, I needed to use the Form_Unload event and create two public boolean variables to check if the save button had been clicked and if any edits had been made in the form.

Form_Unload
If Not SaveClicked And IsDirty Then
    MsgBox "Data is not saved. Save data first and then try to close.", vbExclamation
    Cancel = True
End If

Save changes in form based on temp table using native close button

2 Answers