Hi all. I've lurked here for over a year now, but I finally have a problem that I haven't found an answer for.

I run an update query between tblEmployeeInfo and tblDept so that whenever three check boxes in tblDept change, the change is updated in tblEmployeeInfo. I have a combo box on frmEmployeeInfo to update the Dept field, and I would like to update the three applicable check boxes for the current record only. Right now I just have the query run AfterUpdate for cboDept, but that updates all 211 employees. Not that it takes long, but I'd rather it just update the current record.

Tables: tblEmployeeInfo, tblDept
Fields: HepB, TB, VMMR
Reason: Whether or not the above three tests are applicable to an employee is based upon which department they work in. Someone working in HR won't need to be tested at all, whereas someone working in the lab will need to be tested for all three. When an employee changes departments or someone new is hired, these three fields should be updated according to their department.

Other forms are dependent on the info in tblEmployeeInfo and the value of the three afore-mentioned fields, so a sub-form just using tblDept will not work.

Thanks for any and all help/ideas!

It sound like the best approach is to create a SQL string in the code in the AfterUpdate event.

The string would use constant text for the "UPDATE" part and use a variable value in the "WHERE" clause using the primary key of the current record in tblEmployee. That assumes the primary key of tblEmployee is a field on your form.

After the string is built;
DoCmd.RunSQL strYourSQL
will execute the SQL and update one row.

Last but not least, it sounds like the checkboxes you mention should *NOT* be part of tblEmployee. The values appear to be attributes of the Department and not of the Employee.

HTH,
RichM

Thanks. With a little SQL tweaking, your advice worked like a charm.