Select All/ Clear All checkbox

[TheHarleygirl2005]

I have a form that lists approximately 100 companies and a checkbox for each company. It is set up so a supervisor can check several companies then hit a command button that appends those records to a table (using an append query). Sometimes a supervisor only needs to select a hand full of companies but sometimes they need to check all 100 records. Is it possible to add a command button to the form that when selected it checks all boxes on the form. I would also need a second command button that unchecks all boxes. I'm guessing this would be done by building a code under the "on click" in properties but I'm not sure what the code would be for that.

OR

Would I create an unbound checkbox that I would check to select all and uncheck to clear all?

[alansidman]

I imagine you could use a process similar to this one to check and uncheck all the boxes.

http://www.datapigtechnologies.com/f...tboxtrick.html

Alan

[June7]

Give the 100 checkboxes similar names, like chkSupv1, chkSupv2, etc. Also, labels with similar names that contain the value you want saved, these labels be can hidden.

For i = 1 to 100
If Me.Controls("chkSupv" & i) = True Then
CurrentDb.Execute "INSERT INTO tablename(fieldname) VALUES(" & Me.Controls("lblSupv" & i) & ")"
End If
Next

Similar code to set the checkbox and yes another checkbox (or a toggle button) for the select all/clear all could work.

For i = 1 to 100
Me.Controls("chkSupv" & i) = Me.chkSelect
Next

[TheHarleygirl2005]

I don't have 100 different check boxes. I have one field named "SchoolsOut" in the company table which is a Yes/No field.


I have set up the form so the supervisor adds a date (in the footer) and checks the appropriate box(s) then runs the append query by hitting the "Go" button to dump the data into a table which shows the selections for each date.

I'm just looking for away to check all the boxes or uncheck all the boxes in this form. It doesn't have to update or append because the go button takes care of that.

[June7]

Yes, that gives me a very different understanding than 'a checkbox for each company'. There is only one checkbox bound to Yes/No field.

Probably the easiest way to select/unselect all is with an UPDATE sql action and refresh the form.

Another way is for code to physically move to each record and set the checkbox value. This gets tricky for detecting the begin/end of the records. I have never done code for this.

A method I do use frequently is looping through the form's RecordsetClone.
While Not Me.RecordsetClone.EOF
Me.RecordsetClone!SchoolOut = Me.chkSelect
Me.RecordsetClone.MoveNext
Wend
Me.Refresh

[TheHarleygirl2005]

Okay, I added a Command Button and put the following code in the "on Click" procedure:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Me.TempAttendance.Value = "True"
Me.Requery
Exit_Command25_Click:
Exit Sub
Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub



This code only adds a check to the first record, not the following 99. What did I do wrong?

[June7]

Because the code does not loop and move to next record. Review my previous post.

[TheHarleygirl2005]

I tried to use your previous post but it did not work which is why went with this. When I use the following code I get an error.



While Not Me.RecordsetClone.EOF
Me.RecordsetClone! TempAttendance= Me.chkSelect
Me.RecordsetClone.MoveNext
Wend
Me.Refresh

The control source for Schools Out? is called "TempAttendance"

[June7]

You need an unbound checkbox or toggle button in the form header that will be used as criteria for the value to set all the records. That would be called chkSelect. You can put the code in the AfterUpdate event of that control.

[TheHarleygirl2005]

Okay, I deleted the Command Button and added a checkbox to the header with the following code from above:

Private Sub chkSelect_AfterUpdate()
While Not Me.RecordsetClone.EOF
Me.RecordsetClone!TempAttendance = Me.chkSelect
Me.RecordsetClone.MoveNext
Wend
Me.Refresh

End Sub


I now get a Run-Time error 3020 "Update or Cancel update without AddNew or Edit

[June7]

Oh nuts! Forgot the Edit. I use RecordsetClone a lot to read data from a form but rarely to edit. I normally would use sql UPDATE action to mass edit records.

With Me.RecordsetClone
.MoveFirst
While Not .EOF
.Edit
!TempAttendance = Me.chkSelect
.Update
.MoveNext
Wend
End With

[TheHarleygirl2005]

Thank you! Thank you! Thank you! This is perfect!