Checkbox to check/uncheck all checkboxes in a continuous form

[Islwyn]

Not sure if this is appropriate for the code repository but anyway… here is one solution to a problem I couldn’t find a post relating to, and I thought it might be useful to others, especially novices like me.

*Note -it will modify all the relevant data in the RecordSource of the relevant form, so take care!*

Basically I wanted a checkbox in a form header which would check/uncheck all the “SelectThisRecord” checkboxes on a continuous form.

Here is the code I inserted into the “On Click” property of the “Select/Deselect all” checkbox on the form header (called “TickBox” in the below example):



Private Sub TickBox_Click()
Dim FormName As String
Dim FieldName As String
Dim TickBoxName As String

' Enter the name of the yes/no field to be toggled (in this example it is “SelectThisRecord”:
FieldName = "SelectThisRecord"

' Use VBA to find name of the tick box which will toggle all the FieldName values to "yes" or "no"
TickBoxName = Me.ActiveControl.Name

' Use VBA to find the name of the form
FormName = Me.Name

‘Call a Function which checks/unchecks the relevant boxes
Call TickOnOff(FormName, FieldName, TickBoxName)

End Sub


And here is the TickOnOff Function which is kept in a Module

Function TickOnOff(FormName As String, FieldName As String, TickBoxName As String)
Dim RecordSourceName As String
Dim Switch1 As String
Dim MySql As String

' Find the RecordSource of the continuous forms

RecordSourceName = Forms(FormName).RecordSource

' Find the current status of the controlling 'master' checkbox
Switch1 = Forms(FormName).Controls(TickBoxName).Value

' Create SQL which will update all relevant records to the same value as as the 'master' checkbox
MySql = "UPDATE " + RecordSourceName + " SET " + RecordSourceName + "." + FieldName + " = " + Switch1 + ";"

' Run the SQL
DoCmd.RunSQL MySql

' Update the form to show the new values
Forms(FormName).Controls(FieldName).Requery

End Function

 

[marschall]

could you post a sample file?
I could not get it to work

 

[Islwyn]

Sorry that it's not working marschall.

I've attached a small database I've made from scratch using the same code, but for some weird reason the code now only half works: It does update the yes/no values in the source table, as intended, but the Requery part of the module doesn't seem to work. Yet it all works fine in the original database for which the code was written!

In the attached mdb file, Form1, to see the changes after ticking the 'select/deselect all' check box, you have to scroll down on the form and then back up. When the record check boxes reappear in the form window after scrolling they have changed to the value of the select/deselect all box (and the underlying query/table). If you do not let them change in the form window by scrolling them up and down, then the next time you tick the 'change all' box you get an error saying "another user edited this record"

I have had this kind of weird problem with Requery being temperamental/inconsistent before, so I'd be very grateful for any suggestions as to why this happens - is the code wrong [at Forms(FormName).Controls(FieldName).Requery] ? Is there a better way to refresh the form to show the new data?

Cheers

 

[dbengson]

I'm trying to use the code you posted... it's just what I was looking for. But, Im getting a runtime error '3144' Syntax error in UPDATE statement. My code is below. Can you spot the problem?


Function TickOnOff(FormName As String, FieldName As String, TickBoxName As String)
Dim RecordSourceName As String
Dim Switch1 As String
Dim MySql As String

' Find the RecordSource of the continuous forms

RecordSourceName = Forms(FormName).RecordSource

' Find the current status of the controlling 'master' checkbox
Switch1 = Forms(FormName).Controls(TickBoxName).Value

' Create SQL which will update all relevant records to the same value as as the 'master' checkbox
MySql = "UPDATE " + RecordSourceName + " SET " + RecordSourceName + "." + FieldName + " = " + Switch1 + ";"
MsgBox MySql
' Run the SQL
DoCmd.RunSQL MySql

' Update the form to show the new values
Forms(FormName).Controls(FieldName).Requery

End Function

 

[John Big Booty]

Welcome to the forum.

You need to use the ampersand (&) to concatenate a string rather than the plus sign (+) you are currently using.

 

[dbengson]

Thanks for your reply. I changed it to:

MySql = "UPDATE " & RecordSourceName & " SET " & RecordSourceName & "." & FieldName & " = " & Switch1 & ";"

but I'm still getting the same error.

 

[Islwyn]

Sorry for the delay in responding, for the fact that the code is not working, and for using "+" instead of "&".

Peculiarly the original code (with "+" not "&") works great in the database for which it was written but neither the corrected or original code works in the new database I created. Notwithstanding the incorrect use of "+" I seem to come across this kind of inconsistency now and again in Access, so if anyone can spot what the problem is with this and why it should work in one database but not in another I'd be grateful for a solution.

Cheers

Is

 

[raghuprabhu]

May be this is what you want. It works but, when the form is closed, the "TickBox" goes back to value 0. You may need an underlying table for that.

Cheers
Raghu Prabhu