I posted this on a couple of other forums but haven't been able to resolve it. I am having trouble figuring out how to accomplish the following:

I have a form linked to tblProjects and subform linked to the junction table tblProjectEmployeeRole.

There is an After Update event on the subform:


This part works. If Role 3 (Driver) is selected, the EmloyeeCar field is updated to "-1". This is what I need: if Role 3 is deleted or changed, "-1" in the same field should be automatically updated to "0."

I am not sure if VBA or a query is the answer
I also don't know if this process should be tied to the form or whether it should be a query that runs against the junction table directly.

More details:

I am using Access 2002.
There are 4 possible roles, different people, and different projects.
Examples:

Project 1
John Smith, Driver
Michelle Glow, Driver
Michael Star, Mechanic

Project 2
Jeff Smirk, Teacher
Linda Jame, Coder
Gina Marcus, Driver

When the role Driver is selected for a Project, "-1" is automatically entered in tblEmployees.EmloyeeCar. This works.

However, let's assume that Gina Marcus is no longer part of the Project 2 team and I use the subform to delete the record in the junction table that relates her to the Project; well now I want the "-1" to be changed to "0."

As you can see in Project 1, at the same time, even if John Smith drops from the Project, Michelle Glow, the other Driver, is still there, so the "-1" should remain.

Basically, whenever there is at least one Driver (Role 3) in the Project, I need a "-1" in tblEmployees.EmloyeeCar, but when there is no Driver in a Project, I need a "0" in that field.

Should I take a query or VBA approach here, or a combination?

Thanks

You reached one limitation of Access here: You cannot guarantee the data consistency between two tables in this way because Access has no triggers and only has very limited possibilities as far as constraints (validation rules) are concerned.

If the form is the only place in your application where what you name the "role" can be changed, I would go for running an UPDATE query in the AfterUpdate event of the control related to the "role" column.

In more complex situations, I would consider creating a class that would incorporate the necessary rules and only access the data through an instance of this class.

In any case, opening the table directly should be forbidden.

Thank you for your reply.

If this is an Access limitation, are there other applications or database systems that could do this?


Currently the After Update procedure is linked to the subform. Are you saying that I should link it to the combo box that is used to select the role and then have the procedure run an update query with the selected role as the criteria?

What do you mean by class here?

Thanks again.

1. Any RDBMS that implements triggers (MS SQL Server, Oracle, etc.). I don't know Oracle enough, but in SQL Server there would be other solutions (apart from triggers), using computed columns and/or data constraints.

2. Precisely: the place where the data is changed seems to be the most logical place where to modify any associated or logically related piece of information.

3. This is a complex matter that requires an in-depth knowledge of Access, of the VB/VBA language and of Object-oriented programming in general. In Access you can create Class modules that are not bound to a form or to a report. In such a Class, you can use code to implement any rule you want. This class would act as a proxy, an intermediate layer, between the table(s) and the application interface. Rules for updating a value in one table when a defined value is inserted or updated into another table could be implemented there. Of course, you would probably lose many built-in functionalities of Access with this solution.

This makes sense, but I am struggling with coming up with the logic.
Can such a query be done in design view or do I need to enter an IF statement into the SQL code?

I am still not sure whether the query should review the combo box selection or the junction table that holds that selection.

I see that I can enter both After Update and On Change procedures. But is deleting a record/role considered a change? I don't see On Delete.

Something like this:

I don't think I understand this, but I will try...

Could something like this work?

UPDATE tblProjects INNER JOIN tblProjectPeopleRoles ON tblProjects.ProjectID = tblProjectPeopleRoles.ProjectIDFK SET tblProjects.EmployeeCar = IIf([RoleIDFK]="3","-1","0");

I am getting a type conversion error failure.

If the column "ProjectIDFK " is defined as a numeric data type, it should be:
You don't include numeric values between quotes.

Moreover, you don't define the row in which to test for the value of RoleIDFK, nor the table in which to search for it.

Yes, ProjectIDFK is numeric. I removed the "", so I am no longer getting the type conversion failure error.

I added the table name but I thought that was already implied by the beginning of the code:

What do you mean by "define the row in which to test for the value of RoleIDFK"?

Thanks for your help.

1. When you have a query expression with more than one table (INNER JOIN, etc.) it's recomanded to use the full name: TableName.ColumnName, because you'll get an error if several tables included in the query have a column with the same name.

2. When you do not specify a WHERE condition, a query works on a whole data set, not only on the row that represents the current record of the form. In the present case, the test inside the IIF function is performed on every row of the data set issued from the INNER JOIN operation. So, for every row where [tblCoursePeopleRoles].[RoleIDFK]=3, [tblCourses].[CourseHasTA2] will be set to -1; it will be set to 0 otherwise.

Thank you for the clarification. I got this thing to work using a combination of VBA and sql. All's good, for now...

You're welcome!