Updating all records in a subform

[Guest]

I have a check box on the main form. Each record on the continuous subform
also has a check box. When I click on the main form check box, I want all the
records on the subform to update their check boxes. Anyone know if this is
possible?

 

[Allen Browne]

Probably easiest to do this with an Update query statement.

This kind of thing:

Private Sub chk1_AfterUpdate()
Dim db As DAO.Database
Dim strSql As String
If Me.NewRecord Then
Beep
Else
strSql = "UPDATE [MySubformTable] SET [MyYesNoField] = " &
Me.chk1.Value & ";"
Set db = dbEngine(0)(0)
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " related record(s) changed."
Me.[NameOfYourSubformControlHere].Form.Requery
End If
End Sub

Presumably you are talking about an unbound check box on the main form: you
should not be trying to store the same thing in the main form's table and in
the subform's table (unless there are valid reasons why they should
sometimes be different from each other).

 

[Guest]

Thanks for the help. Just one minor problem.

The intention was that the subform records that were to be updated were the
three displayed in the subform. However this event updated all 2183 records
in the table associated with the subform! Not quite what I had in mind

Sorry to be a pain but can you advise how I amend this to restrict the
updated records. I could probably find out if I ploughed my way through the
help for long enough!

By the way I actually do want to save the check box in both master and
subform. It does makes sense in this database and although I know I could
always calculate the master checkbox, it seems a lot of excessive processing
and coding in multiple queries when saving it on the master would eliminate
all this.

Probably easiest to do this with an Update query statement.

This kind of thing:

Private Sub chk1_AfterUpdate()
Dim db As DAO.Database
Dim strSql As String
If Me.NewRecord Then
Beep
Else
strSql = "UPDATE [MySubformTable] SET [MyYesNoField] = " &
Me.chk1.Value & ";"
Set db = dbEngine(0)(0)
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " related record(s) changed."
Me.[NameOfYourSubformControlHere].Form.Requery
End If
End Sub

Presumably you are talking about an unbound check box on the main form: you
should not be trying to store the same thing in the main form's table and in
the subform's table (unless there are valid reasons why they should
sometimes be different from each other).

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

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

I have a check box on the main form. Each record on the continuous subform
also has a check box. When I click on the main form check box, I want all
the
records on the subform to update their check boxes. Anyone know if this is
possible?

 

[Allen Browne]

Your subform shows only records where a subform field matches the main form
field? Include that match in the WHERE clause of the SQL statement:

strSql = "UPDATE [MySubformTable] SET [MyYesNoField] = " & Me.chk1.Value & "
WHERE [MyRelatedFieldID] = " & Me.[MyMainID] & ";"

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

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

Thanks for the help. Just one minor problem.

The intention was that the subform records that were to be updated were
the
three displayed in the subform. However this event updated all 2183
records
in the table associated with the subform! Not quite what I had in mind

Sorry to be a pain but can you advise how I amend this to restrict the
updated records. I could probably find out if I ploughed my way through
the
help for long enough!

By the way I actually do want to save the check box in both master and
subform. It does makes sense in this database and although I know I could
always calculate the master checkbox, it seems a lot of excessive
processing
and coding in multiple queries when saving it on the master would
eliminate
all this.

Probably easiest to do this with an Update query statement.

This kind of thing:

Private Sub chk1_AfterUpdate()
Dim db As DAO.Database
Dim strSql As String
If Me.NewRecord Then
Beep
Else
strSql = "UPDATE [MySubformTable] SET [MyYesNoField] = " &
Me.chk1.Value & ";"
Set db = dbEngine(0)(0)
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " related record(s) changed."
Me.[NameOfYourSubformControlHere].Form.Requery
End If
End Sub

Presumably you are talking about an unbound check box on the main form:
you
should not be trying to store the same thing in the main form's table and
in
the subform's table (unless there are valid reasons why they should
sometimes be different from each other).

I have a check box on the main form. Each record on the continuous
subform
also has a check box. When I click on the main form check box, I want
all
the
records on the subform to update their check boxes. Anyone know if this
is
possible?