Dcount Recalculate Automatically

[jax]

I have am using Dcount on a subform, which counts the "1"'s in an underlying table. The "1"'s are created by checkboxes in an option group. The code counts the "1"'s ok, but I am unsure where to put a refresh/requery/recalc command so that the user doesnt have to close form and reopen to get correct result. I have looked at adding code to the option group checkbox but it doesnt have afterupdate or onchange or anything obvious. How do I get Dcount to recalculate automatically everytime?

 

[Geoff Codd]

If you click on the frame of your option group you can set an after update event for an option group

 

[jax]

Thanks Geoff, can I put recalcualate two fields at once, below is the code i used:

Private Sub OptionGroup_AfterUpdate()
Me.TotalOccupied.Requery
Me.TotalUnoccupied.Requery
End Sub

I have put the above in the frame afterupdate as you suggested but it doesnt give the correct answers.


Another problem I have just discovered relating to the same Option Group is that the two totals on my subform add up all the 1's and 2's from the option group stored in the table. Do you know how I can count just the 1's and 2's that relate to the particular establishment that the subform sits on? The code I am currently using is:

=DCount("[EstabID]","tblRoomInfo"," [Purpose]='1'")

I will be grateful for any help?