Table validation - not working

[Sheba]

hiya,

I have a number of validation rules on my table but violation of any of them gives a text message that lists the WHOLE syntax of the rules. The below table rule works until you violate it. Then after you correct the error, the validation text message wouldn't go away

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

I understand that it is better to put the validation rule on the form using VB but I need assistance with the code please. I wrote this code in the AfterUpdate event of [extra dosage] - i.e. the textbox txtExtra but it has bugs. I need assistance with this code please:

Private Sub txtExtra_AfterUpdate()
If [More MgSO4?] = "Yes" Then
If Len([extra dosage].Text & vbNullString) = 0 Then
MsgBox "Please provide the extra dose!", vbOK + vbExclamation
Else
[extra dosage].Text = Me.txtextra.Text
Len([extra dosage].Text) > 0
End If
Else Len([extra dosage].Text) = 0
End If
End Sub

[Sheba]

Just to clarify further:

Basically I have a combo box [More MgSO4?] with values "Yes"; "No"
If the user selects Yes, then the text box [extra dose] must not be null. The user must provide the dose

[June7]

Is [More MgSO4!] bound to a yes/no type field? If so, don't put Yes in quote marks. Actually, use True - not in quote marks.

The last Else should be two lines.

BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention. The ? mark is not good.

[Bob Fitz]

I would question the need for the yes/no field in the first place. This can be determined by the value of [extra dosage].

[Sheba]

Hi thanks for your responses. No it is not bound to a Yes/No control. It is bound to a combo box which drops down for the user to make a selection. As for the field names, that is why I enclosed them in [] brackets...?

Also could you advise what two codes those should be...? not clear to me

many thanks

[Bob Fitz]

No it is not bound to a Yes/No control. It is bound to a combo box which drops down for the user to make a selection.

Its value is shown with a combo box but if the value displayed is saved in the underlying table then it is bound to that field.

As for the field names, that is why I enclosed them in [] brackets...?

Yes we know only too well why you use them but a name like ExtraDosage is just as easy to read and does not need the use of square brackets. At some point you will forget to use the brackets and this will cause you problems. We see this all too often, so June's advice is worth taking.

[June7]

Your 'It is bound to a combobox' makes no sense. Is there a field in table for this Yes/No selection in combobox?

The Yes/No combobox seems extraneous. Why would user click the combobox and not just simply enter the value into [extra dosage]? What assurance is there the user will remember to make the Yes/No selection?

Yes, the [] force the bad field names to be accepted, but can be a nuisance to have to remember to type them. Follow good naming convention and will be fewer frustrations.

[Sheba]

the field in table and form are combo boxes

[Sheba]

field names cannot be changed. the user (there is only one) will remember to make a selection otherwise the validation code will remind them. There is a reason for this. all I need is assistance with the code.

[June7]

Did you try the suggestions in post 3?

Nothing in the code posted will require user to make selection in the combobox.

[Sheba]

Not sure I understand but I think True/False is for when the table field is a Yes/No type isn't it?
At the table level, data type IS text and the control is a combo box with value list "Yes"; "No

[June7]

Okay, I did say "If so".

Then there is the other suggestion to split a line to two.

Ooops. Just noticed the code uses .Text. Remove all of those. The property needed is Value and Value is default so don't need to type.

Why are you setting [Extra Dosage] to txtExtra.Text?

Also, this expression in both places is meaningless:
Len([extra dosage].Text) > 0

Private Sub txtExtra_AfterUpdate()
If Me.txtExtra = "Yes" Then
If Me.[extra dosage] & "" = "" Then
MsgBox "Please provide the extra dose!", vbOK + vbExclamation
End If
End If
End Sub

Step debug. Review link at bottom of my post for debugging guidelines.

[Sheba]

Many thanks for your help.