I want to add another column either onto the table or as part of a query that will enable the check box if there are any enabled check boxes in the other columns, ie, in the above example it would be enabled for rows 1 and 2 but not for 3. Ideally it would be fully automatic, as the number of subject colums might change and I don't really want to have to modify the code each time.
It seems to me you should have a table of Subjects.
Then, whatever subject is related to this table will serve as a foreign key field in tblSubject (or a linking table, probably).
If you don't want to redesign your as application (ref: wazz) then you could use an update query. It's not very elegant and the most concise code I can think of relies on the fact that a False/No value in a check box is zero. So if the sum of all checkboxes is non zero then at least one of them is marked. Here's a sample SQL string.
UPDATE Table1 SET Table1.anysubject = Yes
Of course this does not cover the situation where the summary check box presently is true but should be reset to false.
Neither does it cover the situation if your checkbox can have the third, null value interpreted as 'unknown'.
Finally it does not cover the situation where you have non standard values in your checkboxes. By convention Access uses zero as false/no and minus one as true/yes. However it is possible to have any non zero integer as your checkbox value and this will also be interpreted as true. Just suppose you have plus one and minus one; the above SQL will 'think' that none of the checkboxes are marked.
Just had another thought. (That's two already this week and I'm only allowed five!)
Rereading your post you may want to update this checkbox in 'real time' and not in a mass batch update as I describe above. Presumably you have a form with each checkbox displayed. You need to link some code to the AfterUpdate event of each and every checkbox, plus you need to include the new column/checkbox on your form, either disabled or not visible.
The code behind each event is very similar to the SQL in my previous post.
If Me.math + Me.french + ...... <> 0 then
Me.anysubject = true
Me.anysubject = false
how does the code work if you are just browsing and don't update anything? should it also be on the oncurrent event? or is a field effectively 'updated' when a new record is opened?
is the new checkbox excluded from the first line of code?
does making the new column/checkbox disabled/hidden exclude it from the calculation?
i'll stop there. i dig the -1/0 logic.
"how does the code work if you are just browsing and don't update anything?"
If you don't change the value in any check box then the AfterUpdate event is never raised and the code never executes.
"should it also be on the oncurrent event? "
This is somewhat redundant, kind of like belt and braces (or belt and suspenders if you are 'Stateside). To put the code also in the OnCurrent event would provide a self-correcting feature: if there was an error then just visiting the record would correct that error even if you changed none of the check boxes.
"or is a field effectively 'updated' when a new record is opened?"
Much the same answer as above. If the default value for all check boxes (or more accurately, all boolean attributes on the table) is set to 'No' then any brand new record is automatically correct. If you change a value in any check box when adding a record then the AfterUpdate event is raised and the code executes.
"is the new checkbox excluded from the first line of code?"
"does making the new column/checkbox disabled/hidden exclude it from the calculation?"
No. I suggest it is disabled to stop the user fiddling with it - they tend to do these things if left alone even for a short time. Alternatively you can hide it; now not only can the user not fiddle with it he/she can't see it and does not get confused. Users also get confused very easily. However it needs to be on the form for the code to be able to 'poke' the value into it (second and fourth lines of the code).