control button

[ben]

I have a form that allows me to select records by
checking the corresponding box. I had it setup so every
time the form was opened, the boxes would clear (set to
false). I have found this not to be the ideal situation,
and i want to create a button that will clear the check
boxes.
I assume this is a fairly common code, but i can't find
it anywhere.

Any help will be appreciated
Ben

 

[Sandra Daigle]

The best way would be using an update query or inline SQL. The following
shows how to do this using inline SQL. If you would rather use a saved query
you can simply replace the SQL string with the name of the saved query. The
message box is included for confirmation purposes - you might want to remove
it.

This code updates a field named 'fSelect' in a table named 'customer':

Private Sub cmdClearAll_Click()
Dim db As DAO.Database
Set db = CurrentDb()
db.Execute ("Update customer set fSelect=false;")
'For Test Purposes
MsgBox db.RecordsAffected & " Records Updated"
Set db = Nothing
End Sub

Private Sub cmdSetAll_Click()
Dim db As DAO.Database
Set db = CurrentDb()
db.Execute ("Update customer set fSelect=true;")
'For Test Purposes
MsgBox db.RecordsAffected & " Records Updated"
Set db = Nothing
End Sub

 

[ben]

I put the code into the on click event of the button, and
it gives me a compile error and highlights the Private
Sub Command21_click()...here is the code i have:


Private Sub Command21_Click()

Private Sub cmdClearAll_Click()
Dim db As DAO.Database
Set db = CurrentDb()
db.Execute ("Update Form_Shaft Description Data Form -
BEN Query - Selection set Yes/No=false;")
'For Test Purposes
MsgBox db.RecordsAffected & " Records Updated"
Set db = Nothing
End Sub

Private Sub cmdSetAll_Click()
Dim db As DAO.Database
Set db = CurrentDb()
db.Execute ("Update Form_Shaft Description Data Form -
BEN Query - Selection set Yes/No=true;")
'For Test Purposes
MsgBox db.RecordsAffected & " Records Updated"
Set db = Nothing
End Sub

End Sub


Thanks for your help
Ben

-----Original Message-----
The best way would be using an update query or inline SQL. The following
shows how to do this using inline SQL. If you would rather use a saved query
you can simply replace the SQL string with the name of the saved query. The
message box is included for confirmation purposes - you might want to remove
it.

This code updates a field named 'fSelect' in a table named 'customer':

Private Sub cmdClearAll_Click()
Dim db As DAO.Database
Set db = CurrentDb()
db.Execute ("Update customer set fSelect=false;")
'For Test Purposes
MsgBox db.RecordsAffected & " Records Updated"
Set db = Nothing
End Sub

Private Sub cmdSetAll_Click()
Dim db As DAO.Database
Set db = CurrentDb()
db.Execute ("Update customer set fSelect=true;")
'For Test Purposes
MsgBox db.RecordsAffected & " Records Updated"
Set db = Nothing
End Sub

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

I have a form that allows me to select records by
checking the corresponding box. I had it setup so every
time the form was opened, the boxes would clear (set to
false). I have found this not to be the ideal situation,
and i want to create a button that will clear the check
boxes.
I assume this is a fairly common code, but i can't find
it anywhere.

Any help will be appreciated
Ben

.

 

[Sandra Daigle]

Remove the second procedure declaration line:

Private Sub cmdClearAll_Click()

The second procedure (and the final 'End Sub') can/should also probably be
eliminated if you don't have or want a second button to set all the booleans
to true.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

I put the code into the on click event of the button, and
it gives me a compile error and highlights the Private
Sub Command21_click()...here is the code i have:


Private Sub Command21_Click()

Private Sub cmdClearAll_Click()
Dim db As DAO.Database
Set db = CurrentDb()
db.Execute ("Update Form_Shaft Description Data Form -
BEN Query - Selection set Yes/No=false;")
'For Test Purposes
MsgBox db.RecordsAffected & " Records Updated"
Set db = Nothing
End Sub

Private Sub cmdSetAll_Click()
Dim db As DAO.Database
Set db = CurrentDb()
db.Execute ("Update Form_Shaft Description Data Form -
BEN Query - Selection set Yes/No=true;")
'For Test Purposes
MsgBox db.RecordsAffected & " Records Updated"
Set db = Nothing
End Sub

End Sub


Thanks for your help
Ben

-----Original Message-----
The best way would be using an update query or inline SQL. The
following shows how to do this using inline SQL. If you would rather
use a saved query you can simply replace the SQL string with the
name of the saved query. The message box is included for
confirmation purposes - you might want to remove it.

This code updates a field named 'fSelect' in a table named
'customer':

Private Sub cmdClearAll_Click()
Dim db As DAO.Database
Set db = CurrentDb()
db.Execute ("Update customer set fSelect=false;")
'For Test Purposes
MsgBox db.RecordsAffected & " Records Updated"
Set db = Nothing
End Sub

Private Sub cmdSetAll_Click()
Dim db As DAO.Database
Set db = CurrentDb()
db.Execute ("Update customer set fSelect=true;")
'For Test Purposes
MsgBox db.RecordsAffected & " Records Updated"
Set db = Nothing
End Sub

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

I have a form that allows me to select records by
checking the corresponding box. I had it setup so every
time the form was opened, the boxes would clear (set to
false). I have found this not to be the ideal situation,
and i want to create a button that will clear the check
boxes.
I assume this is a fairly common code, but i can't find
it anywhere.

Any help will be appreciated
Ben

.