Clear Checkboxes on Continuous forms

[jackle]

How can i clear all checkboxes (to the value of 0) in a tabular, or
continuous, form? These are to check items in a list to print. The tabular
form is laid out like a Datasheet, so each row/record has its own checkbox.
This can be nearly 100 records/rows. My user needs to be able to clear all
checkboxes so he can print in different groups. The check value gets fed to
a table (tblPrintList) that has two fields (CheckBox and WorkOrderNbr). A
query for a report picks up these values for printing. The tabular form only
shows a group of records from the tblPrintList, so we shouldn't reset all
values in the table, just those that are picked for the tabular form.

I think a loop method will work. But any other ways to reset only resets the
first occurance of a checkbox in the form, not all. Can anyone help.
Thanks,
jackle

 

[Baby Face Lee]

Hi,
Apologies if I've misunderstood this but I'm presuming that the checkbox is
bound to a field within a table. If it is, you can run some code/sql to set
all the checkboxes to zero via a command button.
Add a button to the foot of the form (or wherever) and call it cmdClear.
Then add the following to the OnClick event procedure:

Private Sub cmdClear_Click()

Dim db As Database
Dim strSQL as String

Set db = DBEngine(0) (0)
strSQL = "UPDATE [tblYourTable] SET [chkboxFieldName] = 0"
db.Execute strSQL
Me.Form.Requery

db = Nothing

Exit Sub

I hope this helps!

Lee

++++++++++++++++++++++++++++++++++++++++

 

[John Nurick]

Hi Jackle,

1) If the record source of the form is a query, or if you can obtain or
build the corresponding SQL statement, you can use it as the basis of an
update query. (Baby Face's suggestion will update all records in the
underlying table, which doesn't seem to be what you want.)

2) Otherwise you can iterate through the form's RecordsetClone, along
the lines of this air code (which assumes that the checkboxes you want
to clear are bound to the only boolean fields in the record source):

Dim rsR As DAO.Recordset
Dim F As DAO.Field

Set rsR = Me.RecordsetClone
With rsR
.MoveFirst
Do Until .EOF
For Each F in .Fields
If F.Type = dbBoolean Then
F.Value = 0
End If
Next
.MoveNext
Loop
.Close
End With
Me.Requery

 

[jackle]

Your right, babyface's method isn't the avenue to go down. But I get this
error message when I try to run the below code;
Update or CancelUpdate without AddNew or Edit

Private Sub Command54_Click()
On Error GoTo Err_Command54_Click
'-----------------------------------
' To Clear ALL chekboxes to the Unchecked state
'--------------------------------------

Dim rsR As DAO.Recordset
Dim F As DAO.Field

Set rsR = Me.RecordsetClone
With rsR
.MoveFirst
Do Until .EOF
For Each F In .Fields
If F.Type = dbBoolean Then
F.Value = 0
End If
Next
.MoveNext
Loop
.Close
End With
Me.Requery


Exit_Command54_Click:
Exit Sub

Err_Command54_Click:
MsgBox Err.Description
Resume Exit_Command54_Click

End Sub

What am i doing wrong?
Thanks,
Jackle

 

[John Nurick]

As I said, it was air code. You need to explicitly edit and update the
record: Try this:

If ...
F.Edit
F.Value = 0
F.Update
End If

 

[jackle]

I wasn't sure what air code was. I mistakenly belived that ment paste in the
section in to a Private Sub. I will tinker with it this weekend. Is the F in
F.Edit literal or does it repersent anything (line field name)?
Thanks,
a VBA airhead. (sorry about that pun).

 

[John Nurick]

"Air code" means code that hasn't been compiled or tested but shows the
general way of doing something. The line
F.Value = 0
appears in the If .. End If structure in the code I first posted: just
put the F.Edit and F.Update before and after it.