Dependent validation

[Bobadopolis]

Hi Everyone,

I have two fields - a 'checked' check box (that verifies if the records in the database have been checked against 'real' values) and a 'checked by' field where the checker should enter their name. I want to force the checker to enter their name if the records have been checked (and obviously leave both fields blank if they have not).

My code thus far is this:

Private Sub chkChecked_AfterUpdate()
If chkChecked = True Then
txtCheckedBy.SetFocus
End If
End Sub
'The 'Checked' check box

Private Sub txtCheckedBy_AfterUpdate()
If chkChecked = True And txtCheckedBy = "" Then 'have also tried this with IsNull
MsgBox "If the record has been checked then you must enter your name in the 'Checked By' field"
chkChecked.SetFocus
End If
End Sub
'The 'Checked by' field.

The first step works fine - the focus shifts when the box is checked, but the second stage doesn't work at all - there is no response. Any suggestions, what am I doing wrong?

Thanks,

Bobadopolis

 

[supercharge]

- there is no response. Any suggestions, what am I doing wrong?

That is because if the user doesn't type anything in the textbox and
continues on to the next control, the second sub (txtCheckedBy_AfterUpdate) will not be called.

To fix this, move your "if - then" statement in the second sub to the next control's AfterUpdate sub, like, let's say the user clicks on a "Next" button after he/she checked the box and left the textbox empty, you will then want to move the "if-then" statement to the "Next" button's OnClick sub.

Hope this helps.

 

[Pat Hartman]

The correct place to put validation code that depends on more than one field or that edits a field for null is the FORM's BeforeUpdate event. The BeforeUpdate field is NEVER bypassed when the record is dirty and is the last event to fire before Access actually commits the update.

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me.chkChecked = True Then
    If IsNull(Me.txtCheckedBy) or Me.txtCheckedBy = "" Then 
        MsgBox "If the record has been checked then you must enter your name in the 'Checked By' field"
        Me.txtCheckedBy.SetFocus
        Cancel = True
    End If
Else
    Me.txtCheckedBy = Null
End If
End Sub

That said, the checkbox is actually redundant and therefore unnecessary. You can always tell if a record has been checked by checking the CheckedBy field with the IsNull() function.

 

[Bobadopolis]

Of course!

It all makes perfect sense now. Plus, there is one disadvantage of triggering the validation when clicking the 'next' control - if the user presses Enter to go to the next record then this bypasses the Sub (given that the CheckedBy field is the last in the tab order). It works perfectly off the Form_BeforeUpdate event.

Cheers to you both!

Bobadopolis