check box command 2

[techkenny1]

I have a continous form on which i have a checkbox in the hdr and a checkbox in the detail section.

I want to be able to either check or uncheck the checkboxes in the detail section by the checkbox in the header. i have used this code, but could someone modify this so that is works.
Private Sub CHK2_AfterUpdate()

If Chk2 Then
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone

Do
rst.Edit
rst![CHK1] = Me![Chk2]
rst.Update
rst.MoveNext
Loop Until rst.EOF


Else

End If

End Sub

Chk1 is in the detail section and chk2 is in the hdr

Many thanks

 

[dhookom]

You haven't told us what happens or doesn't happen when you check or uncheck the box. Do you get an error?

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya techkenny1 . . .

You have to requery the form to see the changes!

[blue]Private Sub CHK2_AfterUpdate()
   Dim rst As DAO.Recordset
   Set rst = Me.RecordsetClone
   
   Do
      rst.Edit
      rst!CHK1 = Me!Chk2
      rst.Update
      rst.MoveNext
   Loop Until rst.EOF
   
   Me.[purple][b]Requery[/b][/purple]
   
   Set rst = Nothing

End Sub[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PHV]

A simpler way:

Private Sub CHK2_AfterUpdate()
Dim rst As DAO.Recordset
Set rst = Me.Recordset
With rst
   .MoveFirst
   Do
      .Edit
      ![CHK1] = Me![Chk2]
      .Update
      .MoveNext
   Loop Until .EOF
End With
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

techkenny1 . . .

A faster way would be to use an update SQL along with Requery! ... Example:

[blue]   Dim db As DAO.Database, SQL As String
   
   Set db = CurrentDb
   
   SQL = "UPDATE [purple][B][I]TableName[/I][/B][/purple] " & _
         "SET [Chk1] = " & Me!chk2 & ";"
   db.Execute SQL, dbFailOnError
   Me.[b]Requery[/b]
   
   Set db = Nothing[/blue]

[BLUE]Your Thoughts? . . .[/BLUE]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

TheAceMan1
I think you are making a huge assumption the form's recordsource is the entire table. It could be a query or a filtered recordset.

Duane
Hook'D on Access
MS Access MVP

 

[techkenny1]

Many thanks to both of you. In the end I used PIVs code which worked great. But I due to your help you both deserve a star. I have tried all sorts to get this before I put my post. So many thanks

 

[TheAceMan1]

Howdy dhookom . . .

As I stated the SQL/code was [blue]just an example[/blue]. We both know criteria would easily pin it down to the exact recordset.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]