Updating all records in a subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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).
 
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.

Allen Browne said:
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.

Mark said:
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?
Click to expand...
Click to expand...
 
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.

Mark said:
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.

Allen Browne said:
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).


Mark said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top