alpinegroove
Technical User
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, EmloyeeCar is update to "-1". What I am trying to accomplish is that if Role 3 is deleted or changed, to change the "-1" in the same field to "0."
I am not sure if VBA or a query is the answer.
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.
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?
Thanks
I have a form linked to tblProjects and subform linked to the junction table tblProjectEmployeeRole.
There is an After Update event on the subform:
Code:
If Me.fkRoleID = 3 Then
DoCmd.RunSQL "UPDATE tblEmployees SET EmloyeeCar = '-1' WHERE EmployeeID = Forms!frmCourseInfo!EmployeeID"
End If
This part works. If Role 3 (Driver) is selected, EmloyeeCar is update to "-1". What I am trying to accomplish is that if Role 3 is deleted or changed, to change the "-1" in the same field to "0."
I am not sure if VBA or a query is the answer.
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.
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?
Thanks