Multiple Validation Rule

[gatsby]

how to have a multiple validation rule either in tables or queries?

i have a combobox with several recordsource or sub modules that after you chose a sub module it will give a validation rule. each sub modules has its own validation rule. validation rules are just numbers in-between like :

"sub module A" should have >5 And <25 Then MsgBox ""Please enter a numeric value between 5 and 25"
"sub module B" should have >25 And <55 Then MsgBox ""Please enter a numeric value between 25 and 55"
"sub module C" should have >75 And <95 Then MsgBox ""Please enter a numeric value between 75 and 95"


and so on......

users should do a manual entry on "Local X" (field where validation rule should take effect)....


By the way, im new to Access and VBA but i created simple databases already. MS Access version is 2010.
Thank You!

[June7]

Did you try the ValidationRule property in the table or in control on form?

[gatsby]

Did you try the ValidationRule property in the table or in control on form?

yup i tried and i failed. all i know is single validation only and NOT multiple. when it comes to multiple, im having problems.

[June7]

Why did it fail? I don't see anything wrong with the rule you show if the field is required to have a value. If you want to allow user to skip entering value in the field, try

Is Null Or (>=5 And <=25)

[gatsby]

Why did it fail? I don't see anything wrong with the rule you show if the field is required to have a value. If you want to allow user to skip entering value in the field, try

Is Null Or (>=5 And <=25)

not skip, it should have a value so i dont use the null....


i have this (pls correct):

[Local X]='Deck' Between "«Expr» 5" And "«Expr» 25" <---- Local X is the Field (where users enter a value)... Deck is the Sub Module (selected from combobox)... Assembly is the name combobox... the validation rule is >5 and < 25 (for Deck ONLY)........

another problem is, how should i add other Sub Module in the same field. the combobox has 6 Sub Modules - each has its own validation rule.

[June7]

Use VBA code.

Options:

1. Since you have two comboboxes, make them dependent (cascading). Restrict the choices in Assembly to a list that is valid for the [Local X] selection. Review: http://datapigtechnologies.com/flash...combobox2.html

Or make Assembly a textbox then:

2. In [Local X] combobox AfterUpdate event, set the Assembly ValidationRule property

3. In Assembly combobox AfterUpdate event, check that the enter value is valid for the [Local X] selection

[gatsby]

no, i only have 1 combobox. and this combobox has 5 records that each of the record must have its own validation rule. and i dont know how to create a code and put them all together that if the user will chose 1 record a certain validation rule is to be executed, then the next record has a different validation rule, and so on.... an IF STATEMENT perhaps or a CASE statement perhaps.

i tried it in the AfterUpdate event but still i failed.

[June7]

You have a combobox with 5 items?

You have a textbox for entry of a number value (earlier I thought you said this was also a combobox)?

You need code that will check validity of entered number based on the selection in combobox?

The options suggested are still relevant.

What did you try in the UpdateEvent? Why didn't it work - error message, wrong results, nothing happened? Show the code you built. Yes, a Case statement probably suitable.

[gatsby]

yes its a combobox. and the combobox has 5 records in it. whenever a record is selected there is a certain validation rule, and the user must encode or enter numbers in an unbound textbox which should follow the validation rule that is going to be set....

here is the code i created, please dont laugh because im not really an expert in vba, if i am im not here anymore, hehehe:

If [lkpAssembly] = Deck(Me.Local X, 0) < 5 Or Nz(Me.Local X, 30) > 25 Then
MsgBox "Please enter a numeric value between 5 and 25"
ElseIf [lkpAssembly] = DSF(Me.Local X, 0) < 25 Or Nz(Me.Local X, 50) > 45 Then
MsgBox "Please enter a numeric value between 25 and 45"
ElseIf [lkpAssembly] = Flareboom(Me.Local X, 0) < 55 Or Nz(Me.Local X, 80) > 75 Then
MsgBox "Please enter a numeric value between 55 and 75"
ElseIf [lkpAssembly] = LQ Module(Me.Local X, 0) < 65 Or Nz(Me.Local X, 90) > 85 Then
MsgBox "Please enter a numeric value between 65 and 85"
ElseIf [lkpAssembly] = LSF(Me.Local X, 0) < 75 Or Nz(Me.Local X, 100) > 95 Then
MsgBox "Please enter a numeric value between 75 and 95"
End If



lkpAssembly - is the name of the combobox
Deck, DSF, Flareboom, LQ Module, LSF - are the records in the combox
Local X - name of the field in the table, form, and query


