Help with Validation argument

[AMBLY]

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

[puppydogbuddy]

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.

[AMBLY]

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

[puppydogbuddy]

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 = ""

[AMBLY]

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