Uncheck all checkboxes - update query question

[Guest]

I'm looking for a way to deselect all the checkboxes from an Access document.
Someone gave me this reply, but I have no idea how this works. Any help on
the matter would be appreciated.
This was the reply I got:

"The easiest way to deselect all the checkboxes is to run an update query
that sets the checkbox to false. Use the QBE to create it and then run it
in code using the .execute method. You should find examples in help."

I don't know how to do any of this... I've tried looking in the help file
for update query, but got stranded on like step 1 already. And what's a QBE?
And the .execute method?

Help please... just want to uncheck checkboxes, how hard can that really
be???

 

[Rob Parker]

Well, there's several questions in there. And to answer the last one
first - it's not hard at all ;-)

First off, what do you mean by an "Access document"? Access does not have
documents - it has tables, queries, forms and reports; and macros and
modules to store procedures or code which can be repeated.

I'm assuming that you want to change all the values of a Yes/No field in a
table which is used as the recordsource for a form/report, and which can be
represented in a form/report by checkboxes, to No (or false).

And to answer your third-last question first: The QBE is the Query By
Example grid window which is displayed when you create or open a query in
design view.

Are you needing to do this on a regular basis? If so, the suggestion to use
the .Execute method is good; it's what you'd use if you define the query in
some VBA code and then execute it - say, by clicking a button on a form.
And that's a quick answer to your second-last question - the .Execute method
is a command in VBA code which will execute an Access command. But if you
just want a one-off solution, then a simple update query - which updates
existing values in field(s) in a table - is probably all you need.

To set up your Update query using QBE, open a new query. Add your table in
the top section of the window. Then either double-click or drag-drop your
Yes/no field into the first Field cell in the tabl-like grid. Change the
query from the default Select query type to an Update query, via either the
Query menu, the query type icon on the query design toolbar, or the
QueryType option on the right-click menu in the top section of the QBE
window. [Yes, there's lots of ways to do things in Access, and different
people use/prefer different ways - that's why it can be confusing to learn
;-)]. Then, in the Update to row which has now appeared in the QBE grib,
enter "False" (without the quotes). Then run the query, either via the
Query menu or by clicking on the red exclamation mark icon on the query
design toolbar. Et voila, all your checkboxes will now be unchecked ;-)

If you chose to view your query in SQL view (via the View menu, or the View
icon on the query design toolbar (the left-most icon, unless you've
customised your toolbar)) you'll see something like:
UPDATE YourTableName SET YourTableName .[YourYesNoFieldName] = False;
That's the SQL string for your update query - it's what you would
define/build in VBA code and use if you wanted to run this via an .execute
method.

HTH,

Rob

 

[Guest]

It worked! It worked!
Glory Amen!
Thanks so much Rob... I just made the update query and got it to work. I
don't know how to make the VBA code though and I'd be curious about learning
that as well. It'd be easier for us (since we're using this with an entire
editorial department) if we just had one button to push, like you describe.
Could you tell me how I could go about creating something like that?

This is awesome man... Thanks so much!

Well, there's several questions in there. And to answer the last one
first - it's not hard at all ;-)

First off, what do you mean by an "Access document"? Access does not have
documents - it has tables, queries, forms and reports; and macros and
modules to store procedures or code which can be repeated.

I'm assuming that you want to change all the values of a Yes/No field in a
table which is used as the recordsource for a form/report, and which can be
represented in a form/report by checkboxes, to No (or false).

And to answer your third-last question first: The QBE is the Query By
Example grid window which is displayed when you create or open a query in
design view.

Are you needing to do this on a regular basis? If so, the suggestion to use
the .Execute method is good; it's what you'd use if you define the query in
some VBA code and then execute it - say, by clicking a button on a form.
And that's a quick answer to your second-last question - the .Execute method
is a command in VBA code which will execute an Access command. But if you
just want a one-off solution, then a simple update query - which updates
existing values in field(s) in a table - is probably all you need.

To set up your Update query using QBE, open a new query. Add your table in
the top section of the window. Then either double-click or drag-drop your
Yes/no field into the first Field cell in the tabl-like grid. Change the
query from the default Select query type to an Update query, via either the
Query menu, the query type icon on the query design toolbar, or the
QueryType option on the right-click menu in the top section of the QBE
window. [Yes, there's lots of ways to do things in Access, and different
people use/prefer different ways - that's why it can be confusing to learn
;-)]. Then, in the Update to row which has now appeared in the QBE grib,
enter "False" (without the quotes). Then run the query, either via the
Query menu or by clicking on the red exclamation mark icon on the query
design toolbar. Et voila, all your checkboxes will now be unchecked ;-)

If you chose to view your query in SQL view (via the View menu, or the View
icon on the query design toolbar (the left-most icon, unless you've
customised your toolbar)) you'll see something like:
UPDATE YourTableName SET YourTableName .[YourYesNoFieldName] = False;
That's the SQL string for your update query - it's what you would
define/build in VBA code and use if you wanted to run this via an .execute
method.

HTH,

Rob

I'm looking for a way to deselect all the checkboxes from an Access
document.
Someone gave me this reply, but I have no idea how this works. Any help on
the matter would be appreciated.
This was the reply I got:

"The easiest way to deselect all the checkboxes is to run an update query
that sets the checkbox to false. Use the QBE to create it and then run it
in code using the .execute method. You should find examples in help."

I don't know how to do any of this... I've tried looking in the help file
for update query, but got stranded on like step 1 already. And what's a
QBE?
And the .execute method?

Help please... just want to uncheck checkboxes, how hard can that really
be???

 

[Rob Parker]

Hi Xane,

Glad to have helped.

If you want to do this via a button on a form, you'll need a little bit of
code in the On Click Event Procedure of your button. Here's a couple of
ways of doing it; this uses the .Execute method:

Private Sub btnUncheckAll_Click()
Dim db As DAO.Database
Dim strSql As String

strSql = "UPDATE TableName SET CheckField = False;"
Set db = DBEngine(0)(0)
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) were changed."
Set db = Nothing
Requery 'show changes in form
End Sub


Or you can use the .RunSQL method, like this:

Private Sub btnUncheckAll_Click()
Dim strSql As String

strSql = "UPDATE TableName SET CheckField = False;"
DoCmd.RunSQL strSql
Requery 'show changes in form
End Sub


For a discussion of which method is preferable, see
http://allenbrowne.com/ser-60.html

Note also that there is no error handing in these examples. In a real
application that's something you must include. You might also want to wrap
this in some code to warn the users of the consequences of their action, and
confirm that they really want to do this.

You can, of course, save your update query and run it any time you want by
just double-clicking on its name in the query pane of the database window.
However, having saved action queries in a database with uneducated users can
be extremely dangerous ;-)

HTH,

Rob

It worked! It worked!
Glory Amen!
Thanks so much Rob... I just made the update query and got it to work. I
don't know how to make the VBA code though and I'd be curious about
learning
that as well. It'd be easier for us (since we're using this with an entire
editorial department) if we just had one button to push, like you
describe.
Could you tell me how I could go about creating something like that?

This is awesome man... Thanks so much!

<snip>