Changing field in table based on combobox

[MightyRoo]

Help!
I have a table called tblworkorders and a table called tblemployees. I need to have a "status" of each employee that displays what workorder they are on.

I have a frmproductionsignoffs with the workorders information and a dropdown to select the employee that will be working on the workorder. When this is selected I need to edit the table with the employees so that the field "employee project" receives the current workorder that the employee is working on.

Each workorder could have multiple employees working on it at the same time so I need to have the workorder they are on stored in tblemployees rather then just putting the employees name in to the tblworkorders.

I would really appreciate some direction as I cannot seem to pick the right keywords to find the solution.

Thanks in advance!

 

[Spenney]

Since each work order can have multiple employees, I think I would have a third table along the lines of "tblOrderEmployeeRelationship" with its own ID column and then two further columns to show the Work Order ID and Employee ID. You can then use this relationship to view work orders per employee and vice-versa.

Any help ?

 

[MacCaillean]

Hi SierraBlind

Are you happy to discard historical information about the workorders each employee has worked on?

If yes, you can store the workorder number in tblEmployees as you suggest. When the employee completes that workorder and is assigned to a new one, however, the information linking the employee to the completed workorder has to be discarded if you update the field in tblEmployees with the new workorder.

It seems to me that the relationship between workorders and employees is many-to-many, in that each workorder can have many employees working on it, and each employee will, over time, work on many workorders.

To retain this information you should create a link table, with a minimum of two fields, an employee id field and a workorder id field. The link table, as its name suggests, joins to tblEmployees (using the employee id field in both), and joins to tblWorkOrders (on the workorder id field). Both joins should be one-to-many, and in both, the many side needs to be on the link table.

This allows you to have unique records in tblEmployees and tblWorkOrders, whilst the link table has all the combinations of employees with workorders.

Hope this helps.

Mac

 

[MightyRoo]

Spenny and Mac,
I appreciate the responses. I am a little lost on the use of additional tables. I do not need to store what workorders the employee is on for the future as I have that ability (they actually have multiple signoffs per workorder)in another way. My problem is that the employees forget what they were signed in on. This allows them to not finish a project which means we can't get an accurate time on the projects.

What I think needs to happen is to have my code change a field in the employees table to have the workorder in it. That way I can have a form that displays the employee along with the current workorder they are on. This can be a simple text field if I am not mistaken.

My problem is I am not sure how to have code go and change a field in a table that is not present on the form. I could hide a subform and change the data on it. However the code for that still eludes me.

Thanks for all the response!
Scott

 

[MacCaillean]

Okay Scott

So you don't need to store the value and you don't need to display it on the workorders form. You just need it set to show the current workorder assignment on an employee form.

Okay, easiest way is probably to add a dropdown to your form. This dropdown will get its source data from the employees table. When you select a name from the dropdown, what your database will see is the employee id. With this, and with the workorder id on your form, you can run an update query to update the workorder id of all records in the employees table where the employee id is the same as your dropdown (which should be a single record).

Let me know if this makes sense to you.

Regards

Mac

 

[MightyRoo]

Mac, I think I see what you are suggesting. I will play with the database tonight! Thanks!
Scott

 

[MightyRoo]

Mac,
Thanks for the response. I searched based on your idea and came up with the code like this...

DoCmd.RunSQL "UPDATE TblEmployees " & _
"SET TblEmployees.EmployeeCurrentWO = tblWorkOrders.WorkOrderNumber " & _
"WHERE TblEmployees.EmployeeFName = CmbSlatsFabricInEmployeeSelect;"

It seems to be working! Thanks for the idea! I really appreciate it!
Scott