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

Help with Validation argument

P: 17
Hi
Hope you can help me with this
I run Access2000 on XP

Form has 3 fields:
  • cboSTATUS - which can be set via Limited value List A B or Complete. Default is set as A.
    cboTHIS - which can be set via Limited Value List X, Y or Z. No Default. Intiially field in blank (Null).
    txtTHAT - which can be anything. No Default text. Intiially field in blank (Null).


SCENE1: User goes to work on a record and initially all 3 fields are set as above (A, Null and Null).
The User sets cboStatus as Complete and attempts a Save/Close of the record.
I then run validations to ensure the other 2 fields have valid data for a Completed cboStatus setting.ie:
  • cboTHIS - cannot be Y or Null, but can be X or Z.
    txtTHAT - cannot be blank

If an ivalid entry is detected in THIS or THAT, msg box pops up and SaveClose action cancelled.

I set the validation arguments as:
If IsNull(Me.cboTHIS) Or Me.cboTHIS = "" Or Me.cboTHIS = "Y" And Me.cboStatus = "Completed" Then
MsgBox etc.....

ElseIf IsNull(Me.cboTHAT) Or Me.cboTHIS = "" And Me.cboStatus = "Completed" Then
MsgBox etc.....


I thought this worked a charm. I tested by having both THIS and THAT wrong, or both right, or one wrong, one right. Validation code worked as desired.
I tested by having THIS and THAT either wrong or right, with cboStatus set as A and as as expected, Save was actioned (because no Validation rule was broken since cboStatus was not set as Completed)

SCENE2: However I have found - if User later comes BACK into the record, after it passed the above validations.
Then changes
  • cboStatus to A
    changes cboTHIS to Y
    changes txtTHAT to nothing (Null, "")

and tries to SaveClose, the Validation code runs, and act as if the cboStatus is set as Completed. ie. it says THIS cannot be Y and THAT cannot be Null. I want , in this case to accept the save, because cboStatus is not set as Complete!

I think it's the Or bit in the argument? Because if I take that out, and have just the following, I have no problem with SCENE2. ie The User can save the form as it's been edited:
If IsNull(Me.cboTHIS) And Me.cboStatus = "Completed" Then
MsgBox etc....


Am I missing ( )or [ ] or ! or all, in crucial places?.
Hope this is not too confusing (you should be in my side of the screen :-))

Thanks for all help!
Cheers
AMBLY
Jun 12 '07 #1
Share this Question
Share on Google+
4 Replies


puppydogbuddy
Expert 100+
P: 1,923
If you want all the conditions to be true at the same time, try replacing each of the "or's" with "And" and see what happens.
Jun 12 '07 #2

P: 17
Hi puppydogbuddy

Thanks for replying!

But that doesn't make sense to me? how can for eg, cboTHIS be Null AND "Y" at the same time?

1. If IsNull(Me.cboTHIS) Or Me.cboTHIS = "" Or Me.cboTHIS = "Y" And Me.cboStatus = "Completed" Then
2. MsgBox etc.....


But I tried it out anyway - and if I change all the Or's to And essentially what happens is that everything is acceptable to Access - eg: it allows Y and Completed combination, which I don't want.

I still think, it could be because I'm not bracketing right, or perhaps there are too many arguments? ......Because I thought I understood that If IsNull(Me.field1) Or Me.Field1 = "" was good programming, I made each of my Validation arguments have that initial code before then either:
adding a second OR (if there was a 3rd thing field item to be validated) then adding the AND (specifying the text against which the IF and OR's were being checked against.

Anyway, what I have done is remove all the OR "" parts, and where there is a need for two OR's (Null or Y), I have just created 2 seperate ElseIFarguments, one after the other - and now it seems to work OK in all scenarios (so far anyway!)

cheers
AMBLY
Jun 13 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
Hi puppydogbuddy

Thanks for replying!

But that doesn't make sense to me? how can for eg, cboTHIS be Null AND "Y" at the same time?

1. If IsNull(Me.cboTHIS) Or Me.cboTHIS = "" Or Me.cboTHIS = "Y" And Me.cboStatus = "Completed" Then
2. MsgBox etc.....


But I tried it out anyway - and if I change all the Or's to And essentially what happens is that everything is acceptable to Access - eg: it allows Y and Completed combination, which I don't want.

I still think, it could be because I'm not bracketing right, or perhaps there are too many arguments? ......Because I thought I understood that If IsNull(Me.field1) Or Me.Field1 = "" was good programming, I made each of my Validation arguments have that initial code before then either:
adding a second OR (if there was a 3rd thing field item to be validated) then adding the AND (specifying the text against which the IF and OR's were being checked against.

Anyway, what I have done is remove all the OR "" parts, and where there is a need for two OR's (Null or Y), I have just created 2 seperate ElseIFarguments, one after the other - and now it seems to work OK in all scenarios (so far anyway!)

cheers
AMBLY
Ambly,
Glad you got it resolved, but just in case you need it, I am passing along this tip:

If(nz(Me!field1, "")) = "" does the same as your statement below without the "Or". It uses the "null to zero string function"..

If IsNull(Me.field1) Or Me.Field1 = ""
Jun 13 '07 #4

P: 17
Thanks Puppydogbuddy!

I will certainly store that one in my growing file of Access Tricks!
I might just have a go doing that instead of my (possibly not good) surgery on the code...

This forum is a great help for newbies like me, glad I found it.

Cheers
AMBLY
Jun 13 '07 #5

Post your reply

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