If any of three fields on a form are populated make the other 2 required

[mindbender]

I have three fields on a form (there are way more fields on the form itself but these three are the ones I want to act as a group)

1 is a Boolean checkbox field named Approved
1 is a date field named Approved Date
1 is a dropdown named Method Approved

What I am trying to do is if any one of the three fields are populated make the remaining 2 required.

I think this has to be done on the beforeUpdate event of the form but I am not sure how to construct the VBA. Or maybe it is a validation rule on the table I am not sure

Any help would be appreciated



Bret

[June7]

Might be able to build a validation rule on the field in table or in textbox properties. But I've never tried to do one where fields are mutually dependent. I will suggest that the checkbox is unnecessary. If there is an Approved Date then can be presumed the record is approved.

Review this thread on topic of data validation https://www.accessforums.net/forms/f...ion-52746.html

[Missinglinq]

Validation where Controls/Fields are mutually dependent, like validation to ensure that Controls are populated (and this involves both!) has to be done in the Form_BeforeUpdate event. This is untested but I think it's correct:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  
 If Not IsNull(Me.Approved) Or Not IsNull(Me.[Approved Date]) Or Not IsNull(Me.[Method Approved]) Then
    
  If Nz(Me.Approved, "") = "" Then
    MsgBox "Approved Must Not Be Left Unticked!"
    Cancel = True
    Approved.SetFocus
    Exit Sub
  End If
 
  If Nz(Me.[Approved Date], "") = "" Then
    MsgBox "Approval Date Must Not Be Left Blank!"
    Cancel = True
    [Approved Date].SetFocus
    Exit Sub
  End If
 
  If Nz(Me.[Method Approved], "") = "" Then
    MsgBox "Method of Approval Must Not Be Left Unticked!"
    Cancel = True
    [Method Approved].SetFocus
    Exit Sub
   End If
  
 End If

End Sub

Notice that Approved Date and Method Approved have to have Square Brackets around them, in code; that because you have spaces in their names, which is a bad practice.

Linq ;0)>