BeforeUpdate and/or Save create new record when user clicks on "No"

[skydivetom]

Experts:

I need some assistance with a simple form operation. My forms has several fields... none of them are required fields. I have some issues with "blank" records being inserted into my table. I'm sure there's some redundant code which causes the conflict.
Below summarizes the process for two (2) scenarios, A and B:

Scenario A:
1. User opens form and enters values into fields.
2. If users then selects combo drop-down to switch to another record, the Me.Dirty should kick in and prompt to save the current record.


3a. If user clicks "Yes", I want current record to be saved and then bring up the other (selected) record.
3b. However, if user clicks "No", the recently entered data for current record can be discarded and the other (selected) record show come up.

Scenario B:
1. User opens form and enters values into fields.
2. User click the "Save" button.
3a. If user clicks "Yes", I want current record to be saved.
3b. However, if user clicks "No", the recently entered data for current record can be discarded.

So, in scenario A, the system prompts the "save" record operations before switching to another record... it's then up to the user to either keep or delete current record.
Alternatively, scenario B describes process where users want to save/not save the changes.
Right now, I've seen where the "No" results in a new record with empty values in my table. Please see the VBA function for "BeforeUpdate" and "Save" below. What's causing a record to be created when user did NOT want to save the changes?

Private Sub cmd_SaveRecord_Click()
    'The Dirty property is True if the record has been changed.
    If Me.Dirty Then
       'Prompt to confirm the save operation.
       If MsgBox("Do you want to save the record?", vbYesNo + vbQuestion, "Save Record") = vbNo Then
          Me.Undo
       End If
    End If
    'Save record
    DoCmd.RunCommand acCmdSave
    
    'Update combo for selecting staff member and supervisor
    Me.cbo_StaffMember.Requery
    Me.cbo_supervisor.Requery
    
End Sub



Private Sub Form_BeforeUpdate(Cancel As Integer)
   'If the data has changed, the procedure prompts the user to continue with the save operation or to cancel it.
   'Then the action that triggered the BeforeUpdate event is completed.
   Dim ctl As Control
   On Error GoTo Err_BeforeUpdate
   'The Dirty property is True if the record has been changed.
   If Me.Dirty Then
      'Prompt to confirm the save operation.
      If MsgBox("Do you want to save the record?", vbYesNo + vbQuestion, "Save Record") = vbNo Then
         Me.Undo
         DoCmd.RunCommand acCmdDeleteRecord
      End If
   End If
   'Updates record's datetime stamp
   'Note the use of dot (.) and bang (!) for the two different approaches -- this prevented the 2448 error for first record as it was caused by trying to set txt_date_timestamp in BeforeUpdate
   'Me!txt_date_timestamp = Now()
    Me!Record_Modified_Date = Now()
Exit_BeforeUpdate:
   Exit Sub
Err_BeforeUpdate:
   MsgBox Err.Number & " " & Err.Description
   Resume Exit_BeforeUpdate
   
End Sub

How do I rewrite VBA code so that no blank records are created in table when user chooses "no" (either on Me.Dirty or when clicking "Save")?
Thank you,
EEH

[ranman256]

i would never use BEFORE update. anything can be examined in afterupdate.
vaildate the required fields to keep them from running other code, or leaving the screen:

usage:
if IsValidForm() then docmd.RunMacro "mUpdateMyTables"

Private Function IsValidForm() As Boolean
Dim vMsg
Select Case True
   Case IsNull(dtpWeekOf)
      vMsg = "Date field missing"
   Case IsNull(cboUser)
      vMsg = "Teacher name is missing"
   Case IsNull(cboSubj)
      vMsg = "Subject field is missing"
End Select
If vMsg <> "" Then MsgBox vMsg, vbCritical, "Required Field"
IsValidForm = vMsg = ""
End Function

[pbaldy]

i would never use BEFORE update. anything can be examined in afterupdate.

The before update event is very commonly used for validation. It can be cancelled, where the after update event can't. You also can't examine the OldValue property in the after update event, so before update is often used for audit trail code as well.

[skydivetom]

ranman256 -- so, are you suggesting to replace the BeforeUpdate with the IsValidForm() function? If yes, where do I place IsValidForm? Also, is there a macro that needs to be designed?

So, I'm not sure how your recommendation should be implemented?



//



pbaldy -- do you have a recommendation which lines of code needed to be added/modified/deleted in the Save and BeforeUpdate functions?