Subform wont update

[oxicottin]

OK, got that part, except for the first 3 how do they get Operator 1 - 3?
If you can lay out the steps I can ensure that the right boxes get filled in, or not.
There are other issues, like your current entries have future dates, is that allowed?
Also can the date for say "function" 3 be before function 1 or 2 etc?

Ok, you go through Operator 1,2,3,4,5 in order there is no changing that. Now to the Functional Areas, in order to get to a Operator 1 you have to complete the requirements of the Supply Operator OR Packaging Operator. In order to get to a Operator 2 an employee has to complete all the requirements of the Supply Operator AND Packaging Operator in order to get to a Operator 2 Level/Position. As for the 3 there is no 3 thats in the code as a 0 because it was inserting another Operator 2 into my table when I completed 3 functions same thing happened for 5 and 7.

Next, you asked "There are other issues, like your current entries have future dates, is that allowed" not sure what you mean by future dates but if I completed all requirements and it inserted the level/Position into the table IF I met the requirements like explained above or in my module lets say I did this yesterday and today I came in and took a date out then I wouldn't meet the requirements so then it would remove the level/Position OR if I changed the date it would change the date in the level table to today's date.

I can never have 2 of the same entries in tblEmployeeLevel because like I said an employee can only be an Operator 1,2,3,4,5, one time.

Hope this helps if not Ill try an d explain better....

[OBP]

Yes, that is much better, although I am not sure about " As for the 3 there is no 3 thats in the code as a 0", but there is an "Operator 3" in the table.
The date issues from the fact that your database has the "Achieved Dates" for the first 5 Requirements as the 14th of September, ie in 2 days time
So if you set the Achievement date to nothing you want to delete the record it applies to in the table Employee level, what about the EmployeeFunctions table?
So how do the employees reach Operator 3, 4, 5, 6 & 7 levels and do they have to be in order or can they come straight in as say Operator 5?
I may be mixing up Functions & Operators here.

[oxicottin]

Yes, that is much better, although I am not sure about " As for the 3 there is no 3 thats in the code as a 0", but there is an "Operator 3" in the table.
The date issues from the fact that your database has the "Achieved Dates" for the first 5 Requirements as the 14th of September, ie in 2 days time
So if you set the Achievement date to nothing you want to delete the record it applies to in the table Employee level, what about the EmployeeFunctions table?
So how do the employees reach Operator 3, 4, 5, 6 & 7 levels and do they have to be in order or can they come straight in as say Operator 5?
I may be mixing up Functions & Operators here.

Ok, Yes there is an operator 3 BUT if you look at the requirements to be a Operator 3 it requires you to complete (4) functional Areas not it you only complete 3 which does nothing yet for any Operator 3,4,5 because they haven't produced enough functional areas.

Yes Operator 1,2,3,4,5 goes in order you cant jump, they start out at a Op1 and move up the chain.

Hope this helps....

[OBP]

OK, that is good.
Now, I will need to think about how to test all of that, at the moment a Zero outcome I assume means that the Employee level table should not be updated at all, which is easy enough?
Have you looked at the code to see how the recordsets work?

[oxicottin]

a Zero outcome I assume means that the Employee level table should not be updated at all

You are correct..... I tried to run the code based on if frmRequirementsSubform txtCompletedInCompleteReq was a 0 dont run the code and if it was a 1 run it but something is goofy if you start deleting end entering dates in txtDateCompleted.

Yes I played with the recordset for a few hours last night and couldn't figure it out...

[OBP]

When you say goofy what actually happens?

What would you like explained about the Recordset?

[OBP]

I have a question about practicallity with regards to the frmEmployeeFunctionsSubform which displays the Functional Areas achieved, why do you not use the same display method as the frmRequirementsSubform, ie show all the functions in order.
Currently you can select any of the Functions regardless of whether the previous required functions have been met, but the only 2 that should be allowed to be entered first are the Supply & Packaging.
If they were all displayed it would be easier to control which ones were allowed to be entered.

