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.

[oxicottin]

LOL, thats not it either..... 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?