Update a text box using query

[oxicottin]

hello, Im trying to setup this DB but am having such a hard time. I have a subform (frmEmployeeFunctionsSubform) and next to each combo box (cboArea) I want to have a text box that says if the employee completed all the requirements for the functional area it will show "completed" and if not then "Not Completed". I also need to have the combo box (cboArea) hide values that I have already chosen. For instance if I had chosen Supply Operator I dont want it in the list to choose again.

Thanks,
SoggyCashew.....

 

[dhookom]

I would consider creating a user-defined function that accepts the employee and area and returns true or false for completed.

Apparently you don't want a bound combo box to include its value in the dropdown. This is no small task. Since there are so few, I would add some type of indicator to the displayed value and then notify the user if this was a duplicate.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[oxicottin]

Duane, can you give me an example? Thanks!

Thanks,
SoggyCashew.....

 

[dhookom]

Your code would use a recordset or DCount() to find the number of requirements for an area and also for the number of requirements the employee had completed in that area. Compare the two numbers and return true or false.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[oxicottin]

Andrzejek, I'm not looking for anything to do with a required field, what I want to do is in my subform (frmEmployeeFunctionsSubform) there is a unbound textbox named (txtFunctionalAreaAchieved)and I just want to display whether the requirements associated with the Functional Area are completed or not completed and I cant figure it out.

Thanks,
SoggyCashew.....

 

[dhookom]

Oxicottin,
Did you attempt to create either of the DCount()s to determine the number of requirements in a function or the number of requirements completed based on employee and function?

Is there a good reason why you don't have a RequirementID? I wouldn't want to store the full requirement text in tblRequirements which is actually the EmployeeRequirements table?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[dhookom]

I wouldn't move any further until you:
[ul]
[li]Changed the name of tblRequirements to tblEmployeeRequirements[/li]
[li]Stored the FuncReqID value in tblRequirements/tblEmployeeRequirements rather than the long text Requirement field[/li]
[li]Created a unique index on EmpFuncID and funcReqID in tblRequirements/tblEmployeeRequirements[/li]
[li]Identified if two different areas could have the same requirement[/li]
[/ul]

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[dhookom]

This query should give you the total number of completed and total requirements by Employee and Function (assuming you removed duplicates):

SELECT tblEmployeeFunctions.EmpID, tblEmployeeFunctions.FuncID, Sum(IsNull([tblRequirements].[DateCompleted])+1) AS Expr1, 
   Count(tblRequirements.RequirementID) AS CountOfRequirementID
FROM (tblFunctionalAreas INNER JOIN tblEmployeeFunctions ON tblFunctionalAreas.FuncID = tblEmployeeFunctions.FuncID)
    INNER JOIN tblRequirements ON (tblFunctionalAreas.FuncID = tblRequirements.FuncID) AND (tblEmployeeFunctions.EmpFuncID = tblRequirements.EmpFuncID)
GROUP BY tblEmployeeFunctions.EmpID, tblEmployeeFunctions.FuncID;

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[oxicottin]

Ok Im a weekend warrior and only do this for work projects to make my life easier so please bare with me. Ok, so I changed the table name and deleted Requirements and added FuncReqID field to the new named table tblEmployeeRequirements then I went into frmEmployeeFunctionsSubform and the cboArea text boxes after update event I changed to:

' update record in tblEmployeeRequirements with EmpFuncID
       strSQL = "UPDATE tblEmployeeRequirements " & _
    "SET EmpFuncID = '" & Me.EmpFuncID & "' " & _
    "WHERE EmpFuncID IS NULL;"
  CurrentDb.Execute strSQL, dbFailOnError

Now when I select something from the Functional Area text box I get an error

Enter Parameter Value
tblEmployeeRequirements.Requirement



Thanks,
SoggyCashew.....

 

[oxicottin]

Ok figured out the error, Its because I got rid of the field Requirements in the table tblRequirements/tblEmployeeRequirements and am now saving the FuncReqID value instead. How do I display the Requirements now in the frmRequirementsSubform? Its record source was the tblEmployeeRequirements which included the saved text for Requirements now I can only display a number in the text box.

Thanks,
SoggyCashew.....

 

[oxicottin]

Duane, attached is the current version DB.
[ul]
[li]I updated the table name to tblEmployeeRequirements[/li]
[li]I'm now storing the FuncReqID value in tblEmployeeRequirements instead of all that Requirements text.[/li]
[li]Removed requirements from the tblEmployeeRequirements table since im not storing the text.[/li]
[li]Changed VBA text in the after update offrmEmployeeFunctionsSubform cboArea to reflect new table name[/li]
[li]Added FuncReqID in the qryAllTasks and removed Requirements.[/li]
[li]Created a query from SQL you gave called qryCompletedUncompledtedFunctions but cant figure out how to get lets say "NumOfReqCompleted" to show as a DLookup in frmEmployeeFunctionsSubform txtFunctionalAreaAchieved.[/li]
[/ul]

