Condition main form control based on multiple records in subform

[jle0003]

I have an 2007 Access database that contains a mainform (frmEvent) and a subform (sfrmComment). Each Event has multiple comments and each comment has an individual status (Open, In work, Closed). There is an overall Event "Closed" checkbox that if True the Event is closed. I want this checkbox to auto-check when all of the comment statuses are set to "Closed". I have attached a sample db. Can someone help me out with the code please?

Thanks for your help!!


Jamie

[June7]

The EventStatus field is a calculated value dependent on other data in another table. Saving dependent calculated data is usually a bad idea. There is no real need to save this to table, it can be calculated when needed.

Status: IIf(Not IsNull(DLookup("CommentStatus", "tblComment", "CommentStatus='Open'")), "Open", "Closed")


However, saving the value requires VBA code. The real trick is figuring out what event to put the code in.