Check One, Check all

Some of you know (Tim) that I'm working on a Professional Development and Training Database. I'm about to demo it to HR but I would like to add a feature: If a course is "Mandatory for all employees?" is checked in tblCourse, then check "Mandatory for Employee?" field in tblEnrollment. I assume it is a VBA code.

I learned VB in school, not VBA so i'm having some difficulty even constructing this. Not even sure if it would be better/easier to just do it as a query - or if possible.

You can also guide me in the right direction - eg. a website that has that exact tutorial and I can apply that.

This can be done with either a query or vba. If you do it with a query, then you are interested in using an update query. However, you will need a way to run the query. Either someone will have the run the query manually or it will have to be run via vba. Access has wizards that will build the vba if you want it run when someone clicks a button.

This code assumes you have a field called Mandatory in both tblCourses and tblEnrollment.

This code will replace the tblEnrollment.mandatory value with the tblCourses.mandatory value for all of the enrollment records. Therefore, if the course is set to mandatory, all the enrollment records with that course will be set to mandatory; if the course is not set to mandatory, then all the enrollment records with that course will be set to not mandatory.

You can just search the web for the terms "update query" to get more information about update queries. Once you know what to look for, it's a lot easier to find :)

You can, of course, do all of this in vba without building and saving a query, but it is a little more advanced. Let me know if you are interested in this approach.

Having said all of that, if you have a relationship between tblCourse and tblEnrollment, then what is the purpose of the Mandatory for Employee field in tblEnrollment? Can't you just create a join from tblEnrollment to tblCourse to find out if the course is mandatory? That would be my recommendation if possible; however there are instances where I can think where this would not be possible (for example, if a course is mandatory for most people but for certain individuals it would not be mandatory).

Thanks for the response Tim! I'm going to learn the update query and then I'll get back to you most likely next week

This can be done with either a query or vba. If you do it with a query, then you are interested in using an update query. However, you will need a way to run the query. Either someone will have the run the query manually or it will have to be run via vba. Access has wizards that will build the vba if you want it run when someone clicks a button.

This code assumes you have a field called Mandatory in both tblCourses and tblEnrollment.

This code will replace the tblEnrollment.mandatory value with the tblCourses.mandatory value for all of the enrollment records. Therefore, if the course is set to mandatory, all the enrollment records with that course will be set to mandatory; if the course is not set to mandatory, then all the enrollment records with that course will be set to not mandatory.

You can just search the web for the terms "update query" to get more information about update queries. Once you know what to look for, it's a lot easier to find :)

You can, of course, do all of this in vba without building and saving a query, but it is a little more advanced. Let me know if you are interested in this approach.

Having said all of that, if you have a relationship between tblCourse and tblEnrollment, then what is the purpose of the Mandatory for Employee field in tblEnrollment? Can't you just create a join from tblEnrollment to tblCourse to find out if the course is mandatory? That would be my recommendation if possible; however there are instances where I can think where this would not be possible (for example, if a course is mandatory for most people but for certain individuals it would not be mandatory).

I linked the update query to a button in the course form, and it doesn't work; it says its updating 0 rows.. I think i should reference the form checkbox instead or does it matter? If i should do it by form, just let me know. I would like to try it and show you my query :D

How come its left join not inner join? I did that query in design view.

You do not use the SELECT and FROM command when creating an update query. Instead, you will use UPDATE and SET. I believe this is the statement you are needing:

If you're in one of those predicaments where cls.__private attributes just aren't enough since they can easily be accessed through inst._cls__private, and you need something a little more secure, here's ...