Field Of Nightmares

[Alex2015]

I haven't been able to find a non-VBA solution to this problem, but here we go...

There's an employee form that has the person's name & some other info. Within this form is a subform with the training modules the employee has completed & their dates. The main form's & subform's data properties are as follows: Data Entry=no, Allow Additions=yes, Allow Deletions=yes, & Allow Edits=yes. The purpose of this whole setup is so that my users can easily edit an employee's record. So far so good.

Here's where it goes wrong: I have another table (tblUnits) that has a list of the possible work areas (this is a hospital so they're called units). I want to add a combo box to the employee form that lists those units but only for the specific record that you're currently editing. So if I work on the 8th floor, & I am currently editing my own record, just the 8th floor should show up in the combo box.

Here's what I've done: I created a query to base the combo box off of & met with some success. It'll load the appropriate unit for the employee's record you're currently editing, but when I go to change the unit in the combo box to something else, it either doesn't take or only updates one record in my junction table & not all for the same employee.

Adding fields to a form that come from another table is tricky. Do I need to submit my DB?

 

[spikepl]

This is a forum staffed by volunteers. The "askees" are invited to do anything possible to make the life of the responders easier. You have concocted a long story, easy to visualize when you have the structure/tables/forms in fort of you but tough when you don't.

I for one cannot be bothered to try to wrap my head around something a few screenshots would easily define or hugely aid in understanding of.

 

[Alex2015]

This is a forum staffed by volunteers. The "askees" are invited to do anything possible to make the life of the responders easier. You have concocted a long story, easy to visualize when you have the structure/tables/forms in fort of you but tough when you don't.

I for one cannot be bothered to try to wrap my head around something a few screenshots would easily define or hugely aid in understanding of.

Thank you for your response. I know this forum is staffed by volunteers; this isn't my first post on this site. I've been very informative before & the info has proved helpful to others in understanding my problems. I'll post some screenshots shortly.

 

[Alex2015]

The following attachments should make it easier to understand my problem:

1. My relationships
2. tblUnits where I want to get the fields from
3. My form where I want to place that field
4. Junction table to show the structure of the data input

As mentioned, I've created the combo box before in the form, but for editing purposes, it doesn't work. It'll show what unit the employee belongs to but it won't let me edit the unit from this form.

 

[gemma-the-husky]

it would only update one record in the junction table. that's how it works. each record is independent of each other.

I suspect the item field in the junction table is the problem. do you have multiple items, all on the same unit?

if so then your table design is most probably not normalised correctly.

 

[Alex2015]

it would only update one record in the junction table. that's how it works. each record is independent of each other.

I suspect the item field in the junction table is the problem. do you have multiple items, all on the same unit?

if so then your table design is most probably not normalised correctly.

If it's only supposed to update one record at a time in the junction table, then I suspect an update query is the solution to update many records belonging to the same employee? My concern is, "What if my employee changes work areas (units) & now needs all of his/her records to reflect the new unit?"

Using my pic for the junction table as an example, one employee can only belong to one unit at a time, but he/she can have multiple training modules (items) & the completion dates that go with them.

 

[gemma-the-husky]

If it's only supposed to update one record at a time in the junction table, then I suspect an update query is the solution to update many records belonging to the same employee? My concern is, "What if my employee changes work areas (units) & now needs all of his/her records to reflect the new unit?"

Using my pic for the junction table as an example, one employee can only belong to one unit at a time, but he/she can have multiple training modules (items) & the completion dates that go with them.

The real problem is that if you need to update multiple records, then your data analysis is wrong.

In the example you give, what do the training modules have to do with the unit to which the employee is allocated? None that I can see.

What you are describing is akin to a cascading update. This might occur in properly related tables only if a primary key is changed, and with proper design there need never be a reason to change a primary key.

 

[Alex2015]

The real problem is that if you need to update multiple records, then your data analysis is wrong.

In the example you give, what do the training modules have to do with the unit to which the employee is allocated? None that I can see.

What you are describing is akin to a cascading update. This might occur in properly related tables only if a primary key is changed, and with proper design there need never be a reason to change a primary key.

Hi Gemma,

Thanks so much for the reply. What I didn't provide was the list of union queries. In each union query, the training modules (items) are in fact dependent on the work area (unit). For example, people are trained on chest tubes yearly on one unit but may be done every two years on another unit. The completion dates for everything including chest tubes reside in the junction table where these union queries base the calculations off of.

I don't know if this was the most efficient way, but I created eight update queries (for all of the eight units) & plan to attach them to command buttons. The update queries prompt the user to type the last name, first name, & middle initial of the employee they're trying to change units for. This action won't be done that often but I do plan on making this a super-user only function. I tested it & it works as intended. Again, not sure if this was the best way to go but at least it works

 

[Minty]

You could / should have a default renewal days/months for each course ID based on a the unit type in another junction table. Slightly obtuse but repeatable and scalable. If the renewal isn't there then it's not a renewable course?

 

[Alex2015]

You could / should have a default renewal days/months for each course ID based on a the unit type in another junction table. Slightly obtuse but repeatable and scalable. If the renewal isn't there then it's not a renewable course?

Sorry; I was gone from work for a few days. There are 22 items for each employee. Of those 22 items, two of them are renewed every two years; the rest are renewed every year. I would love to default them all to one year but I can't because of this.

Also, it's not that the items aren't renewable if they don't have a default value, but it's the managers' responsibilities to update the due dates for their employees for each item.