Best way to deal with data entry forms buttons (Save / Close without changes / Delete)

[Thomasso]

Hello,

I'm trying to optimize my data entry forms. My application works like this:

• Main menu from which users can access all the "list" continuous forms (for example orders list, customers list, products list...)

Private Sub cmdCustomers_Click()

    DoCmd.OpenForm "frm_CustomersList", , , , acFormReadOnly
    
End Sub

• Each record on the "list form" has a "Details" button which opens a "details" form (read only)

Private Sub cmdDetails_Click()

    DoCmd.OpenForm "frm_CustomersDetails", , , "CustomerID=" & Me.CustomerID, acFormReadOnly

End Sub

• In the "list form" header, there's a "Create New Customer" button which opens a data entry form like this:

Private Sub cmdAddNew_Click()

    DoCmd.OpenForm "frmDE_Customers", , , , acFormAdd

End Sub

• Finally, on the customer details form, there is an "Edit" button which does this:

Private Sub cmdEdit_Click()

    DoCmd.OpenForm "frmDE_Customers", , , "CustomerID=" & Me.CustomerID, acFormEdit

End Sub

Everything except the Data Entry form is Read Only. I want to fool proof the data entry forms so there are no accidental record changes and so on. I have 3 buttons at the moment: Save, Cancel, Delete:

Save button:

DoCmd.Close acForm, Me.Name, acSaveYes

Cancel (close the form and keep the record in the state in which the form was opened):

Me.Undo
DoCmd.Close acForm, Me.Name, acSaveYes

Delete (if creating a new record, then cancel, if the record already exists, then delete):
Note: Connect, Exec and Disconnect are my custom subroutines that work with Microsoft SQL Server

    If Not IsNull(Me.PurchaseOrderID) Then
    
        ' Editing an existing record, delete it
        If MsgBox("Záznam bude smazán. Chcete pokračovat?", vbYesNoCancel + vbCritical, "Upozornění") <> vbYes Then Exit Sub
        
        Connect
        Exec "DELETE FROM tbl1PurchaseOrders WHERE PurchaseOrderID=" & Me.PurchaseOrderID
        Disconnect

        DoCmd.Close acForm, Me.Name, acSaveYes
        RequeryAllOpenForms
        
    Else
    
        ' Creating a new record, cancel
        If MsgBox("Vytvářený záznam bude smazán. Chcete pokračovat?", vbYesNoCancel + vbExclamation, "Upozornění") <> vbYes Then Exit Sub
        
        Me.Undo
        DoCmd.Close acForm, Me.Name, acSaveYes
        RequeryAllOpenForms
        
    End If

I am not sure if this is the correct approach, and I do have some problems with data entry - for example pressing F5 refreshes the form and saves the record no matter what, skipping the validation checks.

Can you please advise if the approach above is a good practice, or what's the better way to do it?

Thanks, Tomas

[Minty]

The BeforeUpdate event is the place for validation. You can always prevent a save by cancelling in that event.
Pat Hartman has written up an excellent piece about it here. She has also made an accompanying video and a sample database you can download.
https://www.access-programmers.co.uk...-and-2.324342/

There is also a AUG Session that I think was recorded, more info here:
https://accessusergroups.org/pacific...h-pat-hartman/

[Bob Fitz]

Attached an example of how you might do this:

[Bob Fitz]

Attached an example of how you might do this:

Small addition to code: