Finding the Field(s) which outrules the validation?

[prabhafriend]

Is it possbile to find which are the field(s) which outrules the validation. I set the required propety to Yes to most of the fields. When the user skips a Field I want to list all the blank fields to the user and instruct him to fill value. Kindly suggest 'Remove the required property from all the fields and putting it in one main field' as a last option. I believe there must be some way better than this.

[OBP]

With VBA you can check all the fields for data before the form is closed or the record is moved.

[prabhafriend]

The problem is the 'default' error message for all the required fields will be displayed. right?

[HiTechCoach]

The problem is the 'default' error message for all the required fields will be displayed. right?

Only if you do not provide your own error messages.

I normally set the required property at the table level. This way the database engine JET/ACE will not allow all it.

At the form level, I use the form's Before update event to do data validation.

Example using the Customer form in the Northwind sample database:

[vba]

Private Sub Form_BeforeUpdate(Cancel As Integer)

Cancel = False


' perform data validation here
If IsNull(Me.CompanyName) Then

MsgBox "You must enter a Company Name.", vbCritical, "Data entry error..."

Cancel = True

End If

' check other controls here as needed

' you could use the .Tag property of controls to flag which ones are required
' Use code to loop through the controls to perform the validation

If Not Cancel Then
' passed the validation process

If Me.NewRecord Then
If MsgBox("Data will be saved, Are you Sure?", vbYesNo, "Confirm") = vbNo Then
Cancel = True
Else
' run code for new record before saving

End If


Else
If MsgBox("Data will be modified, Are you Sure?", vbYesNo, "Confirm") = vbNo Then
Cancel = True
Else
' run code before an existing record is saved
' example: update date last modified

End If
End If

End If


' if the save has been canceled or did not pass the validation , then ask to Undo changes
If Cancel Then

If MsgBox("Do you want to undo all changes?", vbYesNo, "Confirm") = vbYes Then
Me.Undo

End If

End If



End Sub


[/vba]

[geekgirlau]

Just to expand a little on Boyd's excellent example, you can loop through all the controls. This assumes that you have a tag of "Req" on any field that is required.

[vba]
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
If ctl.Tag = "Req" Then
If nz(ctl, "") = "" Then
MsgBox "This field is required!", vbCritical, "Required Field Missing"
ctl.SetFocus
Cancel = True
End If
End If
End Select
Next ctl

[/vba]

[gcomyn]

or, you can set the _exit function to check to see if they are leaving it blank, then let them know they need to fill the textbox.

[vba]
if isnull(me.[text box name].value) then
msgbox "This field is required!", vbCritical, "Required Field Missing"
me.[text box name].setfocus = true
end if
[/vba]

GComyn

[HiTechCoach]

or, you can set the _exit function to check to see if they are leaving it blank, then let them know they need to fill the textbox.

[vba]
if isnull(me.[text box name].value) then
msgbox "This field is required!", vbCritical, "Required Field Missing"
me.[text box name].setfocus = true
end if
[/vba]

GComyn

GComyn,
Instead of "or" I would have suggest and , you can set the ...

What If the user never "enters" the control. Then your code would NEVER fires.

That is why I use both the After Update for controls and the Before Update event of the Form.

[gcomyn]

That is true... I use both. the _exit would be immediate checking, then the before and after events on the form would check to make sure that all was entered.

The After update (I think) only triggers if the user enters something. If they just tab through the controls, the after update for the control doesn't trigger (I think)... so the exit would be the one to use (again, I thnk).

GComyn