Link to home
Start Free TrialLog in
Avatar of softsupport
softsupportFlag for United States of America

asked on

header checkbox to check or uncheck multiple boxes in access

I would like to know how i can make a multiple selection check box to select or deselect the header check box all the below check boxes are checked or unchecked.
 
In access 2007 I have one subform connected with fields have check box Yes/No for taking attendance.  When the form opens, all boxes are checked.  If a student is absent i will uncheck, it is big job if i have hundreds of students to track their attendance.  i want to create a check box on the header of the form for each row and column to select or deselect all the check boxes in the column or row and uncheck or check.
 
I would greatly appreciate if someone please help me on this
Avatar of mbizup
mbizup
Flag of Kazakhstan image

In the after update event of the checkbox on the main form:


Dim rs as DAO.recordset
Set rs = Me.MysubformName.form.recordsetclone
if rs.recordcount = 0 then exit sub

with rs
do until .eof
     !MyYesNoField = Me.MyMainFormCheckboxName
     .movenext
loop

set rs = nothing

Open in new window

SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of softsupport

ASKER

Not sure if I was clear on question.  I have several students, and 31 days in the month.  If all students are absent on the 25th (Christmas), I want to select the column or header checkbox and unselect all students for the 25th.  If one student is absent the entire month (31 days), I want to select the student name on left of grid and unselect all 31 days.  Will either of the above accomplish this task?
I believe the second method will work for what you are describing, but obviously I don't have your database to verify.

Give it a try, and post back if you run into any trouble with it.

Posting a sample database would also help clarify if you need to.
Your table is not normalized. or the whole thing could be done with a simple query.  As it is, you will need to implement checking a "row" by using code and checking a "column" will need both a little code plus a query.

To do the row, add an unbound checkbox to the subform.  In the click event of the control, set the value for each field appropriately.

Me.fld1 = me.chk
Me.fld2 = Me.chk
Me.fld3 = Me.chk
....

Open in new window

For the columns, add an unbound checkbox in the subform header for each column.

In the click event of each column checkbox, run an update query.
Dim strSQL as String
strSQL = "Update yourtable Set somefield = " & Me.ColChk1 
strSQL = strSQL & " WHERE  somecriteria that selects the correct records"
DoCmd.RunSQL strSQL

Open in new window

Change the variables for  each column.
In regards to the solution listed above:
What should the "!MyYesNoField" and "Me.MyMainFormCheckboxName" be?  

Dim rs as DAO.recordset
Set rs = Me.MysubformName.form.recordsetclone
if rs.recordcount = 0 then exit sub

with rs
do until .eof
     !MyYesNoField = Me.MyMainFormCheckboxName
     .movenext
loop

set rs = nothing
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am receiving an error message.... See attached.
VBA-Error.jpg
You posted the code but not the error message.

The code looks like it will only set a field named [1], if it works at all.
Apologies.... Here is the error
VBA-Error002.jpg
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial