Form validation; I've been doing it wrong so now time to learn how properly

[munkee]

Hi all,

Basically I have been using form validation incorrectly. Partly because of laziness which I now feel is going to really bite me back if I dont get it sorted.

On all of my forms I have a save button. Simply because it is often easier to explain to people just click save and it will save the record than it is for them to grasp the idea that access doesnt need a record to be "saved" from a button.

In the click event of this save button I carry out form validation.

Here is some sample code:

Private Sub Command228_Click()
Dim errorstring As String

On Error GoTo FightingSomethingBeatable


If Len(Nz(Me.subfrmInvestigation_tools.Form.InvStartDate)) < 1 Then
    Me.subfrmInvestigation_tools.Form.InvStartDate.SetFocus
    Me.subfrmInvestigation_tools.Form.InvStartDate.BackColor = vbRed
    errorstring = errorstring & "The investigation start date must be entered." & vbCrLf
Else
    Me.subfrmInvestigation_tools.Form.InvStartDate.BackColor = 16579561
End If

If Len(Nz(Me.subfrmInvestigation_tools.Form.InvEndDate)) < 1 Or (Me.subfrmInvestigation_tools.Form.InvEndDate - Me.subfrmInvestigation_tools.Form.InvStartDate) < 0 Then
    Me.subfrmInvestigation_tools.Form.InvEndDate.SetFocus
    Me.subfrmInvestigation_tools.Form.InvEndDate.BackColor = vbRed
    errorstring = errorstring & "The investigation end date must be entered." & vbCrLf
Else
    Me.subfrmInvestigation_tools.Form.InvEndDate.BackColor = 16579561
End If


If Len(Nz(Me.subfrmInvestigation_tools.Form.InvTechandOutcome)) < 3 Then
    Me.subfrmInvestigation_tools.Form.InvTechandOutcome.SetFocus
    Me.subfrmInvestigation_tools.Form.InvTechandOutcome.BackColor = vbRed
    errorstring = errorstring & "Information regarding the investigation must be added." & vbCrLf
Else
    Me.subfrmInvestigation_tools.Form.InvTechandOutcome.BackColor = 16579561
End If


If Me.Controls("subrootcauseform").Form.Recordset.RecordCount < 1 Then
    Me.Controls("subrootcauseform").Form.RootCauseType.SetFocus
    Me.Controls("subrootcauseform").Form.RootCauseType.BackColor = vbRed
    Me.Controls("subrootcauseform").Form.RootCauseDescrip.BackColor = vbRed
    errorstring = errorstring & "You must assign atleast one root cause for the nonconformance event." & vbCrLf
Else
    Me.Controls("subrootcauseform").Form.RootCauseType.BackColor = 16579561
    Me.Controls("subrootcauseform").Form.RootCauseDescrip.BackColor = 16579561
End If

If Me.Outcome = "Use as is" Or Me.Outcome = "Corrective action required" Then
    Me.Outcome.BackColor = 16579561
Else
    Me.Outcome.SetFocus
    Me.Outcome.BackColor = vbRed
    errorstring = errorstring & "A valid outcome must be selected before an investigation can be completed." & vbCrLf
End If

If Len(Nz(Me.txtoutcomedescrip)) < 3 Then
    Me.txtoutcomedescrip.SetFocus
    Me.txtoutcomedescrip.BackColor = vbRed
    errorstring = errorstring & "A reasoning regarding the outcome must be present before an investigation can be completed." & vbCrLf
Else
    Me.txtoutcomedescrip.BackColor = 16579561
End If

If Len(Nz(Me.txtoutcomeapprovedby)) < 2 Then
    Me.txtoutcomeapprovedby.SetFocus
    Me.txtoutcomeapprovedby.BackColor = vbRed
    errorstring = errorstring & "The investigation outcome must be approved before an investigation can be completed." & vbCrLf
Else
    Me.txtoutcomeapprovedby.BackColor = 16579561
End If

If Len(Nz(errorstring)) > 0 Then
MsgBox errorstring, vbInformation, "Error in completion of investigation stage."
Exit Sub
Else

Select Case Me.Outcome

        Case "Use as is"
            Me.Outcome.BackColor = 16579561
            MsgBox "You have selected: Use as is." & vbCrLf & _
            "The nonconformance will now be marked as ready for closure, no further action is required."
            Me.NCStatus = 4
            DoCmd.RunCommand acCmdSaveRecord


        Case "Corrective action required"
            Me.Outcome.BackColor = 16579561
            MsgBox "You have selected: Corrective action required." & vbCrLf & _
            "This status has been saved and corrective action(s) should now be assigned/recorded."
            Me.NCStatus = 3
            DoCmd.RunCommand acCmdSaveRecord

        Case Else
            
            If Me.Outcome <> "Use as is" Or Me.Outcome <> "Corrective action required" Then
                Me.Outcome.SetFocus
                Me.Outcome.BackColor = vbRed
                MsgBox ("Please select a valid outcome for the nonconformance event." & vbCrLf & _
                "Use as is - To continue using the subject without performing corrections." & vbCrLf & _
                "Corrective action required - Corrections required in order for the nonconformance to be closed.")
                Me.NCStatus = 3
            Else
                Exit Sub
            End If
            
    End Select

End If


InchByInch:

Exit Sub

FightingSomethingBeatable:
MsgBox Err.Description
Resume InchByInch

End Sub

Now I know I SHOULD be using the Before_Update event of a form to do validation but for the life of me I can not find a simple example to follow.

I have found using my current method if validation fails and the user is required to fill in a section, they can still choose not to and move to a new record. They can also close the form and changes have already been commited to the database. I know I need to get cancel=true working which once again means before_update event. I have given this a go on a form producing the following:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_btnsavecorrective_Click

