Uncheck all selected (yes/no) field (1 Viewer)

[Moote Tiira]

Anyone know how to build a button that can uncheck all selected record (yes/no) field in a form?

Thanks

 

[theDBguy]

You can execute an UPDATE query.

UPDATE TableName SET YesNoField=False WHERE YesNoField=True

 

[Moote Tiira]

You can execute an UPDATE query.

UPDATE TableName SET YesNoField=False WHERE YesNoField=True

Do you mean updating the query criteria or the table?

 

[The_Doc_Man]

If your form is bound, clearing the checkboxes on the form clears them in the table. However, since you seem confused about it, tell us what you wanted to do?

If the form is bound to a table, you can clear the check boxes on the form AND the table using the method suggested by theDBguy. BUT if you meant something else, please specify what you really wanted to do.

 

[Moote Tiira]

If your form is bound, clearing the checkboxes on the form clears them in the table. However, since you seem confused about it, tell us what you wanted to do?

If the form is bound to a table, you can clear the check boxes on the form AND the table using the method suggested by theDBguy. BUT if you meant something else, please specify what you really wanted to do.

I understand once a check box bound to a table what you do on the form also done in a table, But what I really meant is that in a datasheet form or a continuous form with check boxes at each field, is there a way to build (code builder) a button can select all or unselect all yes/no fields at one click(on click)?

 

[theDBguy]

I understand once a check box bound to a table what you do on the form also done in a table, But what I really meant is that in a datasheet form or a continuous form with check boxes at each field, is there a way to build (code builder) a button can select all or unselect all yes/no fields at one click(on click)?

Yes, that's what I meant. Your button's code will execute the UPDATE query.

 

[Uncle Gizmo]

See the Check/Uncheck video on my website here:-

Check List Extras - Nifty Access

Check List Extras I have two video series on YouTube which explain how to build Check-Lists in Microsoft Access. The first series shows how to build a single checklist and the second series of videos expands on the information provided for the single checklist, and demonstrates how to add...

www.niftyaccess.com

 

[Pat Hartman]

We are assuming your table is properly normalized and the question is regarding updating a single y/n field on multiple rows. If you are asking if there is any built-in function to update multiple columns of a single row, the answer is a resounding NO. Access is not Excel and relational databases do not have any internal functions to support working with a "group" of columns.. In Excel, most functions can work vertically (one column multiple rows) or horizontally (one row, multiple columns) but relational databases use functions that work on one column, multiple rows only.

If your table is not normalized, your best option is to normalize it. Otherwise, you're in for a lot of code to handle your unnormalized structure.

 

[John Sh]

We are assuming your table is properly normalized and the question is regarding updating a single y/n field on multiple rows. If you are asking if there is any built-in function to update multiple columns of a single row, the answer is a resounding NO. Access is not Excel and relational databases do not have any internal functions to support working with a "group" of columns.. In Excel, most functions can work vertically (one column multiple rows) or horizontally (one row, multiple columns) but relational databases use functions that work on one column, multiple rows only.

If your table is not normalized, your best option is to normalize it. Otherwise, you're in for a lot of code to handle your unnormalized structure.

Was that the royal "WE" or did you mean "I am assuming"?
Apart from a slap on the wrist for a possibly un-normalised table you have done nothing to help the OP.
As a novice myself, we come here for answers not rebukes!

 

[Moote Tiira]

Do you mean updating the query criteria or the table?

Thank you guys for your advice, it really helps, finally, I got what I am looking for, it is

To Select all use this code builder:
CurrentDb.Execute "UPDATE [Table Name] SET [Name of the Yes/No Field]=True", dbFailOnError
Me.Requery

To Unselect all use a code builder:
CurrentDb.Execute "UPDATE [Table Name] SET [Name of the Yes/No Field]=False", dbFailOnError
Me.Requery

 

[theDBguy]

Thank you guys for your advice, it really helps, finally, I got what I am looking for, it is

To Select all use this code builder:
CurrentDb.Execute "UPDATE [Table Name] SET [Name of the Yes/No Field]=True", dbFailOnError
Me.Requery

To Unselect all use a code builder:
CurrentDb.Execute "UPDATE [Table Name] SET [Name of the Yes/No Field]=False", dbFailOnError
Me.Requery

Hi. Congratulations! Glad to hear you got it sorted out. Cheers!

 

[Uncle Gizmo]

As a novice myself, we come here for answers not rebukes!

In order to answer a question it is important to understand the basic design of the dB.

Many dB's start out as Excel projects. The Excel data then moved to MS Access.

Unfortunately, this move from Excel to Access, which is a route adopted by many, causes significant problems in database design.

Pat has quite rightly asked a legitimate question, which will suss out if the OP's dB has this design issue.

For more info on this design issue which is a MAJOR mistake people new to MS Access make, see my blog....

Excel in Access Here:-

Excel in Access - Nifty Access

Excel in Access - Problem & Solution! One of the most frequent problems I see is people confusing MS Access with Microsoft Excel. This leads to fundamental design errors. Now, the problem is, this error "A Major Error" does not become apparent straight away. Unfortunately your database will work...

www.niftyaccess.com

 

[John Sh]

In order to answer a question it is important to understand the basic design of the dB.

Many dB's start out as Excel projects. The Excel data then moved to MS Access.

Unfortunately, this move from Excel to Access, which is a route adopted by many, causes significant problems in database design.

Pat has quite rightly asked a legitimate question, which will suss out if the OP's dB has this design issue.

For more info on this design issue which is a MAJOR mistake people new to MS Access make, see my blog....

Excel in Access Here:-

Excel in Access - Nifty Access

Excel in Access - Problem & Solution! One of the most frequent problems I see is people confusing MS Access with Microsoft Excel. This leads to fundamental design errors. Now, the problem is, this error "A Major Error" does not become apparent straight away. Unfortunately your database will work...

www.niftyaccess.com

Without dragging this out. I have no problem with the gist of "Pat's" reply. It was the manner in which he / she replied that I objected to. I have had similar responses myself and while they do nothing to answer a legitimate, if not ill advised, question. they certainly do huge damage to the confidence of the OP. I, personally, have seen fit to remove my membership from a forum that had, up until then, been quite helpful. It peeves me to see that sort of response on this forum.
That said, I think it is up to "Pat", not a third party, to respond to the OP, not me.

 

[Uncle Gizmo]

That said, I think it is up to "Pat", not a third party, to respond

I did not respond "for" Pat. She is very capable of responding herself.

There's more to helping people than just answering questions.

 

[NauticalGent]

There's more to helping people than just answering questions.

 

[Pat Hartman]

It was the manner in which he / she replied that I objected to

You can object all you want. But WHO made you the arbiter of good advice? Are you sure you don't want to ban me for "hate" speech?

If you don't think I am helpful, just say the word and I will put you on my ignore list so you won't have to be offended by my help.

Was that the royal "WE" or did you mean "I am assuming"?

Your initial post came after this remark so I certainly wasn't speaking for you. Apparently you don't recognize the assumptions that the experts who replied had to make to formulate a response. You also don't recognize that questions are how we get to the root of a problem. If you actually read the question, you would see that it is asking how to check/uncheck multiple controls on a form. That means that the table is NOT normalized and that is a basic problem. You cannot build a house on quicksand and that is what the OP is attempting to do. Rather than attempting to contribute, you got on your high horse to criticize me publicly and interfere with the helping process. But, that's OK, you're a novice and so far, you have contributed nothing to this thread except disruption. Children should be seen and not heard.

Don't forget, If you don't like my style, you are actually free to put me on your ignore list. However, I will give you another chance. I won't ignore you unless you tell me you want me to.