Thanks,
SoggyCashew.....

 

[dhookom]

Try SQL of:

SELECT tblEmployeeFunctions.EmpID, tblEmployeeFunctions.FuncID, tblFunctionalAreas.Functions, 
Sum(IsNull([tblEmployeeRequirements].[DateCompleted])+1) AS Completed, Sum(1) AS TotalRequirements
FROM tblFunctionalAreas RIGHT JOIN (tblEmployeeFunctions INNER JOIN 
   tblEmployeeRequirements ON tblEmployeeFunctions.EmpFuncID = tblEmployeeRequirements.EmpFuncID) 
   ON tblFunctionalAreas.FuncID = tblEmployeeFunctions.FuncID
GROUP BY tblEmployeeFunctions.EmpID, tblEmployeeFunctions.FuncID, tblFunctionalAreas.Functions;

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[oxicottin]

Duane, I forgot to mention in my last post when I removed Requirements text from being saved and saved the FuncReqID instead, how do I get the text to show in the requirements text box now?

Also, what the SQL for? I tried to put in a query and look at it in Design View but I get errors. By lookng at it it appears its from the old DB using requirements ect?

Thanks,
SoggyCashew.....

 

[dhookom]

Add the table with the requirements field to your form's record source:

SELECT tblEmployeeRequirements.RequirementID, tblEmployeeRequirements.EmpFuncID, tblEmployeeRequirements.FuncID, 
tblEmployeeRequirements.FuncReqID, tblEmployeeRequirements.DateCompleted, tblFunctionRequirements.Requirements
FROM tblEmployeeRequirements INNER JOIN tblFunctionRequirements ON tblEmployeeRequirements.FuncReqID = tblFunctionRequirements.FuncReqID;

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[dhookom]

I would also change the requirement text control to both Locked and Disabled.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[oxicottin]

Duane,
[ul]
[li]I fixed the locked and disabled text box.[/li]
[li]I figured out a work around on how to being only able to select one function Area and it not show up in the selection drop down again.[/li]
[/ul]

I cant figure out for the life of me how to get it to say or even have a check next to frmEmployeeFunctionsSubform on the row that the function area is completed or has all its requirements.

Thanks,
SoggyCashew.....

 

[dhookom]

Change your frmEmployeeFunctionsSubform record source to:

SELECT tblEmployeeFunctions.EmpFuncID, tblEmployeeFunctions.EmpID, tblEmployeeFunctions.FuncID, tblEmployeeFunctions.DateCompleted, 
DCount("DateCompleted","tblEmployeeRequirements","EmpFuncID=" & [EmpFuncID]) & " of " & DCount("*","tblFunctionRequirements","FuncID =" & [FuncID]) AS CompOfReq
FROM tblEmployeeFunctions;

Then set the txtFunctionalAreaAchieved Control Source to: CompOfReq


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[oxicottin]

Duane that works perfectly! Two Questions, in the frmEmployeeFunctionsSubform there is a "Date Completed" text box. How can I automatically have it enter todays date when txtFunctionalAreaAchieved text box reaches lets say 6 of 6 or whatever requirement.

Second, When I mack a selection change over an existing Function area in the frmEmployeeFunctionsSubform it doesn't delete the old and just ads the new requirements to the table tblEmployeeRequirements. Here is my before update of the combo box.

Private Sub cboArea_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblEmployeeFunctions", "[FuncID]=" & Me![cboArea] & _
          " AND [EmpID]=" & Me.Parent![cboEmployee]) > 0 Then
  MsgBox "You can not Duplicate a Function. Choose a different Function to proceed!", vbCritical
    Cancel = True
    Undo
    End If
End Sub

 

[dhookom]

Every form has an after update event. You can use the DCount() expressions I provided to determine if the counts match and if so, update the field. Why do you think you need to store this date when you can count the records and return the highest date from the tblEmployeeRequirements table where the employee and function match.

You shouldn't allow writing over an existing function area.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[oxicottin]

I'm not sure what you mean "You shouldn't allow writing over an existing function area."

What I'm thinking your saying is I should suggest the user to delete the record if they want to change Functional Areas, which is what I want to do. The issue is in my Before Update I tell them there is already a functional Area with that name can I include a delete query that will delete the record and let the user decide yes or no if they want to?

As for the DCount() expressions, I'm learning them and messing with them Ill keep tying different expressions and get back to you....

Thanks,
SoggyCashew.....