Dim ErrorStrings

'Create the error form validation stuff here

ErrorStrings = vbNullString

If Len(Nz(Me.CorrectivePersonCarryout)) < 1 Then
    Me.CorrectivePersonCarryout.SetFocus
    Me.CorrectivePersonCarryout.BackColor = vbRed
    ErrorStrings = ErrorStrings & "You must enter the name of the person who will be carrying out the action." & vbCrLf

Else

    Me.CorrectivePersonCarryout.BackColor = 16579561

End If

If Len(Nz(Me.CorrectiveDate)) < 1 Then
    ErrorStrings = ErrorStrings & "You must enter a proposed date for the action to start." & vbCrLf
    Me.CorrectiveDate.SetFocus
    Me.CorrectiveDate.BackColor = vbRed

Else

    Me.CorrectiveDate.BackColor = 16579561

End If

If Len(Nz(Me.CorrectiveCompletedDate)) < 1 Or (Me.CorrectiveDate > Me.CorrectiveCompletedDate) Then
    ErrorStrings = ErrorStrings & "You must enter a proposed date for the action to be completed." & vbCrLf
    Me.CorrectiveCompletedDate.SetFocus
    Me.CorrectiveCompletedDate.BackColor = vbRed

Else

    Me.CorrectiveCompletedDate.BackColor = 16579561

End If




If Len(Nz(Me.CorrectiveDescription)) < 4 Then
    
    Me.CorrectiveDescription.SetFocus
    Me.CorrectiveDescription.BackColor = vbRed
    ErrorStrings = ErrorStrings & "You must supply an adequate description for the action." & vbCrLf

Else

    Me.CorrectiveDescription.BackColor = 16579561

End If


'Create the if statement to see if anything has been done incorrectly before allowing continuation

If Len(Nz(ErrorStrings)) > 0 Then

'error has occured cancel any save of the record
Cancel = True
    MsgBox ErrorStrings, vbInformation, "Errors in your entries"
    


Exit Sub
Else

'everything looks to be ok allow the code to continue running

End If
End Sub

With my save button having:

Private Sub btnsavecorrective_Click()

    DoCmd.RunCommand acCmdSaveRecord

    

Exit_btnsavecorrective_Click:
    Exit Sub

Err_btnsavecorrective_Click:
    MsgBox Err.Description
    Resume Exit_btnsavecorrective_Click
    
End Sub

And to ensure I know the record has been commited:

Private Sub Form_AfterUpdate()
MsgBox "Action saved.", vbInformation, "Success"
End Sub

However for some reason the validation just isn't firing. this is what currently happens:

Load a new record in the sub form and click save = nothing happens = correct since nothing dirty

Load a new record and edit 1 field and click save = record saves and message box pops up to inform it has saved. = incorrect where is the form validation on the other fields?

Load a new record edit 1 field and click to move to next/previous record = record saves = correct but not what I want to happen I want the user to either select to save the changes or to cancel the changes and continue on.


Hopefully someone can help me out here as I know it is a fundamental problem in my designs and something which I want to ensure I get right.

Thanks for the help,

Chris

[munkee]

I have got the before update event to fire. It now validates as my save button would.

However my issue now is how would I incorporate my save buttons in to all of this. The problem I now have is that I wont be able to cancel the actual running of the save event. E.g.

Click save button to save the record. (Docmd.Runcomma nd accmdsaverecord )
Record runs through before update
Record fails validation and cancel = true fires
Click event can not save the record as intended therefore I get runtime error 2501 runcommand action was cancelled and a spew out in to vba.

[munkee]

Got this all solved now. The basic framework should look like this:

Framework for saving records:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ErrorStrings As String



'The error string can be set to null for this first run
ErrorStrings = vbNullString


'If the user tries to move off the record or any other event fires a save lets ask whether they actually want to do something with the changes or discard them
If MsgBox("Changes have been made to this record." _
        & vbCrLf & vbCrLf & "Do you want to save these changes?" _
        , vbYesNo, "Changes Made...") = vbYes Then

'Carry out the form validation to ensure everything is filled in correctly if it isnt lets get this stuff fixed and not save the current record


If Len(Nz(Me.CorrectivePersonCarryout)) < 1 Then
    Me.CorrectivePersonCarryout.SetFocus
    Me.CorrectivePersonCarryout.BackColor = vbRed
    Cancel = True
    ErrorStrings = ErrorStrings & "You must enter the name of the person who will be carrying out the action." & vbCrLf

Else

    Me.CorrectivePersonCarryout.BackColor = 16579561

End If




'Create the if statement to see if anything has been done incorrectly before allowing continuation

If Len(Nz(ErrorStrings)) > 0 Then

'error has occured cancel any save of the record

    MsgBox ErrorStrings, vbInformation, "Errors in your entries"
    
Cancel = True
Exit Sub
Else
'everything looks to be ok allow the code to continue running
MsgBox "Action saved.", vbInformation, "Success"

End If

Else
          DoCmd.RunCommand acCmdUndo
'Clear out any of the vbred backgrounds if an undo is selected 
'Me.CorrectiveDate.BackColor = 16579561


          
    End If
End Sub


'==================== Save button event
Private Sub btnsavecorrective_Click()
On Error GoTo Err_btnsavecorrective_Click


    DoCmd.RunCommand acCmdSaveRecord

    

Exit_btnsavecorrective_Click:
    Exit Sub

Err_btnsavecorrective_Click:

'capture the correct error number and just change it.
If Err = 2501 Then 'The command save record has been cancelled
MsgBox "Save cancelled.", vbInformation, "Info"

Else
    MsgBox Err.Description
    Resume Exit_btnsavecorrective_Click
End If
End Sub