Main Form checkbox toggles many subform checkboxes

[gymcshoe via AccessMonster.com]

I have a main form / subform setup. The subform has a field that is
a yes/no data type, represented by a checkbox in the field.

How do I set up a control on the main form (possibly another checkbox) so
that
when I click the control, it will toggle ALL of the checkboxes in the
subform on and off. Similar to a "select/deselect all"?

 

[Allen Browne]

Use the AfterUpdate event procedure of the unbound check box on the main
form, to execute an Update query on the subform's table.

This kind of thing:

Private Sub chkSetAll_AfterUpdate()
Dim db As DAO.Database
Dim bValue As Boolean
Dim strSql As String

Set db = dbEngine(0)(0)
bValue = Nz(Me.chkSetAll, False)
strSql = "UPDATE [Table2] SET [MyYesNo] = " & _
IIf(bValue, "TRUE, "FALSE") & " WHERE [MyYesNo] " & _
IIf(bValue, "= FALSE", "<> FALSE") & ";"
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) changed."
Set db = Nothing
End Sub

 

[gymcshoe via AccessMonster.com]

Do I change "Table2" and "MyYesNo" to the names of my table and checkbox
field?

Use the AfterUpdate event procedure of the unbound check box on the main
form, to execute an Update query on the subform's table.

This kind of thing:

Private Sub chkSetAll_AfterUpdate()
Dim db As DAO.Database
Dim bValue As Boolean
Dim strSql As String

Set db = dbEngine(0)(0)
bValue = Nz(Me.chkSetAll, False)
strSql = "UPDATE [Table2] SET [MyYesNo] = " & _
IIf(bValue, "TRUE, "FALSE") & " WHERE [MyYesNo] " & _
IIf(bValue, "= FALSE", "<> FALSE") & ";"
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) changed."
Set db = Nothing
End Sub

I have a main form / subform setup. The subform has a field that is
a yes/no data type, represented by a checkbox in the field.

[quoted text clipped - 3 lines]

when I click the control, it will toggle ALL of the checkboxes in the
subform on and off. Similar to a "select/deselect all"?

 

[gymcshoe via AccessMonster.com]

It works! Thanks Allen!

when I check the checkbox on the main form, a popup tells me a certain number
of records were changed.. and when I uncheck the checkbox, a window also pops
up and tells me again how many records were changed. Is there a way to get
rid of this pop up? Thanks

 

[gymcshoe via AccessMonster.com]

ok, i figured out how to get rid of that msg box...

now i have another problem..that's similar

it's the same form/subform... I want to create another checkbox on the main
form.. that when checked, it will populate text in each of the rows in the
subform.. so similar to select/deselect all, but text will be populated
instead. is this possible?

Thanks again!

 

[Allen Browne]

Again, use an Update query to write the value to the appropriate fields in
the table.

To get the updates to show, you may need to add the line:
Me.[Sub1].Form.Requery
substituting your subform name for Sub1.

 

[gymcshoe via AccessMonster.com]

Sorry, I'm fairly new to this. How do I do this?

Again, use an Update query to write the value to the appropriate fields in
the table.

To get the updates to show, you may need to add the line:
Me.[Sub1].Form.Requery
substituting your subform name for Sub1.

ok, i figured out how to get rid of that msg box...

[quoted text clipped - 16 lines]

 

[Allen Browne]

Okay, you need to create a SQL statement that updates your table the way you
want (assigning the text to the field.)

It will be something like:
strSql = "UPDATE [Table2] SET [MyTextField] = """ & Me.Text1 & """;"
where Text1 is the name of the unbound text box that contains the text you
want in your table.

You may need to add a WHERE clause, so only related records are updated.

To help you get your SQL statement, mock up a query using this table.
Switch it to Update query (Update on Query menu.)
Enter a sample value in the Update row under the right field.
Switch to SQL View to see an example of what you need to create.

For an explanation of the quotes above, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

To actually execute the query, see the example shown earlier.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sorry, I'm fairly new to this. How do I do this?

Again, use an Update query to write the value to the appropriate fields in
the table.

To get the updates to show, you may need to add the line:
Me.[Sub1].Form.Requery
substituting your subform name for Sub1.

ok, i figured out how to get rid of that msg box...

[quoted text clipped - 16 lines]

up and tells me again how many records were changed. Is there a way to
get
rid of this pop up? Thanks

 

[gymcshoe via AccessMonster.com]

Thanks Allen.

Ok so I did want you suggested and im trying to execute using this:

Private Sub Check5_AfterUpdate()
Dim db As DAO.Database
Dim bValue As Boolean
Dim strSql As String

Set db = DBEngine(0)(0)
bValue = Nz(Me.Check5, False)
strSql = "UPDATE [Reviewer Table 1] SET [Action] = """ & "MyText" & """;"
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) changed."
Set db = Nothing
End Sub

So I decided to use a checkbox on the main form, that when checked, "MyText"
will populate in each row of a column in a subform. However, How do I get it
so that when I uncheck the box, "MyText" will be deleted or removed? Thanks
again for your help, you're a lifesaver!

Okay, you need to create a SQL statement that updates your table the way you
want (assigning the text to the field.)

It will be something like:
strSql = "UPDATE [Table2] SET [MyTextField] = """ & Me.Text1 & """;"
where Text1 is the name of the unbound text box that contains the text you
want in your table.

You may need to add a WHERE clause, so only related records are updated.

To help you get your SQL statement, mock up a query using this table.
Switch it to Update query (Update on Query menu.)
Enter a sample value in the Update row under the right field.
Switch to SQL View to see an example of what you need to create.

For an explanation of the quotes above, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

To actually execute the query, see the example shown earlier.

Sorry, I'm fairly new to this. How do I do this?

[quoted text clipped - 11 lines]

 

[Allen Browne]

To clear the Action field:
strSql = "UPDATE [Reviewer Table 1] SET [Action] = Null;"