multiple table validations

[Cindy]

Could someone help me please? I have to assign multiple table validations to my table but they are not even working separately. What am I doing wrong here? Any help is most appreciated

1) I have two combo box controls: [Tocolytics] & [multiple_Tocolytics], 3 check boxes and 1 text box.
Rule:
IIf(([Tocolytic?]="Yes" Or [Multiple Tocolytic?]="Yes"),([Indomethacin]=True Or [Nifedipine]=True Or [Nitroglycerin]=True Or Len([Other])>0),"check one of the boxes")

2)Two combo boxes The answers are mutually exclusive. If one is yes then the other must be "No" or "unable to determine"
Rule:
IIf(Oxytocin Infusion - no PPH]="Yes",([Oxytocin Infusion - if PPH]="No" Or [Oxytocin Infusion - if PPH]="Unable to determine"),[Oxytocin Infusion - if PPH]="Yes")

 

[CJ_London]

your Iif statements do not look right as a way of describing the problem. For example

IIf(([Tocolytic?]="Yes" Or [Multiple Tocolytic?]="Yes"),([Indomethacin]=True Or [Nifedipine]=True Or [Nitroglycerin]=True Or Len([Other])>0),"check one of the boxes")

This reads as

if ([Tocolytic?]="Yes" Or [Multiple Tocolytic?]="Yes")
then ([Indomethacin]=True Or [Nifedipine]=True Or [Nitroglycerin]=True Or Len([Other])>0)
otherwise "check one of the boxes"

You can see the then part does not make sense - you can't have OR's

Can you write out more clearly what you mean

 

[plog]

First, you've not explained your rules you've only provided code, so I can only help you syntatically. Here's what I see--mismatched brackets. Count left and right brackets in your second rule: You have 3 Left and 4 Right. Left Brackets should equal Right brackets and the same goes for parenthesis.

Next, if you want to nest Iif statements, you need to retype the "Iif" part:

IIf(([Tocolytic?]="Yes" Or [Multiple Tocolytic?]="Yes"),([Indomethacin]=True...

You need another Iif after that comma.

 

[Cindy]

hey CJ and Plog, to be clear I have attached two files containing parts of the screen.

Tocolytic(s) are drugs administered to women in labour. We need to track which ones were given. I have 2 fields for these if either of them is "Yes" then the user has to specify which of the drugs was given. There are 4 options. At least one of these 4 options must be specified.

If both fields are "No" then none of the options needs to be confirmed. I hope this makes it clearer..?

 

[Cindy]

The second seems to be working now. I had my parentheses wrong. This is what I have and it is working.

IIf([Oxytocin Infusion - no PPH]="Yes",([Oxytocin Infusion - if PPH]="No" Or [Oxytocin Infusion - if PPH]="Unable to determine"),[Oxytocin Infusion - if PPH]="Yes")

 

[plog]

That seems pointlessly redundantly. Why not just give them the check boxes and let them select which ones were given? Then, if they select none you know it wasn't given and if multiple ones are checked you know multiple drugs where given.

Making it a two parter and enforcing immediate congruity between them accomplishes nothing for you.

 

[Cindy]

That is the design that was agreed upon and prefer to go with.

 

[CJ_London]

Does this do what you want (for logic)? Not sure of the context since it was either returning a true/false value or text

IIf(([Tocolytic?]="Yes" Or [Multiple Tocolytic?]="Yes") AND ([Indomethacin]=false AND [Nifedipine]=false and [Nitroglycerin]=false and Len([Other])=0),"check one of the boxes","OK")

 

[Cindy]

Hi CJ, no it is not working :banghead:. Could you help advise how I can correctly represent the table rule?

 

[vbaInet]

If I were you I wouldn't spend too much time fiddling with table level validation. Do it on your form.

And instead of "Yes" or "No", it's Yes or No (without quotes), -1 or 0, or even True or False. "Yes" is text and doesn't interpret as Yes (-1).

 

[Cindy]

Thanks vbaInet. On the form, that would be in the Change Event procedure...wouldn't it?

 

[vbaInet]

Before Update event of the form

Or

After Update event of each of the 6 fields used in the condition.

 

[Cindy]

um.. sorry I meant the After Update event

 

[Cindy]

hm oookay. I'll try that. hopefully it'll work.

 

[Cindy]

Excuse my programming inexperience. vbaInet, I reckon that in the Before Update event of the form the controls should be initialised...? This is the code that I have there but it incomplete. The controls involved here (see attachment as well) are 2 combo boxes, 3 checkboxes and one text box. If either of the combo boxes is Yes, then at least one of the rest 4 controls must be true (i.e. some of them will be false and others true). If both of them are No, then all of them remain false.

Could you help me out with this code please. I'm know where I want to go but my VB skills can't take me there right now. Your help is greatly appreciated

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.chkIndomethacin.Value = False
Me.chkNifedipine.Value = False
Me.chkNitroglycerin.Value = False
If Len(txtOther.Text & vbNullString) = 0 Then
txtOther.Text = vbNullString
Else
????????????????
End If
End Sub
**************************
after update for Tocolytic combo box:
=IIf([cmbTocolytic]="Yes",
([chkIndomethacin]="True" Or [chkNifedipine]="True" Or [chkNitroglycerin]="True" Or Len([txtOther])>0),
([chkIndomethacin]="False" Or [chkNifedipine]="False" Or [chkNitroglycerin]="False" Or Len([txtOther])=0))
***************************************************************
after update for Multiple Tocolytic combo box:
=IIf([cmbMultipleTocolytic]="Yes",([chkIndomethacin]=True Or [chkNifedipine]=True Or [chkNitroglycerin]=True Or Len([txtOther])>0),([chkIndomethacin]=False Or [chkNifedipine]=False Or [chkNitroglycerin]=False Or Len([txtOther])=0))

I did know how to code the check boxes because they can be checked or unchecked when either of the combo boxes is "Yes. I hope I'm making sense?

 

[vbaInet]

I already explained in post #10 that you should be using either Yes (no quotes) or True or -1 instead of "Yes". Yes (no quotes), True and -1 translate to the same thing in Access, whereas "Yes" (with quotes) doesn't.

Secondly, all the validation code needs to go in one event. If you want the conditions tested after you make a selection in one combo box, the validation should go in the After Update event of that combo box, not the Control Source.

 

[Cindy]

Okay this is the code that I need to clean up. Just don't know how. Please forgive the syntax & programming errors

Private Sub Form_BeforeUpdate(Cancel As Integer)
const string vbNullString
Me.chkIndomethacin.Value = False
Me.chkNifedipine.Value = False
Me.chkNitroglycerin.Value = False
If Len(txtOther.Text & vbNullString) = 0 Then
txtOther.Text = vbNullString
me.cmbTocolytic.text= vbNullString
me.cmbMultipleTocolytic = vbNullString

If me.cmbTocolytic.text="Yes" Then
me.chkIndomethacin =True
Else
chkNifedipine = True
Else
chkNitroglycerin = True
Else
Len([txtOther])>0),([chkIndomethacin]="False" Or [chkNifedipine]="False" Or [chkNitroglycerin]="False" Or Len([txtOther])=0))
End If
End Sub

 

[Cindy]

sorry yes. I took the quote marks off but put them in again out of habit, when I was typing.

many thanks vbaInet. Okay so in this regard, all this code is going into the AfterUpdate event of both combo boxes then. Since their values' determine those of the check boxes...

 

[vbaInet]

But I can still see them in your code?

 

[Cindy]

I'm still trying to figure out though how to code the check boxes. I can't use OR and the ELSE is looking really wrong from here