Subform wont update

[oxicottin]

Hello, I have a subform frmRequirementsSubform and when the user enters all the text fields on this continuous form its supposed to as you enter a date in txtDateCompleted on the subform it updates another subform and a textbox (txtCompletedInCompleteReq) on the current subform. This textbox reads 0 untill all txtDateCompleted have dates in them then it shows a 1. Once the last date is entered and it shows 1 here is where the issue is, its not running my function. If I play around and delete the date and enter the date then it runs my function so not sure whats going on. Not sure if I have right or not? I can post DB if needed..


Private Sub txtDateCompleted_Change()

If Me.txtCompletedInCompleteReq > 0 Then

Me.Recalc 'Process any pending date completed changes
Me.Parent!frmEmployeeFunctionsSubform.Form!txtDateFunctionCompleted = Date

'Call module modOperatorPosition
Call SetLevel(txtParentFuncID, txtParentEmpID, txtParentDateFunctionCompleted)

Me.Parent!frmEmployeeFunctionsSubform.Requery 'Requery subform frmEmployeeFunctionsSubform
Me.Parent!frmEmployeeLevelSubform.Requery 'Requery subform frmEmployeeLevelSubform
MsgBox "Date Entered Test"

Else

Me.Recalc 'Process any pending date completed changes
Me.Parent!frmEmployeeFunctionsSubform.Form!txtDateFunctionCompleted = Null
Me.Parent!frmEmployeeFunctionsSubform.Requery 'Requery subform frmEmployeeFunctionsSubform
Me.Parent!frmEmployeeLevelSubform.Requery 'Requery subform frmEmployeeLevelSubform

MsgBox "Date Removed Test"

End If
End Sub

[OBP]

Try putting the code in the After Update Event Procdure instead of the On Change one.

[oxicottin]

I have tried the After Update prior with same results.

[OBP]

Does the code actually run?
If you can post a zipped copy of the database (with no personal data in it) I can try some tests.

[oxicottin]

OMG I spent hours messing with this and all it was is...

If Me.txtCompletedInCompleteReq < 1 Then


Sorry I got it!

[OBP]

Well done, I always found it helped solve problems just by explaining what they were to other people.
:thumb:thumb

[oxicottin]

LOL, thats not it either.....:banghead: If I enter dates in the text box I get the correct test message then I delete one and I get the correct message then I enter a date again and then it seems im getting the correct messages but now the date and the NULL are backwards and then if I close the dB and reopen its all goofed up. The text box it goes off of is showing the correct 0 or 1 so I dont now why its doing this. Also I needed some other help besides this.

Im attaching my DB, the frmRequirementsSubform is the form im taking about. When I do get the date to work its supposed to update tblLevel/tblEmployeeLevel with information from the module modOperatorPosition which it does but the issue im having is WHEN WORKING and i enter all the dates it enters the operators position into the tblEmployeeLevel table BUT if I decide to delete a date in txtDateCompleted the operators position still stays there in the level table and then when I enter a date back in and all the requirements are met and the module is ran it creates another position the same as the last one.

I need somethiing like a delete query to run when that happens if two of the same PosID with the same EmpID then delete the one with the one with
oldest date. An employee can only have One of each PosID 1,2,3,4,5

If you wanted to open the DB to view. Select Lenny as supervisor, Ed as employee, Packaging Operator as Functional Area but should auto com up then dates are next to requirements.

[oxicottin]

I noticed when I start entering dates and using the delete key OR highlights the date and entering thats when it does it.

[OBP]

OK, I have looked at the database (nice work) so to overcome your new issue of duplicates can I offer a different solution from SQL to updating the tblEmployeeLevel table and introduce you to recordsets?
If you open a recordset of the table you can check for the duplication prior to either Adding a new record or Updating the original to prevent the duplication.

[oxicottin]

OBP, lol im a access weekend worrier can you show me what your talking about? I gave up on this DB over a year ago and this year is my second wind.....

[OBP]

Sorry for the delay in responding I have been Grand Children sitting.
I will craete the code and repost your database.

I have a question, your dcounts set lngPosID to 0, but you cannot have a zero as there is no zero in the related level table, so what does that actually do?

Allso what is the Pipe database?

[OBP]

OK, I have messed up the selection of the lngPosID because it keeps coming out as 0 (zero) which I couldn't use, if it is zero I set it to 1 to overcome the problem for now.
But I have the adding record or updating a record working OK, but the code is all in the After Udate Event Procedure and I have left your code in place and just used exit sub to not run it.

[oxicottin]

I have a question, your dcounts set lngPosID to 0, but you cannot have a zero as there is no zero in the related level table, so what does that actually do?

Allso what is the Pipe database?

The IngPosID has to be a 0 because when those numbers like 3,5 and 7 come up it doesn't update the record to "Operator 2" as for the pipe I have no clue I thought that was you. I cant get it to work like it should with your example.

[OBP]

When you say "I cant get it to work like it should with your example." do you mean the database that I posted doesn't add record or update a record when it has been changed?
So if lngPosID has to be zero how can you put it in the tblEmployeeLevel table, because it cannot accept a zero as it does not exist in the Level table?
I obviously do not understand that part of your code.

[oxicottin]

When you say "I cant get it to work like it should with your example." do you mean the database that I posted doesn't add record or update a record when it has been changed?
So if lngPosID has to be zero how can you put it in the tblEmployeeLevel table, because it cannot accept a zero as it does not exist in the Level table?
I obviously do not understand that part of your code.

Ok, the reason the lines:


ElseIf DCount("*", "tblEmployeeFunctions", strCriteria) = 7 Then
lngPosID = 0
ElseIf DCount("*", "tblEmployeeFunctions", strCriteria) = 5 Then
lngPosID = 0
ElseIf DCount("*", "tblEmployeeFunctions", strCriteria) = 3 Then
lngPosID = 0

