Loop through checkboxes using ADO – Access 2003

[mrowe]

I am working on a form in an Access 2003 database (using ADO). The detail section of my form has as checkbox named ‘Select’ that occurs once for each row of data. I have placed another checkbox in the header of the form named ‘SelectAll’. When ‘SelectAll’ is modified, I want to modify every occurrence of the ‘Select’ checkbox in the detail section. I looked through several posts and have played around with the code, but I have received an error on everything that I have attempted.

I have figured out how to loop through the recordset, but I cannot seem to isolate the checkbox in each row of the form and set its value to either True or False. I have tried it four different ways (one at a time). I have included all four examples below. I would appreciate any help that I can get.

    Dim tmpcontrol As Controls
    Dim bSelectAll As Boolean
    bSelectAll = Me.SelectAll.Value ‘Checkbox in the Form Header
 
    For indx = 0 To (Me.Form.Recordset.RecordCount - 1)
 
 
        ‘Attempt 1
        Me.Select.OptionValue = bSelectAll
 
        ‘Attempt 2
        Me.Select(indx).Value = bSelectAll
 
        ‘Attempt 3
        For Each tmpcontrol In [CourseInfoForm].Form.Controls
            If tmpcontrol.ControlType = acCheckBox Then
                tmpcontrol.Value = bSelectAll
            End If
        Next tmpcontrol
 
        ‘Attempt 4
        For Each tmpcontrol In [CourseInfoForm].Form.Controls
            If tmpcontrol.Name = "Select" Then
                tmpcontrol.Value = bSelectAll
            End If
        Next tmpcontrol
 
    Next
 

Thank you in advance for your help.
Mark

[Denburt]

Try this

 Dim tmpcontrol As Controls
    Dim bSelectAll As Boolean
  Dim rs as recordset
set rs = me.recordsetclone
 
    bSelectAll = Me.SelectAll.Value ‘Checkbox in the Form Header
 
if not rs.eof then
do until rs.eof
rs!tmpcontrol = bSelectAll
rs.movenext
loop
end if
 

[mrowe]

DenBurt,

Thank you for you help. I had to play with it a little bit to get it to work, but it works and I am happy.

Because I am using ADO, I received a datatype error on the declaration of the recordset. So, I included ‘Microsoft DAO 3.6 Object Library’ in my references and changed the declaration to:
Dim rs As DAO.Recordset

Then I received Run-time Error 320 – ‘Update or CancelUpdate without AddNew or Edit. So, I had to add a rs.Edit and rs.Update. I also had to add a rs.MoveFirst so that I could click the checkbox more than once and get the desired results.

The only bad thing is that because I have 215 items in my list the form flickers for about 3 seconds while all the rows are updated. But it does what it is suppose to do.

Here the final code:

Private Sub SelectAll_AfterUpdate()
 
    Dim tmpcontrol As Controls
    Dim bSelectAll As Boolean
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    rs.MoveFirst
 
    bSelectAll = Me.SelectAll.Value 'Checkbox in the Form Header
 
    If Not rs.EOF Then
        Do Until rs.EOF
            rs.Edit
            rs!Select = bSelectAll
            rs.Update
            rs.MoveNext
        Loop
    End If
 
End Sub
 

[Denburt]

Yes my example was lacking a few things, glad you were able to get it up and going.

[bre1603]

I get a runtime error when using this code. "Item not found in this collection."

Th code catches on rs!tmpcontrol = bSelectAll or on rs!Select = bSelectAll (I tried it both ways to get around the error).

I think I'm probably missing a reference. Although I am using Access 2007, so maybe it's a program change...

[Denburt]

@bre1603
In the form you are using there is a Record Source property. This Record Source is then cloned and you actually access those fields and change the records via the cloned recordet. So your Record Source for that form would need to have a field named tmpcontrol or a field named Select in order to use this code. Don't forget to make sure you set the references and the other issues pointed out by mrowe.

One in specific:

Because I am using ADO, I received a datatype error on the declaration of the recordset. So, I included ‘Microsoft DAO 3.6 Object Library’ in my references and changed the declaration to:
Dim rs As DAO.Recordset

[bre1603]

@Denburt
Ah ha. My field is [Send Email] - when I used that in place of tmpcontrol/Select, the code executed perfectly.

Thanks!