By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,199 Members | 1,742 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,199 IT Pros & Developers. It's quick & easy.

Require Fields Based on Drop Down

blyxx86
100+
P: 256
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.
Jun 28 '07 #1
Share this Question
Share on Google+
2 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. If Me.IsDirty Then ' Form has been changed
  3.    If Not IsNull(Me.Field1) Then ' If field1 has a value
  4.       If nz(Me.Field2,"") = "" Then ' If no value in field2 then
  5.          Cancel = True ' Go back to form, you can add an error message too
  6.       End If
  7.    End If
  8. End If
  9. End Sub
Jul 4 '07 #2

blyxx86
100+
P: 256
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. If Me.IsDirty Then ' Form has been changed
  3.    If Not IsNull(Me.Field1) Then ' If field1 has a value
  4.       If nz(Me.Field2,"") = "" Then ' If no value in field2 then
  5.          Cancel = True ' Go back to form, you can add an error message too
  6.       End If
  7.    End If
  8. End If
  9. End Sub
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).

Expand|Select|Wrap|Line Numbers
  1. If cboCustomer="CustomerA" Then
  2. If IsNull(Field2) or IsNull(Field3) or IsNull(Field4) Then
  3. MsgBox "Please include the required information for " & lblField2 & ", " & lblField3 & " and " & lblField4
  4. End If
  5. End If
  6.  
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 tblCustSpecifics.*
FROM tblCustSpecifics
WHERE (((tblLocalVersion.*)=Yes));

Any ideas?
Jul 4 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.