473,320 Members | 1,947 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

Loop through checkboxes using ADO – Access 2003

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
6 6042
Denburt
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
  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
mrowe
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
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
bre1603
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
1,356 Expert 1GB
@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
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

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

Similar topics

4
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...
0
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...
1
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...
3
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...
8
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...
1
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...
9
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...
13
beacon
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...
13
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...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

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.