i know how to set 1 validation rule only, the simple validation rule (in the Table), BUT if its multiple validation rule that needs coding that i DONT know how thats why i need your/someone's help.

Thanks.

[June7]

You are using Deck, DSF, Flarebloom, Module, LSF like functions. Why?

Is this what you want:

If [lkpAssembly] = "Deck" And (Nz(Me.[Local X], 0) < 5 Or Nz(Me.[Local X], 30) > 25) Then
MsgBox "Please enter a numeric value between 5 and 25"
ElseIf [lkpAssembly] = "DSF" And (Nz(Me.[Local X], 0) < 25 Or Nz(Me.[Local X], 50) > 45) Then
MsgBox "Please enter a numeric value between 25 and 45"
ElseIf [lkpAssembly] = "Flareboom" And (Nz(Me.[Local X], 0) < 55 Or Nz(Me.[Local X], 80) > 75) Then
MsgBox "Please enter a numeric value between 55 and 75"
ElseIf [lkpAssembly] = "LQ Module" And (Nz(Me.[Local X], 0) < 65 Or Nz(Me.[Local X], 90) > 85) Then
MsgBox "Please enter a numeric value between 65 and 85"
ElseIf [lkpAssembly] = "LSF" And (Nz(Me.[Local X], 0) < 75 Or Nz(Me.[Local X], 100) > 95) Then
MsgBox "Please enter a numeric value between 75 and 95"
End If

Literal text must be enclosed in quote marks.
Advise not to use spaces or special characters in names nor reserved words as names. If used, must enclose in [].

[gatsby]

those are just names for the records in the combobox (Deck, DSF, Flareboom, Module, LSF).

thanks for the tips about enclosing in quotes marks, and not to use spaces, etc...

anyway, i tried the codes you corrected but still its not working. it accepts whatever numbers the users key-in. there are no validation rules that took place. what i did, i clicked on the combobox in design view and click on the AfterUpdate event of the combobox and here how it looks like:

Private Sub lkpAssembly_AfterUpdate()

lkpQuery.Requery

If [lkpAssembly] = "Deck" And (Nz(Me.[Local X], 0) > 5 Or Nz(Me.[Local X], 30) < 25) Then
MsgBox "Please enter a numeric value between 5 and 25"
ElseIf [lkpAssembly] = "DSF" And (Nz(Me.[Local X], 0) > 25 Or Nz(Me.[Local X], 50) < 45) Then
MsgBox "Please enter a numeric value between 25 and 45"
ElseIf [lkpAssembly] = "Flareboom" And (Nz(Me.[Local X], 0) > 55 Or Nz(Me.[Local X], 80) < 75) Then
MsgBox "Please enter a numeric value between 55 and 75"
ElseIf [lkpAssembly] = "LQ Module" And (Nz(Me.[Local X], 0) > 65 Or Nz(Me.[Local X], 90) < 85) Then
MsgBox "Please enter a numeric value between 65 and 85"
ElseIf [lkpAssembly] = "LSF" And (Nz(Me.[Local X], 0) > 75 Or Nz(Me.[Local X], 100) < 95) Then
MsgBox "Please enter a numeric value between 75 and 95"
End If

End Sub

Private Sub lkpAssembly_Enter()

End Sub

[June7]

That code needs to go in the [Local X] textbox AfterUpdate event. Sorry, it appears I had control references messed up in the suggestions listed in post 6.

[gatsby]

no its okay, no need to apologize, i guess im the one who will apologize here since im a newbie here and i dont get it at first glance.

i have a question, is there a chance that these validation rules will be placed in Tables or Queries and not in Form (AfterUpdate event)?

i asked because whenever i view the database into design view everything is just plain white since it is done in a subform/subreport type of sheet, in this manner i could not locate where [Local X] is. for the combobox i can since it is not part of subform/subreport.... attached is the photo

Attachment 10727

[June7]

[Local X] is in the subform query? Can't have VBA code behind tables and queries. Would need to build a form to use as a subform.

If the combobox is on mainform, code in subform needs to reference the mainform like:

If Forms!WCRArchitectural![lkpAssembly] =

[gatsby]

yes [Local X] is in the subform query.

and yes also, the combobox (Assembly) has this code/criteria in the query:

Like [forms]![WCRArchitectural]![lkpAssembly]


so now, where should i put the validation rules if the case is something like that --- [Local X] is in subform query?

sorry if i ask too much, its just that been working with this for days now but i cant figure it out, i really need help.

thanks!