Applying update on click to all related records

[Evan McCutchen]

Hello,

I'd like to, when a check box is clicked on my main form, have all related
records in a subform have the checkbox clicked, as well. My situation is
this: we have a number of companies that are marked as inactive. When those
companies are marked inactive, i would like the representatives (listed in
the subform) to be marked as inactive when the company is marked inactive.
Does anyone know how I should go about doing this?

Thanks!
Evan M
(e-mail address removed)

 

[Alphonse Giambrone]

In the AfterUpdate event of your check box

Dim binCheck as Boolean

binCheck = me!chkInactive
With me!yoursubform.Form.RecordsetClone
If .Recordcount > 0 Then
.MoveFirst
Do While Not .EOF
!Inactive = binCheck
.MoveNext
Loop
End If
End With

If you have a very large number of subform records this may be a bit slow.
In which case a SQL update would be quicker.

 

[Evan McCutchen]

Alphonse,

After putting the code in there, i received the error "Update or
CancelUpdate without AddNew or Edit". Do you have any suggestions?

Evan M.
evan @ radiologyonesource DOT com

 

[Alphonse Giambrone]

Sorry about that,

Dim binCheck as Boolean

binCheck = me!chkInactive
With me!yoursubform.Form.RecordsetClone
If .Recordcount > 0 Then
.MoveFirst
Do While Not .EOF
.Edit
!Inactive = binCheck
.MoveNext
Loop
End If
End With

 

[Alphonse Giambrone]

Woops, left out the update also,

Dim binCheck as Boolean

binCheck = me!chkInactive
With me!yoursubform.Form.RecordsetClone
If .Recordcount > 0 Then
.MoveFirst
Do While Not .EOF
.Edit
!Inactive = binCheck
.Update
.MoveNext
Loop
End If
End With

 

[Evan McCutchen]

No problem Alphonse! It works wonderfully now. Thanks for your assistance.
If I still had some chips and salsa from my lunch break, I'd send them to
you. Have a good afternoon,

Evan M.
evan AT radiologyonesource DOT com