Solved Form: Changes in field will update another table thru After Update Event (1 Viewer)

[evictme]

Hello all,

I have been trying to find a way to update a specific record on one table when there is a change on one of my forms.

The form I have is a Client Form that has combo boxes for the worker(s) assigned. after update causes the worker ID to be updated, I would like to include in this event the a change to the that worker's record in the "Employee" table with the value of a specific field.

I have been trying to do it with DAO but cant seem to figure out the correct syntax that will call the table and find the record THEN update.
Additionally, I've been try to use the DoCmd.RunSQL in the event to "insert" into the table but this does not allow for finding a specific record, or at least Im not sure if it does.

I'd like to do this without entering a NEW Entry. I am familiar with the DAO syntax for creating NEW entries but not editing.

Any help would be GREATLY appreciated.

Thank you,

Tables:
CLIENTS
Fields: [WorkerName], [WorkerID]

EMPLOYEES
Fields: [Employee], [EmployeeID]

Tables JOINED by [EmployeeID]<->[WorkerID]

I want to capture the [Supervisor] field from the CLIENTS form on the Afterupdate event and ADD the value of that field to the EMPLOYEE Table for that specific record.

 

[theDBguy]

Hi. What is the difference between Clients and Employees, or was there a ClientID field you just didn't show us? Besides, where is the "supervisor" information coming from? If from the Clients Form, why there? What is the relationship between clients and employees?

 

[evictme]

Hi. What is the difference between Clients and Employees, or was there a ClientID field you just didn't show us?

Yes, CLIENTS have a ClientID field. I included the relevant fields but CLIENTS and EMPLOYEES are separate tables that both have their own FORMS. As i mentioned, they are Joined by EmployeeID and WorkerID.

 

[theDBguy]

Yes, CLIENTS have a ClientID field. I included the relevant fields but CLIENTS and EMPLOYEES are separate tables that both have their own FORMS. As i mentioned, they are Joined by EmployeeID and WorkerID.

Hi. I have edited my post to add more questions. Normally, you wouldn't update a table based on another if it's like a "calculation process." For example, it's not advisable to store the person's "age" because one second after you do it, it's already incorrect. Instead, you would use a query to get the "latest" information from your tables.

 

[evictme]

Hi. I have edited my post to add more questions. Normally, you wouldn't update a table based on another if it's like a "calculation process." For example, it's not advisable to store the person's "age" because one second after you do it, it's already incorrect. Instead, you would use a query to get the "latest" information from your tables.

Hi. What is the difference between Clients and Employees, or was there a ClientID field you just didn't show us? Besides, where is the "supervisor" information coming from? If from the Clients Form, why there? What is the relationship between clients and employees?

Both Client and Employee Tables have a Supervisor field. Relationships between clients and employees is dictated by the Client form/table. CLient is assigned a Supervisor and subsequently the Employee is then associated to the Supervisor based on the Client assigned.

We assign workers (Employee) thru the Client Form by way of combo box. So, the Client record indicates the correct Supervisor. When the Employee is assigned to the Client as a Worker the Client would already have a Supervisor value.

I would simply like to copy the Value for that Client into the Supervisor Field on the Employee Table for the specific worker.

The supervisor field is not calculated. It is an actual field that uses a combo box for selecting the value on the actual Client Form.

 

[theDBguy]

Okay, in that case, you don't even need supervisor in the employee table. In this sense, the supervisor is "calculated" because it depends on the client. In other words, if I'm assigned to a client and a client is assigned to a supervisor, I can transitively tell who is my supervisor from my client. All you need is a query to show this information. You "shouldn't" have to store the supervisor information twice and then have to worry about synchronizing the information every time the client data gets changed.

 

[evictme]

Okay, in that case, you don't even need supervisor in the employee table. In this sense, the supervisor is "calculated" because it depends on the client. In other words, if I'm assigned to a client and a client is assigned to a supervisor, I can transitively tell who is my supervisor from my client. All you need is a query to show this information. You "shouldn't" have to store the supervisor information twice and then have to worry about synchronizing the information every time the client data gets changed.

Yea. Believe me, I thought that for a long time. What happens is that sometimes we have Employees (Workers) who are NOT assigned to ANY Clients BUT we have other reports/queries in which having that Employee (Worker) appear is important.

My solution to this was to add a Supervisor Field to the Employee table which is managed manually for those cases in which the Employee(Worker) is not assigned or not yet assigned a Client. This field allows me to Union Join Employees with Assigned clients and ACTIVE employees w/o Assigned clients into a query that is sorted by supervisor.

It is complicated because of how we use the database and the hierarchy of main work in the office. It is important for Supervisors to access information on ALL workers that are assigned to them or potentially assigned to them or that were at some point assigned to them.

 

[theDBguy]

Hi. The solution for reporting employees without clients is not to assign a supervisor to them but to use an OUTER JOIN. If you must have a supervisor assigned to each employee, then your current table structure could probably be changed so that supervisors are assigned to the employees rather than the clients. For example, if I had to deal with an employee, as a client, a supervisor at that company is not necessarily assigned to me, but to that employee. So, when I need to speak to the supervisor, I would ask the employee. I am not sure if that translates to your own business process, but the concern I have is when you assign a supervisor to an employee through the client, your data could get out-of-sync. I always thought bad data is worse than no data.

