After Update Logic - Query or VBA?

[alpinegroove]

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:

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

 

[Andrzejek]

If Me.fkRoleID = 3 Then
DoCmd.RunSQL "UPDATE tblEmployees SET EmloyeeCar = '-1' WHERE EmployeeID = Forms!frmCourseInfo!EmployeeID"
End If

This part works.

Well, it shouldn't.

Try:

Dim strSQL As String
...
If Me.fkRoleID = 3 Then
    strSQL = "UPDATE tblEmployees SET EmloyeeCar = '-1' WHERE EmployeeID [blue]= " & [/blue]Forms!frmCourseInfo!EmployeeID[blue]
    Debug.Print strSQL[/blue]
    DoCmd.RunSQL strSQL
End If

Have fun.

---- Andy

 

[alpinegroove]

1. Why shouldn't it work, and if it shouldn't why does it work?
It does update the field with "-1" when the role is 3.

2. How do I handle the other part, when it Role 3 is removed or changed?

Thanks

 

[Andrzejek]

It shouldn't work because you are trying to run the (string) as an SQL command of:
[tt]
"UPDATE tblEmployees SET EmloyeeCar = '-1' WHERE EmployeeID = Forms!frmCourseInfo!EmployeeID"[/tt]

Unless in your tblEmployees table in your field EmployeeID you have an entry of Forms!frmCourseInfo!EmployeeID instead of 0, or 1, or 2, etc.

With your syntax, try this:

Dim str As String
....
If Me.fkRoleID = 3 Then
    str = "UPDATE tblEmployees SET EmloyeeCar = '-1' WHERE EmployeeID = Forms!frmCourseInfo!EmployeeID"
    MsgBox str
    DoCmd.RunSQL str
End If

And see what you will have in a message box. That's what you send to your data base as UPDATE statement.

Of course, your OP may be just a typo and not copy/paste from your real code.


Have fun.

---- Andy

 

[PHV]

Sorry Andy, but you're wrong.
The RunSQL method is happy with forms references (instead of any recordset).

 

[Andrzejek]

PHV, I don't mind to be proved wrong. How would I learn? But are you saying thet this Update statement, which is just a string, would update anything:
[tt]
"UPDATE tblEmployees SET EmloyeeCar = '-1' WHERE EmployeeID = Forms!frmCourseInfo!EmployeeID"[/tt]
and that what is send to a data base.

I assume EmployeeID field is a Number in the data base, and I don't think any data base is smart enough to know that it needs to convert the part of this Update statement to be:
[tt]
"UPDATE tblEmployees SET EmloyeeCar = '-1' WHERE EmployeeID = 5"[/tt]

Notice that [tt]"EmployeeID = Forms!frmCourseInfo!EmployeeID"[/tt]
and not [tt]"EmployeeID = " & Forms!frmCourseInfo!EmployeeID[/tt]



Have fun.

---- Andy

 

[alpinegroove]

I am a beginner and by no means claim to have a thorough understanding of any of this, but this is how I think this works.

If I understand this correctly, yes, if the EmployeeID currently showing in frmCourseInfo is 5, then it would be the same as WHERE EmployeeID = 5.

Referring to the form in this manner, Forms!frmCourseInfo, is just like referring to a query or a table. You could also use it as a criteria in the query design view, in which case the form would need to be open for the query to work.

 

[PHV]

Andy, please, reread carefully this thread.

 

[Andrzejek]

alpinegroove -
In your tblEmployees table you have fields like EmloyeeCar and EmployeeID, and (I assume) 1 records per employee. And any employee can be a Driver, or teacher, or coder, or whatever. So any one employee CANNOT be a Driver for Project1 and a Coder for another Project. Is that correct?
And how EmloyeeCar field relates to Projects?

PHV -
I value your input on TT and respect your knowledge. And I do know you can reference a field from the Form, or a variable, in a string. I do it all the time. But all what I am saying is: there is a big difference between:
[tt]
MsgBox "[blue]EmployeeID = Forms!frmCourseInfo!EmployeeID[/blue]"
and
MsgBox "[blue]EmployeeID = [/blue]" & [red]Forms!frmCourseInfo!EmployeeID[/red]
[/tt]
I will just assume there was a typo on OP.


Have fun.

---- Andy

 

[alpinegroove]

There was no typo. That reference is the in the criteria. It is only use to establish the match, and the code that I posted does update the field to "-1" when the Role=3.

My people can have different roles in different projects, even different roles in the same project.
I have a junction table, tblProjectPeopleRole, where I create the association between a person and a project and assign the role.

So one person can be both a driver and a coder in Project 1 and a teacher in Project 2, for example.

 

[Andrzejek]

So if John Smith is a Driver in Project1 and Project2, he has -1 as a value in EmployeeCar field in tblEmployees table. Right?
That's according to your statement: If Role 3 (Driver) is selected, EmloyeeCar is update to "-1". What will happen when John Smith is removed from Project1? The value in EmployeeCar should be updated to 0 because he is no longer a driver for Project1? But he is stil the driver for Project2, so the value in EmployeeCar should be -1...

How would that work?
Or am I missing all of this?



Have fun.

---- Andy

 

[alpinegroove]

Sorry, I missed that in your previous message. EmployeeCar is actually a field in tblProjects so it is not linked to a specific person.

 

[dhookom]

Andy,
PH is correct that you can reference a control on a form from within the SQL Statement. Most of us veterans would use your solution but either is correct. Try it.

My concern on this entire thread is probably the same as Andy which is storing the same information in multiple tables.

Duane
Hook'D on Access
MS Access MVP

 

[alpinegroove]

I came up with this but am getting a type conversion error failure:

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

ProjectHasDriver is a yes/no field.

This might be the wrong direction altogether

 

[alpinegroove]

Thanks, dhookom. How is it the same information.

I am trying to say: if Role X is associated with a record in the junction table, put a different value in a different table, different field.

 

[alpinegroove]

Sorry I messed up the field names; it should have been:

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

 

[alpinegroove]

The type conversion failure error was my fault. Forget that. But that query does produce the result I anticipated.

 

[dhookom]

If EmployeeCar is numeric, you need to use numbers in your SQL rather than text strings. The same is true for RoleIDFK which I expect is numeric.

I'm attempting to stay away from your final objectives but try

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

Duane
Hook'D on Access
MS Access MVP

 

[alpinegroove]

does produce" should have been "does not produce"

 

[alpinegroove]

Yes, the error was my fault.
ProjectIDFK is numeric. That was the problem.
EmployeeCar is a yes/no field.
I am no longer getting the error now that I fixed that.

Why are you trying to stay away from my final objective?
The database has certain limitations. It was not fully normalized in the first place (for a reason). I know that, but I am still trying to solve the problem.