Check One, Check all

[jmw229]

Hey Guys,

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.

Any and all help is greatly appreciated, as always.

Jay

[timothybard]

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.

Here is the code for the update query:

UPDATE tblCourses LEFT JOIN tblEnrollment ON tblCourses.ID = tblEnrollment.courseID SET tblEnrollment.mandatory = tblCourses.mandatory;

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).

[jmw229]

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.

Here is the code for the update query:

UPDATE tblCourses LEFT JOIN tblEnrollment ON tblCourses.ID = tblEnrollment.courseID SET tblEnrollment.mandatory = tblCourses.mandatory;

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).

[jmw229]

Just an update - i'm hoping to sit down and check that query out this weekend. I've been busy with work and school :(. I will keep you posted.

[jmw229]

Hey Tim,

I'm just trying it out now. I tried to build one, but having issues:

SELECT tblCourse.[MandatoryForAll?], tblEnrollment.[MandatoryForEmployee?]
FROM tblCourse INNER JOIN tblEnrollment ON tblCourse.CourseID = tblEnrollment.CourseID
SET (((tblEnrollment.[MandatoryForEmployee?])="Yes"))
WHERE (((tblCourse.[MandatoryForAll?])=Yes));

When the course is mandatory for all employees (checked in tbl.Course.[MandatoryForAll?]), then set it to be mandatory for the individual employee field to yes (checked).

I'm going to work at it. :D

As you mentioned, there are many instances when the courses are only mandatory for a select few.

[timothybard]

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:

UPDATE tblCourse LEFT JOIN tblEnrollment ON tblCourse.CourseID = tblEnrollment.CourseID SET tblEnrollment.[MandatoryForEmployee?] = True
WHERE (((tblCourse.[MandatoryForAll?])=True));

The MandatoryForAll? and the MandatoryForEmployee fields are yes/no fields in my case.

[jmw229]

Hey Tim,

I realized that after as well.
I actually came up with a code not to long after:

UPDATE tblCourse INNER JOIN tblEnrollment ON tblCourse.CourseID = tblEnrollment.CourseID SET tblEnrollment.[MandatoryForEmployee?] = "Yes"
WHERE ((([tables]![tblCourse.MandatoryForAll?])="Yes"));

but it didnt work and yours explains why. How come its true not Yes? I thought it could be yes as well.

[jmw229]

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:

UPDATE tblCourse LEFT JOIN tblEnrollment ON tblCourse.CourseID = tblEnrollment.CourseID SET tblEnrollment.[MandatoryForEmployee?] = True
WHERE (((tblCourse.[MandatoryForAll?])=True));

The MandatoryForAll? and the MandatoryForEmployee fields are yes/no fields in my case.

[timothybard]

In this case, using an INNER join should give the same results as using a LEFT join. When I build queries, I usually think of one table as the "base" table and join other tables to it.

With Yes/No fields, you can use either Yes/No or True/False. I use True/False from my programming background. However, if you are using Yes/No fields, then you should not use quotes around Yes and No.

Lastly, what is the purpose of the [tables]! section of your statement? That doesn't seem to work for me.

[jmw229]

I didn't realize i put quotes around it haha.. Ahh i realized why it wasn't working, and I cleared all the values first haha. Also I switched it to yes just in case.

I was using the reference to form style statement but for tables eg. [forms]![formName]etc..thought it would work..

UPDATE tblCourse LEFT JOIN tblEnrollment ON tblCourse.CourseID=tblEnrollment.CourseID SET tblEnrollment.[MandatoryForEmployee?] = Yes
WHERE (((tblCourse.[MandatoryForAll?])=Yes));