472,354 Members | 1,538 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,354 software developers and data experts.

Loop through checkboxes using ADO – Access 2003

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
  5.     For indx = 0 To (Me.Form.Recordset.RecordCount - 1)
  8.         ‘Attempt 1
  9.         Me.Select.OptionValue = bSelectAll
  11.         ‘Attempt 2
  12.         Me.Select(indx).Value = bSelectAll
  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
  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
  28.     Next
Thank you in advance for your help.
Mar 20 '07 #1
6 5900
1,356 Expert 1GB
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
  6.     bSelectAll = Me.SelectAll.Value ‘Checkbox in the Form Header
  8. if not rs.eof then
  9. do until rs.eof
  10. rs!tmpcontrol = bSelectAll
  11. rs.movenext
  12. loop
  13. end if
Mar 20 '07 #2

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()
  3.     Dim tmpcontrol As Controls
  4.     Dim bSelectAll As Boolean
  5.     Dim rs As DAO.Recordset
  6.     Set rs = Me.RecordsetClone
  7.     rs.MoveFirst
  9.     bSelectAll = Me.SelectAll.Value 'Checkbox in the Form Header
  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
  20. End Sub
Mar 20 '07 #3
1,356 Expert 1GB
Yes my example was lacking a few things, glad you were able to get it up and going.
Mar 21 '07 #4
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
1,356 Expert 1GB
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
Ah ha. My field is [Send Email] - when I used that in place of tmpcontrol/Select, the code executed perfectly.

Jul 19 '10 #7

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

Similar topics

by: Pete | last post by:
Okay, I'm still stuck with this problem. Here's a quick recap/summary :- 1. Page 1:User checks 3 out of 10 checkboxes and submits form to page 2 2. Page 2:Item count shows 3 items. User checks...
by: Sue Adams | last post by:
I actually have two issues/questions: I have an autonumber field in an access db table that I grab and later use to update a record in another table withing the same db. The code I use to get...
by: Geoff Lane | last post by:
Browser: IE5+ I have a form with an unknown number of checkboxes (the page is generated by a server-side script). I need to loop through those checkboxes, test whether the name of each checkbox...
by: Kezza | last post by:
Hi There.. I have dynamically created some textbox and checkbox controls by adding them to a panel. Now I would like to get the values out. I have created a for each loop that I can see the...
by: Tim | last post by:
On my form I have 10 checkboxes named chkbox1,chkbox2,....chkbox10. I would have like to set it up as an array control like in VB6 where I could have chkbox(1),chkbox(2) but I think .net you have...
by: RLN | last post by:
Re: Access 2003 I found a method here in the newsgroup that works for clearing checkboxes, but don't understand a particular line of code and how it works. Here is the code: 1. Dim ctrl As...
by: martin DH | last post by:
I've seen similar posts to what I am looking for but have not been able to successfully use what I have read. I have a form with 4 checkboxes: CkQ1 CkJan CkFeb CkMar I would like to be able to...
by: beacon | last post by:
Hi everybody, I have a quality assurance database that has a form with a bunch of checkboxes on it that serve as reasons for why a product didn't pass inspection. It's a given that the...
by: curious80237 | last post by:
In Access 2003: A table with four yes/no fields is part of a query used as data source for a report. The yes/no fields show in the datasheet and the report as checkboxes; the default value of the...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made but the http to https rule only works for...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it so the python app could use a http request to get...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.