De-select All Button

  • Thread starter Thread starter Adam
  • Start date Start date
A

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.
 
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,

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


Allen Browne said:
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.

Adam said:
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.
Click to expand...
Click to expand...
 
Adam said:
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;"
Click to expand...


You inserted an extra quote in front or True
 
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


Marshall Barton said:
Adam said:
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;"
Click to expand...


You inserted an extra quote in front or True
Click to expand...
 
Adam said:
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
Click to expand...


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

Maybe you don't have a Reference to the DAO library?
 
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
 
Adam said:
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
Click to expand...


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).
 
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"
 
Back
Top