Require Fields Based on Drop Down

[blyxx86]

I'm attempting to remove all the garbage from being entered into a database, so I'd like to severely limit the information entered and require specific fields if a certain value is chosen....

For example....

User selects customer "Joe" and that customer requires "Tracking Number" and "Return Tracking Number"

If user selects customer "Ted" but does not enter "Store Number" then they must enter an "Address".



How do I perform these two actions? Perhaps a bit of insight into how to program this, I do not need the code written out, unless there are some extremely fancy commands being used. I just want to know how to go about performing this, and the most effective method.

Remember, Garbage-In-Garbage-Out... Missing some information for one customer could breach a contractual agreement and give a headache.

[MMcCarthy]

If there is any field you know has to be completed always then set it to required. Otherwise you would set the validation in code in the before update event of the form. This means the record won't update unless the validations are satisfied.

Validation would be something like
[CODE=vb]
Private Sub Form_BeforeUpda te(Cancel As Integer)
If Me.IsDirty Then ' Form has been changed
If Not IsNull(Me.Field 1) Then ' If field1 has a value
If nz(Me.Field2,"" ) = "" Then ' If no value in field2 then
Cancel = True ' Go back to form, you can add an error message too
End If
End If
End If
End Sub[/CODE]

[blyxx86]

If there is any field you know has to be completed always then set it to required. Otherwise you would set the validation in code in the before update event of the form. This means the record won't update unless the validations are satisfied.

Validation would be something like
[CODE=vb]
Private Sub Form_BeforeUpda te(Cancel As Integer)
If Me.IsDirty Then ' Form has been changed
If Not IsNull(Me.Field 1) Then ' If field1 has a value
If nz(Me.Field2,"" ) = "" Then ' If no value in field2 then
Cancel = True ' Go back to form, you can add an error message too
End If
End If
End If
End Sub[/CODE]

I guess, I am wondering how I would go about setting specific validation rules up based on a drop down menu...

For example...
CustomerA requires: Field2, Field3 and Field4
CustomerB requires: Field2

I'd like to set this up in a table that contains the requirements (basically yes/no values).

[code=vb]If cboCustomer="Cu stomerA" Then
If IsNull(Field2) or IsNull(Field3) or IsNull(Field4) Then
MsgBox "Please include the required information for " & lblField2 & ", " & lblField3 & " and " & lblField4
End If
End If
[/code]

I'm not sure if you can string together IsNull like that, but you see the general idea.

I'd like to be able to string it together from the database based on customer values...

So perhaps something like...
SELECT tblCustSpecific s.*
FROM tblCustSpecific s
WHERE (((tblLocalVers ion.*)=Yes));

Any ideas?