Were put in was there isn't a position for those numbers and when an employee completes a functional area and completes all its requirements it wants to put a position in tblEmployeeLevel so for each one of those it puts a 2 which is "Operator 2" thats and by using that line that puts a stop to that and puts a stop to entering of data at that time on 3,5,7.

Now for your example, I kinda get what your dong but it enters the record right off the bat without completing the requirements. The textbox txtCompletedInCompleteReq is a 0 if they are not all completed and if they are it a 1 is there a way to incorporate that into the recordset?

[OBP]

For the condition where you need a zero, could you not have a 6th level which is N/A or N/R or something, just so a valid value goes in the table?
Also you do not actually need all three lines of code, just one if the condition you want is not met.

Yes, any test can be incorporated in the code, I was only interested in getting the table to added to when there was no previous record or updating a record that is already there.
Where in your code does it currently do that test?

ps I will be going out for tonight's Pub Poker in a few minutes, so let me know as much as you can for me to work on tomorrow.

[oxicottin]

For the condition where you need a zero, could you not have a 6th level which is N/A or N/R or something, just so a valid value goes in the table?
Also you do not actually need all three lines of code, just one if the condition you want is not met.

Yes, any test can be incorporated in the code, I was only interested in getting the table to added to when there was no previous record or updating a record that is already there.
Where in your code does it currently do that test?

ps I will be going out for tonight's Pub Poker in a few minutes, so let me know as much as you can for me to work on tomorrow.


As long as the condition is met you can have 1 line. I guess a 6th level wouldn't matter as long as it doesn't show on the form.

[OBP]

I have reread your post #15 again and looked at the database, so if an employee is doing the Packaging requirements, does that mean that he is supposed to have completed the Supply Operator Requirements first?

[oxicottin]

I have reread your post #15 again and looked at the database, so if an employee is doing the Packaging requirements, does that mean that he is supposed to have completed the Supply Operator Requirements first?

When a new employee comes in they will 99.9% usually complete the Supply Operator and Packaging Operator first then its a matter of numbers of functional areas you complete to get to your next position or level. For instance if you complete (3) functional area you get nothing AKA "lngPosID = 0" but if you complete (4) you are then considered an "Operator 4" level/position and so on after that untill you reach a Operator 5.

[OBP]

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?

[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 :yes
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 :yes
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.

[OBP]

OK, I have slightly modified the VBA code in the Requirements subform and I think it now does what you want, try it out and see what you think.
As to the append queries, that can either be handled by a no duplicates field or we can add some VBA to wherever you add the Employee to either run the similar queries for just that added employee or use VBA code to do the update.

[oxicottin]

Ok, I choose supervisor - Len, Employee - ED and the list populated in the functions subform. I clicked on packaging operator and added a date in every requirement and I got a message saying I completed the requirement. It then put Operator 1 in the position in the functions subform next to packaging operator. It should say Operator 2 but gave me another Operator 1. Here is the criteria, if you complete Supply Op OR Packaging OP it gives you Operator 1 but if you complete both Supply Op AND Packaging OP it should give you a position of Operator 2. For the append queries whats the best way to go about it? is there a way if there is records for that employee empID then don't run the append queries and if there isn't then run them to populate the records. Also, I entered dates for a 3rd functional area and it didn't give me anything in position then I entered dates for a 4th functional area and I got the message and it should have said Operator 3 but nothing came up in the positions box.

[OBP]

Yes I did say that I only had Operator 1 working, I didn't want to go any further if you didn't like the layout.
I am off to Poker in a few minutes, so I will have a look tomorrow.

Where do you add Employees?

I will make a Query or queries that will add a single employee record based on the new employee, that way you can use the current queries to set up tables in the first place and then just add to them.

[oxicottin]

I haven't made the form yet to add employees..... Oh I thought when you said you modified the VBA you meant that as well...

Thanks,

[OBP]

Created the Employee form and Queries, but still working on it, as I have been busy with other things today.
I should be able to post it tomorrow.

[OBP]

OK, I have it Appending Functions & Requirements for a new Employee. I have appended two new employees to test it.
I have also added some queries and a Supervisor Subform.
Take a look and see what you think.
We then need to talk about updating Functions 3 to 8.

[oxicottin]

OBP It will give me Operator 2 correctly and it stays OP 2 even though it supposed to move up an operator to 3 in the image.

22911

[OBP]

Interesting, I did not get an email yesterday either, it looks their notification system has gone a bit buggy.

If you re-read my last post I stated "We then need to talk about updating Functions 3 to 8.", as I have done no work on the higher functions as I am not certain of the rules required.

[oxicottin]

Ok, Operator 3 has to complete (4) of the functional areas, Operator 4 has to complete (6) of the functional areas, Operator 5 has to complete (8) or all of the functional areas. Now for the times an operator completes a function and he/she stays at the same operator level is. If an operator completes 3 of the functional areas they stay an operator 2, if an operator completes 5 functional areas they stay an operator 3, If an operator completes 7 of the functional areas they stay an operator 4.

[OBP]

OK, I can do that.
As the operators have to complete the Functions in order we need to ensure that the user cannot enter a new Function before the previous function(s) have been completed.

[oxicottin]

OK, I can do that.
As the operators have to complete the Functions in order we need to ensure that the user cannot enter a new Function before the previous function(s) have been completed.


It doesn't need to be in order they can complete any functions in any order they do complete the supply and package two first the rest doesn't matter.

[OBP]

Try this version, I think it does what you require.

[oxicottin]

OBP, I played with it for a bit but yes after I figure out how you did it I think this will work! Thank you so much....