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:
Expand|Select|Wrap|Line Numbers
- 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:
Expand|Select|Wrap|Line Numbers
- 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
Expand|Select|Wrap|Line Numbers
- Private Sub btnsavecorrective_Click()
- DoCmd.RunCommand acCmdSaveRecord
- Exit_btnsavecorrective_Click:
- Exit Sub
- Err_btnsavecorrective_Click:
- MsgBox Err.Description
- Resume Exit_btnsavecorrective_Click
- End Sub
Expand|Select|Wrap|Line Numbers
- Private Sub Form_AfterUpdate()
- MsgBox "Action saved.", vbInformation, "Success"
- End Sub
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