Data Validation when using a Close button

[SueHopson]

Ok, I have spent way too much time on this, so reaching out for guidance.

When I run the code below it does everything I need it to do, except close the form... Before I added the required fields, I had originally used DoCmd.Close on the btnCxSave button, but now it closes the form before the Validation Code finishes running.

Thoughts?

Private Sub Form_BeforeUpdate(Cancel As Integer)
If ValidateRecord = False Then
Cancel = True

End If
End Sub

Private Function ValidateRecord() As Boolean

ValidateRecord = False
'confirm name is entered
If IsNull(CxName.Value) Then
MsgBox CxName.ValidationText
CxName.SetFocus
Exit Function
End If
'confirm either tel or email is entered - must have a minimum of one contact method minimum
If IsNull(CxTel.Value) Then
 If IsNull(CxEmail.Value) Then
 MsgBox CxTel.ValidationText
 CxTel.SetFocus
Exit Function
End If
End If

ValidateRecord = True

End Function

Private Sub btnCxSave_Click()
        ValidateRecord
        'Forms("frmMAIN").[sbfmCx1_Customers].[Form].Requery
        'Forms("frmMAIN").[sbfmCx2_Contacts].[Form].Requery
        'Forms("frmMAIN").[sbfmCx3_Notes].[Form].Requery
End Sub

[NeoPa]

Hi Sue.

If I understand you correctly, and that means the code you're talking about is a different version than the set posted, you want the Save Command Button to :

1. Check that the data is valid.
2. If data valid.
   1. Save the data
   2. Close the form
3. If data Invalid.
   1. Continue from the point where it found a problem
   2. Data remains unsaved
   3. Form remains open

To handle that we would need the btnCxSave_Click () code to attempt to save the record - rather than trying to invoke ValidateRecord( ) directly, as now seems to be the case. That will trigger the Form_BeforeUpda te() code to run automatically and leave Me.Dirty set to False if it succeeds and True otherwise.

Something like :

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Cancel = (Not ValidateRecord)
End Sub

Private Function ValidateRecord() As Boolean
    'ValidateRecord = False
    'Above unnecessary as Boolean variables are always set to start as False.
    With Me
        'confirm name is entered
        If IsNull(.CxName.Value) Then
            Call MsgBox(.CxName.ValidationText)
            Call .CxName.SetFocus
            Exit Function
        End If
        'confirm either tel or email is entered
        'must have a minimum of one contact method
        If IsNull(.CxTel.Value) _
        And IsNull(.CxEmail.Value) Then
            Call MsgBox(.CxTel.ValidationText)
            Call CxTel.SetFocus
            Exit Function
        End If
    End With
    ValidateRecord = True
End Function

Private Sub btnCxSave_Click()
    With Me
        If .Dirty Then
            .Dirty = False
            If .Dirty Then Exit Sub
            Call DoCmd.Close(ObjectType:=acForm _
                           , ObjectName:=.Name _
                           , Save:=acSaveNo)
        End If
        'Call .[sbfmCx1_Customers].[Form].Requery
        'Call .[sbfmCx2_Contacts].[Form].Requery
        'Call .[sbfmCx3_Notes].[Form].Requery
    End With
End Sub

NB. Although it seems quite counter-intuitive, the best code for telling Access to save the current record on a Form is to use Me.Dirty = False. A non-object-oriented version is to use Call DoCmd.RunComman d(acCmdSaveReco rd) but I vaguely recall that sometimes even fails to execute as expected.

[SueHopson]

OK, that makes sense and I understand most of the logic behind the sequencing of these events, the function is called before the form updates and the save button is checking the form to see if it's been changed (Dirty = False)

So the private function is operating as expected and double checking the form and returning the proper messages...

HOWEVER when trying to enter a NEW customer record, after the first message appears, I am getting a 3021: No current record error. There is definitely a record being created as a new ID number is assigned and the records are being added to my table...

Specifically, the error points here to the .Dirty = False reference

Private Sub btnCxSave_Click()
   With Me
        If .Dirty Then
            .Dirty = False
'...the rest of the code....

So how is handling new records different in this code?

[SueHopson]

Just for fun, I also tried removing an existing customer name and saving it and got a run-time 2101 error.
The setting you entered isn't valid for this property

Same error line when I Debug as above