By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,441 Members | 996 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,441 IT Pros & Developers. It's quick & easy.

Loop through checkboxes using ADO – Access 2003

P: 7
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.


Expand|Select|Wrap|Line Numbers
  1.     Dim tmpcontrol As Controls
  2.     Dim bSelectAll As Boolean
  3.     bSelectAll = Me.SelectAll.Value ‘Checkbox in the Form Header
  4.  
  5.     For indx = 0 To (Me.Form.Recordset.RecordCount - 1)
  6.  
  7.  
  8.         ‘Attempt 1
  9.         Me.Select.OptionValue = bSelectAll
  10.  
  11.         ‘Attempt 2
  12.         Me.Select(indx).Value = bSelectAll
  13.  
  14.         ‘Attempt 3
  15.         For Each tmpcontrol In [CourseInfoForm].Form.Controls
  16.             If tmpcontrol.ControlType = acCheckBox Then
  17.                 tmpcontrol.Value = bSelectAll
  18.             End If
  19.         Next tmpcontrol
  20.  
  21.         ‘Attempt 4
  22.         For Each tmpcontrol In [CourseInfoForm].Form.Controls
  23.             If tmpcontrol.Name = "Select" Then
  24.                 tmpcontrol.Value = bSelectAll
  25.             End If
  26.         Next tmpcontrol
  27.  
  28.     Next
  29.  
Thank you in advance for your help.
Mark
Mar 20 '07 #1
Share this Question
Share on Google+
6 Replies


Denburt
Expert 100+
P: 1,356
Try this

Expand|Select|Wrap|Line Numbers
  1.  Dim tmpcontrol As Controls
  2.     Dim bSelectAll As Boolean
  3.   Dim rs as recordset
  4. set rs = me.recordsetclone
  5.  
  6.     bSelectAll = Me.SelectAll.Value ‘Checkbox in the Form Header
  7.  
  8. if not rs.eof then
  9. do until rs.eof
  10. rs!tmpcontrol = bSelectAll
  11. rs.movenext
  12. loop
  13. end if
  14.  
Mar 20 '07 #2

P: 7
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub SelectAll_AfterUpdate()
  2.  
  3.     Dim tmpcontrol As Controls
  4.     Dim bSelectAll As Boolean
  5.     Dim rs As DAO.Recordset
  6.     Set rs = Me.RecordsetClone
  7.     rs.MoveFirst
  8.  
  9.     bSelectAll = Me.SelectAll.Value 'Checkbox in the Form Header
  10.  
  11.     If Not rs.EOF Then
  12.         Do Until rs.EOF
  13.             rs.Edit
  14.             rs!Select = bSelectAll
  15.             rs.Update
  16.             rs.MoveNext
  17.         Loop
  18.     End If
  19.  
  20. End Sub
  21.  
Mar 20 '07 #3

Denburt
Expert 100+
P: 1,356
Yes my example was lacking a few things, glad you were able to get it up and going.
Mar 21 '07 #4

bre1603
P: 39
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...
Jul 14 '10 #5

Denburt
Expert 100+
P: 1,356
@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
Jul 19 '10 #6

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

Thanks!
Jul 19 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.