Select all check boxes on a form (only one check box field is on the form)

[khughes46]

Is there a way to select all check boxes on a form; there is only one check box field on the form. If the solution is using VB then I am going to need step by step instructions on how to do it.

[June7]

Yes, requires VBA. More than one way to code.

What is purpose of this action?

What do you mean by 'all check boxes' - every record?

[rpeare]

yes there is

dim ctl as control

for each ctl in me.controls
    if ctl.controltype = 106 'check box
        ctl.value = -1
    else

    endif
next ctl

you could attach this to any event on your form (i.e. ON CLICK of a button, etc)

[khughes46]

Yes. I have preselected a group of records for display. Once these are up I want to give the option to select some (checking each box) or all check one button to perform the select all action.

[June7]

rpeare's example code is to set value of multiple checkboxes in a single record. If you want to set value of a single checkbox in multiple records, that requires code to cycle through the records or run an UPDATE sql to set value of specific records based on the form filter criteria.

[rpeare]

Excellent point, I had thought this was something like an automated report queue where it would print whatever reports were selected (whatever was checked off). This changes significantly if you are doing this on a bound form, somewhat easier on an unbound form.

[khughes46]

Tried this. I have a command buton, the ON CLICK entry is [Event Procedure]. The event has the following code:
Private Sub Command23_Click()
Dim ctl As Control
For Each ctl In Me.Controls
if ctl.controltype = 106 'check box
ctl.Value = -1
Else
End If
Next ctl
End Sub

I clicked the button and got the following:
Compile Error
Syntax Error

Highlighted in yellow is Private Sub Command23_Click()
Selected in blue is if ctl.controltype = 106 'check box

What did I miss?

[rpeare]

What references are you using?

The ones I used to test the code are:

Visual Basic for Applications
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Access database engine object library

I believe it's one of the two object libraries that contain the reference you need.

[June7]

Did you read post 5? Unless I completely misunderstand the requirements, I don't think rpeare's code is what you need.

[khughes46]

And where would I find these object libraries?

[khughes46]

I did just now. I've attached a copy of the form I am talking about. It's the profiles boxes I want to select all at once.

[Missinglinq]

...If you want to set value of a single checkbox in multiple records, that requires code to cycle through the records...

Something like this:

Private Sub cmdTickAllBoxes_Click()

Dim rs As DAO.Recordset

Dim db As Database

Set db = CurrentDb

Set rs = db.OpenRecordset("ActualTableName")

Do While Not rs.EOF
  rs.Edit
  rs!CheckboxField = -1
  rs.Update
  rs.MoveNext
Loop

rs.Close

Set rs = Nothing

Me.Requery

End Sub

Or if you want a single button to alternate between ticking all or unticking all:

• Create a Command Button
• Name it cmdTickUntickAllBoxes
• Set it’s Caption to Tick All

Now use this code:

Private Sub cmdTickUntickAllBoxes_Click()

Dim rs As DAO.Recordset

Dim db As Database

Set db = CurrentDb

Set rs = db.OpenRecordset("ActualTableOrQueryName")

If cmdTickAllBoxes.Caption = "Tick All" Then
  
 cmdTickAllBoxes.Caption = "Untick All"
 
 Do While Not rs.EOF
  rs.Edit
  rs!CheckboxField = -1
  rs.Update
  rs.MoveNext
 Loop

Else

 cmdTickAllBoxes.Caption = "Tick All"
 
 Do While Not rs.EOF
  rs.Edit
  rs!CheckboxField = 0
  rs.Update
  rs.MoveNext
 Loop

End If

rs.Close

Set rs = Nothing

Me.Requery

End Sub

Linq ;0)>

[June7]

linq's code will check all the records in the table but there is simpler code to do that:

CurrentDb.Execute "UPDATE tablename Set Profiles=True"

However, maybe the form is filtered and not displaying all records. If you want to check only the records displayed on form, that means cycling through records of the form. Suggest using the form's RecordsetClone.

With Me.RecordsetClone
!Profiles = True
.MoveNext
End With

Then to uncheck all records:

CurrentDb.Execute "UPDATE tablename Set Profiles=False"

[Missinglinq]

...However, maybe the form is filtered and not displaying all records...

Sorry, meant to add that caveat and got distracted by tremendous thunderstorm that started as I was posting! My handsome but scared-of-everything Brittany Spaniel jumped in my lap, sending my keyboard flying!

Linq ;0)>

[khughes46]

LOL! I don't see a RecordsetClone. Where do I find RecordsetClone?