Help with validation rule on form

Question

So I have a form where 3 fields cannot be blank if my combo box is a specific value.

Example: Field 1, Field 2 and Field 3 must have data entered if my combo box selection is "Completed".

I'm able to write a simple IF statement for each field separately, but obviously this isn't efficient. Is there a way to combine this into a validation rule or a combined IF statement for all 3 fields?

TIA!

Taz

Answer 1

Rather than attempting to use the ValidationRule property for this, the usual method of validation in this sort of scenario would be to include code in the form's BeforeUpdate event procedure, and set the return value of the procedure's Cancel argument to True if the validation criteria are not met.  Something along these lines:

Const MESSAGE_TEXT = "Fields 1 to 3 must be completed for a completed transaction"

If Me.[YourComboBox] = "Completed" Then

    If IsNull(Me.[Field1]) Or IsNull(Me.[Field2]) Or IsNull(Me.[Field2]) Then

        MsgBox MESSAGE_TEXT, vbExclamation, "Invalid Operation"

        Cancel = True

    End If

End If

Answer 2

Sure. I know this isn't very efficient with 3 separate IF statements.

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If Status.Value = "Completed" And IsNull([Awd_Dt]) Then

       [Award_Dt].SetFocus

       MsgBox "Please enter the award date!", vbCritical, "Missing Award Date"

       Cancel = True

    End If

    If Status.Value = "Completed" And IsNull([Awd_Amt]) Then

       [Awd_Amt].SetFocus

       MsgBox "Please enter the award amount!", vbCritical, "Missing Award Amount"

       Cancel = True

    End If

    If Status.Value = "Completed" And IsNull([KNbr]) Then

       [KNbr].SetFocus

       MsgBox "Please enter the contract number!", vbCritical, "Missing Contract Number"

       Cancel = True

    End If

End Sub

Answer 3

If you can show us what you tried, maybe we can help you fix whatever is wrong.