Follow Up to Validating Two Fields 2

[mellay]

Hi! Concerning Validaton between two controls on a form. How would you force a null value in one control if another is populated? For Example, If a YES field is populated with a 'Y', how can I keep the corresponding NO field unpopulated? I am having situations where the user will populate both fields, showing both a 'Y' and an 'N'. Thank you!! Any help would be greatly appreciated! mellay

(Many thanks to Scoty and mrf1xa on my previous post!)

 

[jebry]

Hi!

You can use this code:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Nz(Len(txtYes), 0) <> 0 And Nz(Len(txtNo), 0) <> 0 Then
Call MsgBox(&quot;You have put information in both the yes field and the no field. You must leave one of the fields blank.&quot
Cancel = -1
Call txtYes.SetFocus
End If

End Sub

If you need to test two fields together, usually the proper place to do it is in the Before Update of the form.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[mellay]

Hi Jeff!! I finally have it working with one exception. If the User puts an 'N' in the NO Box First and then puts a 'Y' in the YES Box second, it works fine. But if the user puts the 'Y' in the YES Box first, and the 'N' in the No box second, it will not work. I wish I could have it both ways!

Any ideas? I really appreciate your help!! mellay

 

[mrf1xa]

I may be missing something obvious, but wouldn't it be easier to use an option group- say with radio buttons- to select yes or no for this field? This would allow you to select only one at a time.....

Nigel

 

[jebry]

Hi again!

Are you using the Form's Before Update event, it sounds like you are using one of the combo box's before update events. If you don't actually need to store this as a string (y or n) then Nigel's suggestion may be best. If you are interested in implementing it that way, let us know.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[mellay]

Hi Jeff and Nigel!

I have figured it out and it is working!! Thanks to both of you! My database is unique in a way, my supervisor insists on doing it this way. We have to count the total 'Y's and total 'N's for comparison. I am trying to &quot;bend&quot; Access to do what my supervisor wants! I am enjoying working with Access! Thank You Both!!

This forum is really great! Experts like yourself will take the time to work with first time users, etc. I really appreciate your patience and understanding! Again, Thanks!! mellay