Also should the Date Completed only be entered when all the Requirements have been met instead of after each requirement?

[oxicottin]

I have a question about practicallity with regards to the frmEmployeeFunctionsSubform which displays the Functional Areas achieved, why do you not use the same display method as the frmRequirementsSubform, ie show all the functions in order.
Currently you can select any of the Functions regardless of whether the previous required functions have been met, but the only 2 that should be allowed to be entered first are the Supply & Packaging.
If they were all displayed it would be easier to control which ones were allowed to be entered.

I would like to display all the Functional areas at once but wouldnt that mean there would be a records created in frmRequirementsSubform for each of them? Some employees might never do some of the requirements for one of the functional areas so then I would have records/data I dont need right or maybe im wrong? Thats what I originally wanted to do...

Also should the Date Completed only be entered when all the Requirements have been met instead of after each requirement?

Yes

When you say goofy what actually happens?

It starts working backwards its like the line If Me.txtCompletedInCompleteReq < 1 Then is now > and instead of one field being null and the other applying a date they switch... in txtDateCompleted_Change()

[OBP]

Yes you would have unnecessary records, but they are only a couple of fields with ID numbers in, so it would depend on how many Employees you have, if it was thousands it would take a bit of space.

OK, that is a check that needs building in.

The On Change VBA would not be required.
It is odd because when using the original "On Change" code it will not allow a manual date entry, it triggers on the very first date character.

I will try coding in what we have discussed and see what you think of it, if that is OK?

[oxicottin]

This is only for my department which is 15 of them so were good

[OBP]

I have a question for you, If the Operator 1, 2, 3 etc is dependent on Functional Areas, why can't it be in the Functional areas table?

[oxicottin]

Not sure why just thought it needed it's own table I guess.

[OBP]

I am not sure either, do you think it would work as an extra field in the Functional Areas Table, they already share the same Achievement Date?

[oxicottin]

when you say they share the same achievement date your meaning the day you achievied a functional area could b the same day you moved up to and Operator 2, Not the requirements dates right?

[OBP]

Yes when an employee completes the Requirements for Functional Area, that should trigger both the Functional Area Date Completed and the Position Date Achieved if it is applicable.

[OBP]

OK, I have done some work on your database in line with my comments/questions.
I have modified the Functions form and table to include the Posid and added the Position to the Functions form.
I have modified the requirements form and it's code so that it updates both itself and the functions form.
The Requirements form will ask if you are sure you want to delete a date, if you say no it will put back the original date.
If you say yes it will remove the date on that form and if it is also on the functions form if it matches along with the PosID.
The functions PosID and the date are only updated when all the requirements have been met for that function.
At the moment I have only created the code to update the PosID for Functions 1 & 2, if you want to go with this design then I or you can create the rest.
Let me know what you think.

ps I have left your and my old code in place.

[oxicottin]

Im so sorry I never got an email that you responded, I just thought you was working on it... I'll take a look in the morning...

[oxicottin]

OBP, I like the design but I did notice its giving me Operator 1 for the supply op and package op and its giving me the position before I complete the requirements. I also noticed it does this when you enter/delete dates from the Function area subform. How does it enter all the functions and requirements for lets say a new operator and how is it auto populating the functions area forms list? can we continue on this design? Thanks!

[OBP]

It shouldn't give you the Operator until all the Requirements have been completed, I will need to check that.

The Auto Populate was done using the 2 Append Queries that I added, if you wanted to run it more than once we would need to create a Unique field so that you do not get duplicates in the table.

Can you describe what you did to get the Operator to appear when it shouldn't?

[oxicottin]

It shouldn't give you the Operator until all the Requirements have been completed, I will need to check that.

The Auto Populate was done using the 2 Append Queries that I added, if you wanted to run it more than once we would need to create a Unique field so that you do not get duplicates in the table.

Can you describe what you did to get the Operator to appear when it shouldn't?

The append querys I would need to run again because what do I do when I get a new employee? To get the operator to appear I was deleting and entering dates in the function subform. This was after Operator 1 was already entered.