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]

OK, I will take a blame. For reading the post 'literally', so when I see:
[tt]
DoCmd.RunSQL "UPDATE tblEmployees SET EmloyeeCar = '-1' WHERE EmployeeID = Forms!frmCourseInfo!EmployeeID"
[/tt]
I assume EmloyeeCar (although miss-spelled) is a field in tblEmployees table. And I am wrong because "EmployeeCar is actually a field in tblProjects" - who knew....?

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.

I agree with first part of this statement - I use it all the time. But I would like to know what you mean by "either [method] is correct"?

Dim iSomeNumber As Integer
iSomeNumber = 5

MsgBox "The Number is iSomeNumber"
MsgBox "The Number is " & iSomeNumber

No compiler is smart enough to recognize in first Message Box that I want to replace (reference) iSomeNumber with number 5 - IMHO of course

Have fun.

---- Andy

 

[dhookom]

I'm a normalization cop. I don't know your exact table structures but it seems like you are storing data in a couple places. If you think different, you might want to post your table structures and some related records.

There might be a justification for un-normalized data storage so I don't like to judge other people's motivation.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

I agree you can't embed a variable name inside a SQL statement. You can embed the reference to a control or possibly a function name that will resolve when run. This isn't much different from creating a query in design view with a reference to the value of a control on a form.

I created a form in Northwind with a text box and command button. My code is:

Private Sub Command2_Click()
    MsgBox DCount("*", "Employees", "EmployeeID = Forms!frmCourseInfo!EmployeeID")

    MsgBox DCount("*", "Employees", "EmployeeID = " & Forms!frmCourseInfo!EmployeeID)
End Sub

Running the code causes 2 message boxes each with the value 1 assuming I entered a good employeeID into the text box. Clearly Access could resolve the reference to the value of the control.

Duane
Hook'D on Access
MS Access MVP

 

[alpinegroove]

Andrzejek, I am sorry I wasn't clearer in the first place.

 

[Andrzejek]

dhookom -
WOW! I did not know that. My fault :-( I guess you can learn something new even on Friday afternoon. Thanks

alpinegroove -
That's OK. We just cannot see into your mind or what’s on your screen, so we have to trust to what you type in a message. It helps a great deal if you have all the facts correct. Otherwise it takes several responses to straight things up.

Have fun.

---- Andy