Form Validation where 1 field depends on another fields value

[Nurse Nancy]

Hi
I have a form where I have 2 fields
AgencyDirectCB - this has a valuelist with Agency or Direct
AgencyCB - This is a combobox from a query of the Agency Table
DirectContactID - this will contain the Direct ContactID

I would like to do the following...

If the user selects Agency, in the AgencyDirectCB, then they must select an
Agency from AgencyCB (it can not be null) and
The DIrectContactID must be null, or not enabled,,,, (but what if there was
already a contact in there, and user is changing from Direct to Agency, it
would need it to be nulled out)

If the User select Direct, then the AgencyCB must be null and
DirectContactID must not be null

Does anyone know how I would do this,, or have a similar example they could
send me,, thanks so much

 

[Jeanette Cunningham]

Hi Nurse Nancy,

Here are some sample codes - air code, untested.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Me.AgencyDiretCB) >0 Then
If IsNull(Me.AgencyCB) Then
Cancel = True
MsgBox "You must select an agency"
End If
End If
End Sub

Private Sub AgencyDirectCB_AfterUpdate()
If Len(Me.AgencyDirectCB) >0 Then
Me.DIrectContactID = Null
End If
End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia