De-select All Button

[Adam]

Hello,

I have a form with a list of items for a shopping list. I have check boxes
next to them that add them to the shopping list when selected. Is there a
way to add a master check box at the top of the form to de-select all of the
boxes when it is selected? It is very annoying to have to go in and
de-select all of the items every time I start a new shopping list.

Any help is much appreciated.

 

[Allen Browne]

Assumptions:
- The check boxes are bound to a yes/no field named Shop4This in a table
named Table1.

- The form is in Continuous View, so you can see many rows, but it has a
Form Header section, and it is bound to Table1.

Steps:
1. Add a command button to the Form Header section.

2. Set its On Click property to:
[Event Procedure]

3. Click the Build Button (...) beside this.
Access opens the code window.

4. Between the "Private Sub..." and "End Sub" lines, enter this:

Dim strSql As String
If Me.Dirty Then Me.Dirty = False 'save any edits
strSql = "UPDATE [Table1] SET [Shop4This] = False WHERE [Shop4This] =
True;"
dbEngine(0)(0).Execute strSql, dbFailOnError

For an explanation of what the code is doing, this might help:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

 

[Adam]

Allen,

It didn't work. I got a message that said syntax error. The that starts
with strSql = "UPDATE" is hilighted in red so I'm guessing the error is in
that part. Here is what I put. My table is called Items and the field I am
trying to de-select is called Need.

Option Compare Database

Private Sub Command12_Click()
Dim strSql As String
If Me.Dirty Then Me.Dirty = False 'save any edits
strSql = "UPDATE [Items] SET [Need] = False WHERE [Need] = "True;"
DBEngine(0)(0).Execute strSql, dbFailOnError
End Sub

Private Sub Exit_All_Items_Click()
On Error GoTo Err_Exit_All_Items_Click


DoCmd.Close

Exit_Exit_All_Items_Click:
Exit Sub

Err_Exit_All_Items_Click:
MsgBox Err.Description
Resume Exit_Exit_All_Items_Click

End Sub

Assumptions:
- The check boxes are bound to a yes/no field named Shop4This in a table
named Table1.

- The form is in Continuous View, so you can see many rows, but it has a
Form Header section, and it is bound to Table1.

Steps:
1. Add a command button to the Form Header section.

2. Set its On Click property to:
[Event Procedure]

3. Click the Build Button (...) beside this.
Access opens the code window.

4. Between the "Private Sub..." and "End Sub" lines, enter this:

Dim strSql As String
If Me.Dirty Then Me.Dirty = False 'save any edits
strSql = "UPDATE [Table1] SET [Shop4This] = False WHERE [Shop4This] =
True;"
dbEngine(0)(0).Execute strSql, dbFailOnError

For an explanation of what the code is doing, this might help:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a form with a list of items for a shopping list. I have check
boxes
next to them that add them to the shopping list when selected. Is there a
way to add a master check box at the top of the form to de-select all of
the
boxes when it is selected? It is very annoying to have to go in and
de-select all of the items every time I start a new shopping list.

Any help is much appreciated.

 

[Marshall Barton]

It didn't work. I got a message that said syntax error. The that starts
with strSql = "UPDATE" is hilighted in red so I'm guessing the error is in
that part. Here is what I put. My table is called Items and the field I am
trying to de-select is called Need.

Option Compare Database

Private Sub Command12_Click()
Dim strSql As String
If Me.Dirty Then Me.Dirty = False 'save any edits
strSql = "UPDATE [Items] SET [Need] = False WHERE [Need] = "True;"

You inserted an extra quote in front or True

 

[Adam]

I tried this and it still didn't work.

Option Compare Database

Private Sub Command14_Click()
Function UnpickAll()
Dim db As DAO.Database
Dim strSql As String

strSql = "UPDATE Items SET Need = False WHERE Need = True;"
Set db = DBEngine(0)(0)
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) were unpicked."
Set db = Nothing
End Function

It didn't work. I got a message that said syntax error. The that starts
with strSql = "UPDATE" is hilighted in red so I'm guessing the error is in
that part. Here is what I put. My table is called Items and the field I am
trying to de-select is called Need.

Option Compare Database

Private Sub Command12_Click()
Dim strSql As String
If Me.Dirty Then Me.Dirty = False 'save any edits
strSql = "UPDATE [Items] SET [Need] = False WHERE [Need] = "True;"

You inserted an extra quote in front or True

 

[Marshall Barton]

I tried this and it still didn't work.

Option Compare Database

Private Sub Command14_Click()
Function UnpickAll()
Dim db As DAO.Database
Dim strSql As String

strSql = "UPDATE Items SET Need = False WHERE Need = True;"
Set db = DBEngine(0)(0)
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) were unpicked."
Set db = Nothing
End Function

How about a clue to what "didn't work" means?

Maybe you don't have a Reference to the DAO library?

 

[Adam]

It says Compile Error. Expected End Sub.

Here is what is written when I click the button.

Option Compare Database

Private Sub Command14_Click()
Function UnpickAll()
Dim db As DAO.Database
Dim strSql As String

strSql = "UPDATE Items SET Need = False WHERE Need = True;"
Set db = DBEngine(0)(0)
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) were unpicked."
Set db = Nothing
End Function


Private Sub Exit_All_Items_Click()

End Sub
Private Sub Command15_Click()
On Error GoTo Err_Command15_Click


DoCmd.Close

Exit_Command15_Click:
Exit Sub

Err_Command15_Click:
MsgBox Err.Description
Resume Exit_Command15_Click

End Sub

 

[Marshall Barton]

It says Compile Error. Expected End Sub.

Here is what is written when I click the button.

Option Compare Database

Private Sub Command14_Click()
Function UnpickAll()
Dim db As DAO.Database
Dim strSql As String

strSql = "UPDATE Items SET Need = False WHERE Need = True;"
Set db = DBEngine(0)(0)
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) were unpicked."
Set db = Nothing
End Function


Private Sub Exit_All_Items_Click()

End Sub
Private Sub Command15_Click()
On Error GoTo Err_Command15_Click


DoCmd.Close

Exit_Command15_Click:
Exit Sub

Err_Command15_Click:
MsgBox Err.Description
Resume Exit_Command15_Click

End Sub

It seems like every time you touch your code, it gets worse
;-)

The readability of our code would improve if you'd clean it
up so you don't have an empty procedure and get rid of the
extra
Private Sub Command14_Click()
line that is causing the current error. The function itself
looks ok to me.

You should also do yourself a favor and rename your controls
to something more meaningful than Command15 (a common naming
convention suggests cmdClose).

 

[Douglas J. Steele]

You've put the code for the UnpickAll function inside the code for the
Command14_Click sub.

If your desire is to have the UnpickAll code run with Command14 is clicked
(and I have to agree with Marsh that you really should give your controls
meaningful names!), remove the lines "Function UnpickAll()" and "End
Function"