check/uncheck all checkboxes in a form

Hello,
I am struggling with this situation here for a while.  I am a new user to access so pardon my ignorance.  I have a continuous subform that has a number of checkboxes on there (hundreds).  I would like include a single check box in the header of that form where I can select the checkbox to check or uncheck all the checkboxes in the form.  

This is the code I have in my After update Event Procedure (I receive code from another source).  Needless to say, my codes are not working.  My table name is Report_Dimensions and the field name for the checkboxes is Report.  I have no idea what the DBEngine line does, like I said I borrowed these codes from another online source.  Please advice.  Thanks a lot.

Private Sub chkDoEmAll_AfterUpdate()
 Dim strSQL As String

 'Save any edits to avoid concurrency issues.
  If Me.Dirty Then
  Me.Dirty = False
  End If

 strSQL = "UPDATE Report_Dimensions SET Report = " & _
 Me.chkDoEmAll.Value & " WHERE Report = " & _
 Me.chkDoEmAll.Value & ";"

 DBEngine(0)(0).Execute strSQL, dbFailOnError

 'Requery the form.
 Me.Requery

End Sub
cscott27Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

heer2351Commented:
You are almost there, the dbengine stuff is old (Access 2).

Private Sub chkDoEmAll_AfterUpdate()
 Dim strSQL As String

  'Save any edits
  Me.Dirty = False

 strSQL = "UPDATE Report_Dimensions SET Report = " & _
 Me.chkDoEmAll.Value & " WHERE Report <> " & _
 Me.chkDoEmAll.Value & " or Report is null;"

 CurrentDb.Execute strSQL, dbFailOnError

 'Requery the form.
 Me.Requery

End Sub
heer2351Commented:
It is possible that you have to requery the subform explicitly, in that case change:

Me.Requery

to:

Me![yourSubForm_CONTROL_name].requery
will_scarlet7Commented:
   One thing that I see in your code that might be throwing you off is that you have a condition in your SQL statement that will only set the value of the yes/no field for those records that are already set (WHERE Report = " &  Me.chkDoEmAll.Value & ";"). This was probably an accident, but it might be the thing that is causing it not to work as you had hoped. Try the code below and see if it works better for you. You might also notice that I changed the "Me.Requery" to "Me.Form.Refresh", this will avoid the form jumping to the top of the list, and in this case should produce the same result.

Private Sub chkDoEmAll_AfterUpdate()
    Dim strSQL As String

    'Save any edits to avoid concurrency issues.
    If Me.Dirty Then
        Me.Dirty = False
    End If

    strSQL = "UPDATE Report_Dimensions SET Report = " & _
        Me.chkDoEmAll.Value & ";"

    DBEngine(0)(0).Execute strSQL, dbFailOnError

    'Requery the form.
    Me.Form.Refresh

End Sub
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

will_scarlet7Commented:
Just to combine the criteria fix with the elements noted by heer2351 (If this works, he should get at least half the points, if not more):

Private Sub chkDoEmAll_AfterUpdate()
    Dim strSQL As String

    'Save any edits to avoid concurrency issues.
    If Me.Dirty Then
        Me.Dirty = False
    End If

    strSQL = "UPDATE Report_Dimensions SET Report = " & _
        Me.chkDoEmAll.Value & ";"

    CurrentDB.Execute strSQL, dbFailOnError

    'Requery the form.
    Me!SubForm.Form.Refresh

End Sub
cscott27Author Commented:
Hello All,
I have tried all three suggestions but none of them seem to be working for me.  The single checkbox I have in my header of the subform clears one box in the continuous form but not all.  Any other suggestions are welcome.  Thanks a bunch!
heer2351Commented:
The problem is probably in the requery. What happens if you click the box and close and reopen the form? Are the values correct then?
cscott27Author Commented:
if I uncheck the checkbox in the header and close the form and reopens it, then there is only one box unchecked in the continuous form.  
will_scarlet7Commented:
Can you post the current version of the code you have running in your "After Update" event for the check box in the Header?
cscott27Author Commented:
Below are the codes I am using.  Where you have Me.subform.form, I substituted my subform and form name respectively.  I also in put the database name where you had CurrentDB (even if I comment out this line nothing changes in the way the checkbox functions).  

Dim strSQL As String

    'Save any edits to avoid concurrency issues.
    If Me.Dirty Then
        Me.Dirty = False
    End If

    strSQL = "UPDATE Report_Dimensions SET Report = " & _
        Me.chkDoEmAll.Value & ";"

    PROFILE0705.Execute strSQL, dbFailOnError

    'Requery the form.
    Me!subfrmDimensionSelections.ProgressReport.Refresh


End Sub
will_scarlet7Commented:
I think you might have slipped on a few of the customizations. Try this (without changing anything):

Dim strSQL As String

    'Save any edits to avoid concurrency issues.
    If Me.Dirty Then
        Me.Dirty = False
    End If

    strSQL = "UPDATE Report_Dimensions SET Report = " & _
        Me.chkDoEmAll.Value & ";"

    CurrentDb.Execute strSQL, dbFailOnError

    'Requery the form.
    Me!subfrmDimensionSelections.Form.Refresh

End Sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cscott27Author Commented:
ok, I did exactly as instructed.  I copied the codes you last posted and pasted it in the After event procedure.  I didn't change anything as suggested, but it's still doing the same thing (only unchecking and checking one box in the continuous form).  Even after closing and reopening the form the values are incorrect.  I have no idea what's going on.
will_scarlet7Commented:
Strange... Try this. Insert the following line immediately after the "strSQL = " line:

    Debug.Print strSQL

Now, after you update the "chkDoEmAll", go to the VBE (Visual Basic Editor) and look in the immediate window. There should be text similar to this:

    UPDATE Report_Dimensions SET Report = -1;

Copy the text from the immediate window, and post here.
cscott27Author Commented:
in the process of completing the late suggestion, my checkbox started working.  I didn't change any of the codes, I just added the debug.print str sql as suggested.  I am not questioning it, all I know is that I am excited.  Thank you guys so very very very much.  
will_scarlet7Commented:
Glad to hear it's working.
God bless!

Sam
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.