Validating Form Data using VBA

[dweekley]

I have been trying to get this validation working for the form BeforeUpdate. Catches the empty field but makes it all the way to the Me.Dirty. Not Exiting. I Rem'd out a couple of the actions trying different scenarios. The Me.Dirty works fine as stand alone.

'Validate Entries


If Len(txtCustomerID.Value & "") = 0 Then
MsgBox "Customer ID must have a value", vbOKOnly, "Missing Value"

Cancel = True
'Me.txtCustomerID.SetFocus
'Exit Sub


ElseIf Len(txtCustomerAbbr.Value & "") = 0 Then
MsgBox "Customer Abbreviation must have a value", vbOKOnly, "Missing Value"

Cancel = True
'Me.txtCustomerAbbr.SetFocus
'Exit Sub


ElseIf Len(txtCustomer_Name.Value & "") = 0 Then
MsgBox "Customer Name must have a value", vbOKOnly, "Missing Value"



Cancel = True
'Me.txtCustomer_Name.SetFocus
'Exit Sub


ElseIf Len(txtDwg_File_Loc.Value & "") = 0 Then
MsgBox "Customer File Location must have a value", vbOKOnly, "Missing Value"

Cancel = True
'Me.txtDwg_File_Loc.SetFocus
'Exit Sub

Else

If Me.Dirty Then
If MsgBox("Do you want to Save?", vbYesNo + vbQuestion, _
"Save Record") = vbNo Then
Me.Undo
Exit Sub

End If

End If

End If

TIA

[RuralGuy]

Me.Dirty will *always* be true in the BeforeUpdate event. That's how you got there.

[jwhite]

Further to RuralGuy's observation, I would re-structure your code.

    If MsgBox("Do you want to Save?", vbYesNo + vbQuestion, "Save Record") = vbYes Then
        'Validate Entries
        If Len(txtCustomerID.Value & vbNullString) = 0 Then
            strMessage = strMessage & vbCrLf & "Customer ID must have a value"
        End If
        If Len(txtCustomerAbbr.Value & vbNullString) = 0 Then
            strMessage = strMessage & vbCrLf & "Customer Abbreviation must have a value"
        End If
        If Len(txtCustomer_Name.Value & vbNullString) = 0 Then
            strMessage = strMessage & vbCrLf & "Customer Name must have a value"
        End If
        If Len(txtDwg_File_Loc.Value & vbNullString) = 0 Then
            strMessage = strMessage & vbCrLf & "Customer File Location must have a value"
        End If
        
        If Len(strMessage & vbNullString) <> 0 Then
            'Display message to user
            MsgBox "Data Entry is incomplete:" & strMessage, vbCritical, "AppNameHere"
            Cancel = True
        End If
    Else
        Cancel = True
    End If

Consider revising logic so users can correct their entry mistakes without loosing other changes that are valid. Also, asking them to save every time can get tiresome. Instead, have an Undo/cancel button so they can get out without saving. If they click [Close] or close the form, then validate and give message if some input is missing and Undo the update - allowing them to correct the mistakes or simply Undo/Cancel the editing themselves.

[dweekley]

Ah...so having the Me.Dirty left me no option but to deal with it. No need to have it if I am attempting to save and/or exit anyways.

Thank you much.

[RuralGuy]

I believe you are on your way to success!