Validation rule

ScottXe · Jul 19, 2015

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!

CJ_London · Jul 19, 2015

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

Code:

ctrl=0

ScottXe · Jul 19, 2015

Hi CJ,

I believe Company is the check field to determine the validation rule. I update the 2nd row of statement to case company like "*Sony*"

Select Case Company
case company between 1 and 50

It did not work as intended and always applied case else validation rule. Is there any syntax error on the second row of statement?

CJ_London · Jul 19, 2015

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

ScottXe · Jul 19, 2015

Hi CJ,

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.

CJ_London · Jul 19, 2015

still not clear what you mean by 'not working'

it may be because you are using text values to compare against a number

or it may be that 111 would fit for either of these rules

if you are comparing numbers then you need

between 100000 and 999999

and

between 1000000 and 9999999

ScottXe · Jul 20, 2015

Hi CJ,

Could you please advise what the below two statements do especially "between 1 and 50"

Select Case Company
case company between 1 and 50

Thanks!

CJ_London · Jul 20, 2015

ignore it - I interpreted your post to mean something else

Validation rule

ScottXe

Registered User.

CJ_London

Super Moderator

ScottXe

Registered User.

CJ_London

Super Moderator

ScottXe

Registered User.

CJ_London

Super Moderator

ScottXe

Registered User.

CJ_London

Super Moderator

Similar threads

Users who are viewing this thread