But if you really insist on using code to update the employee table, then you should be able to use an UPDATE query to "copy" the supervisor information from clients to employees. For example:

CurrentDb.Execute "UPDATE Employees SET SupervisorID=" & Me.SupervisorID & " WHERE EmployeeID=" & Me.WorkerID, dbFailOnError

Hope that helps...

 

[evictme]

I have been using this code for adding new records from another form. Anyway to adapt the syntax to EDIT a Record based on a specific field (WorkerID)? -

Private Sub Form_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM ClientNotes")

rs.AddNew

rs![Notes] = Me.Home_Visit_Notes
rs![NotesDate] = Me.Home_Visit_Date
rs![ClientID] = Me.ClientID
rs![User updated] = Me.User_Updated
rs![Date modified] = Me.Date_Updated
rs![Time modified] = Me.Time_Updated

rs.Update
rs.Close

Set rs = Nothing
End Sub

 

[theDBguy]

Sure. You could try something like:

Set rs = CurrentDb.OpenRecordset("SELECT SupervisorID FROM Employees WHERE EmployeeID=" & Me.WorkerID, dbOpenDynaset)
If Not (rs.BOF AND rs.EOF) Then
    .Edit
        rs!SupervisorID=Me.SupervisorID
    .Update
End If
rs.Close
Set rs = Nothing

Hope that helps...

 

[evictme]

Hi. The solution for reporting employees without clients is not to assign a supervisor to them but to use an OUTER JOIN. If you must have a supervisor assigned to each employee, then your current table structure could probably be changed so that supervisors are assigned to the employees rather than the clients. For example, if I had to deal with an employee, as a client, a supervisor at that company is not necessarily assigned to me, but to that employee. So, when I need to speak to the supervisor, I would ask the employee. I am not sure if that translates to your own business process, but the concern I have is when you assign a supervisor to an employee through the client, your data could get out-of-sync. I always thought bad data is worse than no data.

But if you really insist on using code to update the employee table, then you should be able to use an UPDATE query to "copy" the supervisor information from clients to employees. For example:

CurrentDb.Execute "UPDATE Employees SET SupervisorID=" & Me.SupervisorID & " WHERE EmployeeID=" & Me.WorkerID, dbFailOnError

Hope that helps...

Unfortunately, the supervisors are technically the supervisors for the Clients specifically. They are supervisors to ALL the workers, if that makes sense. So, for internal purposes the employees are regarded in that way. It's dumb and i've been fighting it for a long time but its how they want the information to be maintained.

 

[theDBguy]

Unfortunately, the supervisors are technically the supervisors for the Clients specifically. They are supervisors to ALL the workers, if that makes sense. So, for internal purposes the employees are regarded in that way. It's dumb and i've been fighting it for a long time but its how they want the information to be maintained.

Hi. The keyword there is "technically." I wish you the best. Please let us know if the suggestion above doesn't work. Good luck!

 

[evictme]

Sure. You could try something like:

Set rs = CurrentDb.OpenRecordset("SELECT SupervisorID FROM Employees WHERE EmployeeID=" & Me.WorkerID, dbOpenDynaset)
If Not (rs.BOF AND rs.EOF) Then
    .Edit
        rs!SupervisorID=Me.SupervisorID
    .Update
End If
rs.Close
Set rs = Nothing
[code]
Hope that helps...

This is perfect. I couldnt find the correct syntax for this and it seems I wasnt using the correct phrasing for when doing a Google search.

Thank you again. I will report back with what finally works!

 

[theDBguy]

This is perfect. I couldnt find the correct syntax for this and it seems I wasnt using the correct phrasing for when doing a Google search.

Thank you again. I will report back with what finally works!

Hi. You're welcome. Cheers!

 

[evictme]

I am getting a syntax error: Any ideas? Syntax Error (missing operator) in query expression '[EmployeeID]=',

 

[theDBguy]

The name of your employee table is "Employee" without an "s," right? The name of your field in that table is EmployeeID and it's numeric, correct?

 

[evictme]

The name of your employee table is "Employee" without an "s," right? The name of your field in that table is EmployeeID and it's numeric, correct?

Yes, to both those questions.

 

[theDBguy]

Okay, then try it this way:

Dim strSQL As String
strSQL = "SELECT Supervisor FROM Employee WHERE EmployeeID=" & Me.WorkerID
Debug.Print strSQL
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
...

And show us what you get in the Immediate Window from the Debug.Print statement. Then, copy and paste it into a new query to see if you could run it.

 

[evictme]

Okay, then try it this way:

Dim strSQL As String
strSQL = "SELECT Supervisor FROM Employee WHERE EmployeeID=" & Me.WorkerID
Debug.Print strSQL
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
...

And show us what you get in the Immediate Window from the Debug.Print statement. Then, copy and paste it into a new query to see if you could run it.

I may be placing it in the wrong spot, When I debug it does not open a window, it only highlights the strSQL line

 

[theDBguy]

To see the Immediate Window, try pressing the Ctrl + G key combo.