table validation again

[Cindy]

Hi, I have this table validation which I built using the VB expression builder:

IIf([More MgSO4?]="Yes",Not IsNull([extra dosage]),MsgBox("Provide the dose!"))

It doesn't work. If violated, how can I make this constraint give the message "Provide the dose!"

 

[vbaInet]

You like your table level validations don't you?

Is there any reason why you don't want to do it on form level by using the Before Update event of the control?

 

[Cindy]

okay I admit it! I'm avoiding really VB coding if I can help it

 

[vbaInet]

The more you run away from it the more distant Access would be from you

What you're trying to do is best done in a form. You will have total control over how the message should look as well and if there any extra layers of validations you need to add, it will be much easily done in the form than the table.

 

[Cindy]

Oh I don't want that just that this project can't wait for me to finish the VB training and master it before it is completed

This is what I have but the line is highlighted in red. What is wrong here?

Private Sub Text230_BeforeUpdate(Cancel As Integer)
With Me
If ![More MgSO4?] = "Yes" Then
Not IsNull(![extra dosage])
Else
Cancel = True
MsgBox ("Provide the dose!")
End If
End With
End Sub

 

[vbaInet]

I should be the one asking you what that second line means.

 

[Cindy]

I've commented the second line. What I want, is a message box to pop up when [More MgSO4?] is "Yes" AND [extra doasge] is left blank.

I changed the code and still got this error

Private Sub Text230_BeforeUpdate(Cancel As Integer)
'With Me
If [More MgSO4?] = "Yes" Then
Len([extra dosage])>0
Else
Cancel = True
MsgBox ("Provide the dose")
End If
'End With
End Sub

 

[vbaInet]

You need to learn the basics and understand how a conditional statement is built.

http://www.techonthenet.com/access/functions/advanced/if_then.php

 

[Cindy]

thanx for the link vbaInet. I've always known the basics to be:

IF condition
THEN result
ELSE consequence

I'm afraid it is still not working and I need to move quickly.

 

[vbaInet]

I'm afraid it is still not working and I need to move quickly.

You can't move quickly if your code isn't working

Len([extra dosage])>0

Anyway, what is the above doing in your code? Nothing really.

 

[Cindy]

On the form, I have the following validation rules for two textbox controls. If a combo box has value "Yes" then the textbox should not be empty.

When I violate it, it appears to work but after I put the missing data, the error message still pops up. How can I clean this up?

IIf([cmbMgSO4]="Yes",Len([txtExtra])>0,Len([txtExtra])=0)

IIf([txtHyperTagent]="No",Len([txtHyperTdose])=0,Len([txtHyperTdose])>0)

 

[Cindy]

Len([extra dosage])>0

By this I meant to say: "extra dosage field should not be empty.

I admit this was supposed to have been a small project but it has grown arms and legs in strange places and I'm loathe to let go of it

 

[MSAccessRookie]

MS Access uses a mildly different set of "Basics":

IF { [I]condition[/I] } THEN 
    [I]resul[/I]t 
ELSE
    [I]consequence[/I] 
END IF

Try to do it in a similar manner, and let us know what happens. Remember that the position of the Keywords will be important

 

[vbaInet]

Len([extra dosage])>0

By this I meant to say: "extra dosage field should not be empty.

Doesn't this constitute another IF check?

 

[vbaInet]

By the way Rookie, what happened there. You seemed to pasted the image thrice

 

[MSAccessRookie]

By the way Rookie, what happened there. You seemed to pasted the image thrice

vbaInet: I have no idea. It did not look like that when I posted it or I would have corrected it like I did after you pointed it out. Perhaps there is a curse on post #13?

-- Rookie

 

[Cindy]

hey Rookie, no curses on my thread I will try the nested IF statement and send out an SOS if it still doesn't work. I really hope it does. thanks y'all

 

[Cindy]

It did not work so I'm going with the table validation rule

IIf([More MgSO4?]="Yes",Not IsNull([extra dosage]),IsNull([extra dosage])) And
IIf([anti HyperT agent?]="Yes",Not IsNull([anti HyperT dosage]),IsNull([anti HyperT dosage]))

 

[vbaInet]

Just because you couldn't make it work doesn't mean you should revert to the old method which also didn't work for you.

 

[Cindy]

vbaInet - The validation rule worked (the only measure of success I've had with this particular constraint) until I needed to correct or violate a previously saved record...sigh!