Validation Rule for Yes/No Fields

[ledmark]

Hello - I have a number of Yes/No fields - one for yes and one for no. How do I write the validation Rule or VBA so they can only mark Yes or No, but not both?

Thank you for any help.

Laura

 

[rainman89]

why not just use one yes/no field for each one??? if its checked then yes, else no??

 

[ledmark]

I actually have it this way but they seem to think people will check both if given the opportunity so they want to take the opportunity away from them. If there isn't a way I totally understand and will let them know that.

Laura

 

[Scooterbug]

What he means it to have just one check box per Yes/No field. If it's suppose to be yes, the check the box. If it's suppose to be no, they leave it blank.

 

[MSAccessRookie]

For the record, I fail to understand the need for two Yes/No switches for each entry. Rainman and Scooterbug have the right idea.

BUT:


Since that is how you have it, why not try a toggle switch. In the On Modify Event for each of the cases, set the other case to the opposite position. In other words:

• If the Yes Field is turned On, then the No Field would be turned Off.
• If the Yes Field is turned Off, then the No Field would be turned On.

This way only the last one selected would be enabled.

 

[ledmark]

The toggle switch is a great idea - than you all for your help.

Laura

 

[Mr. B]

Laura,

If you place your two check boxes (one for Yes and one for no) in a Group or Frame control, then only one of them can be checked at any one time and you will not need any code.

HTH

 

[MSAccessRookie]

Laura,

If you place your two check boxes (one for Yes and one for no) in a Group or Frame control, then only one of them can be checked at any one time and you will not need any code.

HTH

Excellent Point. I have never used two check boxes like she is doing, but what you are saying is absolutely true, and probably a better option than mine.

 

[SOS]

Having two fields - one yes and one no is NOT good database design. It should be ONE field only Yes is selected, NO is not.

 

[MSAccessRookie]

Having two fields - one yes and one no is NOT good database design. It should be ONE field only Yes is selected, NO is not.

I agree with you 100%. That is the way I would have approached it. My point was related to the fact that two fields appeared to be a part of the requirement.

 

[Mr. B]

I must put my two cents worth in.

First, Laura, when I first read your statement, I did not really catch the fact that you state that you have one field for a "Yes" answer and another field for a "No" answer. The solution that I posted will not work to allow you to use both fields. You would have to write some code to do that.

I must agree with Smeghead that this is not good database design, but I would like to know if there is something that makes someone at your place think that this is needed?

Please post back and let's look at this one. Sorry for not getting it right the first time. I have got to get new glasses. LOL or a brain.

HTH

 

[ledmark]

Hello and thank you for all the responses. Since I am continuously learning I appreciate all the feedback - I put two fields on the form so they would have a visable choice. I tried to do the toggle switch and couldn't find any Event named On Modify - where is this located?

So if I'm hearing everyone correctly there should be one field for yes. There is really no need for a "no" field. I have a lot of people both clinical and administrative who want Yes/No for visual purposes but I will do what is best for the database.

I don't know how to put the two boxes in a Frame or Group control so I will just eliminate the No field.

Thank you and any more discussion is welcome and wanted.

Laura

 

[SOS]

Hello and thank you for all the responses. Since I am continuously learning I appreciate all the feedback - I put two fields on the form so they would have a visable choice. I tried to do the toggle switch and couldn't find any Event named On Modify - where is this located?

So if I'm hearing everyone correctly there should be one field for yes. There is really no need for a "no" field. I have a lot of people both clinical and administrative who want Yes/No for visual purposes but I will do what is best for the database.

I don't know how to put the two boxes in a Frame or Group control so I will just eliminate the No field.

Thank you and any more discussion is welcome and wanted.

Laura

Actually, I like the option group method with a Yes/No and then you can set the value of yes to -1 and No to 0 and then it is just like having a checkbox - you get two things (a yes and a no) but you store only the value of the option group frame to the bound field.

 

[rainman89]

I tried to do the toggle switch and couldn't find any Event named On Modify - where is this located?

Just use onclick. it should do the same thing

 

[Mr. B]

Laura,

After looking at this and thinking about it over night, I decided to put together a test db for you to look at.

Look at the attachment and check out how this can be handled. I will take a little work (you will not use the group control) but it can be done and will work with just one Yes/No field.

 

[ledmark]

Wow - thank you for all the answers!!

Mr. B - I have looked at your example and this is what I see:

You have put two check boxes on the form - Yes and No - which hhas code written for it to determine what answer goes into the answer box, which is not visable.

The Control Source for the answer box would be the YES/No field in the table.

Do I have this correct in my head?

Laura

 

[Mr. B]

Yes, Laura, you do have it correct.

You would need to repeat the process for each question, but that is how I think you can do what you want to do.

If I were to look at it a little more, we might be able to write a function that would handle all questions without you having to have all of the code in the After Update event of each of the controls, but for now, give it a try this way.

 

[ledmark]

OK - let me try to figure this out in what I currently have built. I have to add a question field in the table for each yes/no field. I want to put the the question as a default so I can delete the lable and the question auto fills - otherwise the user wouldn't get it because it would be blank when the form opens up. Does that make sense - I'll get back to you on Friday since I won't be at work tomorrow - or tomorrow night from home.

One other question - in the code for the Yes/No check boxes do I put the name of the source where the answer will be stored or the word Answer like you have?

Thank you for your help!

Laura

 

[Mr. B]

I was only using a single table for demo purposes. If this structure will suffice your requirements, then you can use the "Question" field. I only did this so I could show the relationship between the answer and the question. Doing it this way makes it possible to modify questions and/or add new questions, etc. You might also want to add an "Active" field to the table as a Yes/No field so you can easily remove a question but not actually delete it.

If your users will be opening your form at a point where some questions have already been answered, then you would need some code to set the various check boxes for each question based on the value of the "Answer" field (or what ever you call it)

Yes, the Control Source for the check box that is not visible would be the field where the value as determined by user selections is to be stored.

Feel free to get back to me at your convenience. I may have time to look into creating that Function that I mentioned earlier.

If you have a chance, check out my web site: www.askdoctoraccess.com

HTH

 

[ledmark]

OK - I've been messing around with this for a couple of hours and can't get it to work. I added Question1, Question2, and Question3 to the table and put the question in as a default value so when the form opens it looks the same to the user. I then added the yes and no check boxes, changed the name in the properties of each to chkYes and chkNo and put the StableTransfer-Yes (which is the yes no field I want filled) and made it not visable. I then put the code in for the Yes checkbox like this:

If M.chkYes = -1 Then
Me.chkNo = 0
Me.chkStableTransfer-Yes = -1
Else
Me.chkNo = -1
Me.chkStableTransfer-Yes = 0
End If

Then did the same for the No Checkbox:
If Me.chkNo = -1 Then
Me.chkYes = 0
Me.chkStableTransfter-Yes = 0
Else
Me.chkYes = -1
Me.chkStableTransfer-Yes = -1
End If
End Sub

It doesn't work - you can check both boxes without something stopping you and it doesn't register in the table. I have no idea what I'm doing wrong.It looks just like what you did and is set up the same way but no go. Did I miss something?

Laura