I have a validation rule in a control of form: between 1 and 999999. Now I need to have different criteria subject to the value of other control, say between 1 and 9999999 if the value of A company in company control. Is it beyond the capability of validation rule? If so, any other idea to accomplish it? Thanks!
checking a value against its validation rules are only triggered when the user leaves the control so you are better to handle this in code in a form - either to set the validation rule depending on the value in company or to set your own handling process.
In addition you need to consider the situation where the user selects company A which means the rule is between say 100000 and 999999, the user enters a value which is accepted, then the user goes back and changes the company to company B where the rule is between 1000 and 9999
To set the validation rule you would use some code a bit like this in the control enter event (ctrl is the name of the control you want to put this validation)
Code:
Select Case Company
case company between 1 and 50
ctrl.validationrule=">=1000 and <=9999"
ctrl.validationtext="Must be between 1000 and 9999"
case else
ctrl.validationrule=">=10000 and <=99999"
ctrl.validationtext="Must be between 10000 and 99999"
End Select
to prevent the scenario I mentioned about in the company control after update event put
you provided very little detail so I can only provide a rough example of what might work.
If you were to explain exactly what your validation rule is supposed to be and provide details of what your company field is (and if it is a lookup) and how it affects the rule, I can provide a more realistic example.
All 'It did not work as intended and always applied case else validation rule' tells me is that company is not between 1 and 50 - but for all I know, company is a text value - so suggest show the code you are actually using
I tried to modify the code as below but still not working. Your further help is greatly appreciated.
Private Sub txtSKU_Enter()
If Me.Brand like “*GoGo*” Or Me.Brand like “*Mango*” Then
txtSKU.ValidationRule = ">=0000001 and <=9999999"
txtSKU.ValidationText = "Must be 7 digits!"
Else
txtSKU.ValidationRule = ">=000001 and <=999999"
txtSKU.ValidationText = "Must be 6 digits!"
End If
End Sub
Note:
txtSKU is the control that I need to apply the validation rule.
If Me.Brand control has been entered "GoGo" or "Mango", use the validation rule of >=0000001 and <=9999999 for txtSKU. The brand is a text field in which most of brands require 6 digits and only above two brands require 7 digits.
If not, use >=000001 and <=999999 for txtSKU.