How to uncheck a check box when a check box from another table is unchecked

[dudda89]

Hi:

I have two tables Students and Classes that are joined together by a inner table Student And Class (many to many relationship). I want the user to be able to check a check box called Inactive in the Class table to deactivate a class once this happens I want the check box called enrolled in the students table to be unchecked automatically if the student was in that class.

I currently have a form set up in datasheet view that just has the class table on it that allows the user to Inactivate the classes. Can I some how using VB have all the students that are enrolled in the class check box unchecked once the class in inactive? I tried using the Students and Classes table, but I don't want the user to have to uncheck maunally all instances for every student in the class.

Thanks

 

[rick982]

You would have to run two queries to do this. The first query would get the list of students that are enrolled in this class, then the second query would be an update query to update the checkbox for enrolled to NO. I would display the results of the first query in a form and then run the second query from a VERIFY/CLOSE button on that form, but there are many ways to accomplish this. Hopefully this will head you in the right direction.

 

[dudda89]

Is there anyway to do this from the Class Inactive form (this form just shows the classes, so it only has class information)? I don't want the user to have to go anywhere else. I just want them to be able to inactivated the class and then automatically (by running code I'm guessing) inactivate all the students in the class.

 

[RuralGuy]

How much wood could a woodchuck chuck if a woodchuck could chuck wood?

 

[dudda89]

Ok smarta**, How do I do it then if you are so smart? I can make the queries like the above poster stated but then how do I get those queries to run when the user checks the check box to inactivate the class.

 

[RuralGuy]

CheckBoxes have AfterUpdate events that run when the value of the CheckBox changes.

 

[dudda89]

Yes, I do know that already and have tried to put code in to make enrolled = 0 from the students table, but I'm not doing it right. Thanks for helping me. I need the code to do this or if I can do it with queries I'm unsure on how to do this in the afterupdate event. Thanks again.

 

[RuralGuy]

What exactly are you doing to show a student in a class? Do you show the classes and add a student to it or show a student and enroll them in a class?

 

[dudda89]

The user enters students in a class form with a subform where there is a combo box and list all the students that they entered from a different form. The user is able to enroll each student in future classes (to hold their spots, preschool). I have the user check the enrolled box (this also means the student has turned in the enrollment paperwork for that class) for the current class the student is in. At the end of the year I have the user Inactivate all current year classes and this is where I want Access to unenroll the students from those inactive classes.

 

[RuralGuy]

What is the structure of the inner table Student And Class (many to many relationship) table? I think all you need to do is simply delete all of the records that have this class in them but I could be wrong.

 

[dudda89]

Could I use the below SQL?

UPDATE Students

SET Enrolled = 0,



WHERE Inactive = 1;

 

[dudda89]

Opps I meant Inactive = -1

 

[dudda89]

Yes you could inactivate all the records from the student and classes form that have that classID, but I don't want the user to have to do this manually. I don't want to delete any records.

Ok so I need help on the WHERE part. It doesn't like just Inactive. Or if I put the form and then the field in there (unless I'm doing the syntax wrong)

UPDATE Students
SET Enrolled = 0,
WHERE [Inactive Class]!Inactive = -1;

 

[RuralGuy]

You only have one student record and one class record right? Where is the checkbox?

 

[dudda89]

I figured it out. I used a Macro to run versions of the two queries the first poster suggested and that worked.

 

[RuralGuy]

Excellent! Glad you got it sorted.