Show a form for yearly assessment for employee

[scott0_1]

I'm creating a form that will list all employees at a work location and three check boxes next to their name. I want to have a combobox where the user can select the assessment year and then be able to edit the three checkboxes based on that year. Here's how I'm set up now:

tblAssessments
ID - Primary key
EmpID - employee number
AssessmentYear - (e.g. 2018)
InitialExpectations - yes/no
MidTermReview - yes/no
FinalReview - yes/no

I have a datasheet with the following SQL statement in VBA (parameter is work location chosen by the user):

SELECT tblEmployee.EmpID, tblEmployee.[LastName] & "", "" & [FirstName] AS EmpName, tblAssessments.InitialExpectations, tblAssessments.MidTermReview, tblAssessments.FinalReview, tblOffices.Code, tblAssessments.AssessmentYear FROM tblOffices INNER JOIN (tblAssessments RIGHT JOIN tblEmployee ON tblAssessments.EmpID = tblEmployee.EmpID) ON tblOffices.ID = tblEmployee.Office WHERE (((tblOffices.Code) = " & strSearch & ")) ORDER BY tblEmployee.[LastName] & "", "" & [FirstName];

Currently, it lists ALL employees at the selected office and offers three checkboxes for the reviews.

I'm drawing a complete blank on how to limit this to the user selected assessment year and list all employees. If I add in the user selected assessment period to the WHERE statement, it then only selects employees that have matching data and no longer shows ALL employees. Picture attached:



Hopefully this makes sense as to what I'm trying to do. If not, please let me know if I can add more. Due to the nature of the DB, I cannot post any parts of it here.

Thanks for any help you can offer,

Scott

[June7]

All employees would need a record for every assessment year in tblAssessments.

Can provide copy of db with confidential data removed.

[scott0_1]

Like I said, it would be very difficult to remove all the confidential info as it's not just table data, but also VBA and even some labels on many forms. I realize that can make it harder to help diagnose, so I appreciate any help you can give, even if just in theory, not specifics.

June7, I feared that may have to be the solution. Is there an easy way on the combobox_click event to check to see if that assessment period exists for employees, and if not, then create a record for all employees? It would have to do this every time in case an employee is added midway through the assessment period.

[June7]

Can use DLookup() domain aggregate function to verify if records exist before running code.

If IsNull(DLookup("AssessmentYear", "tblAssessments", "AssessmentYear=" & Me.cbxYear)) Then
'code to add records
CurrentDb.Execute "INSERT INTO tblAssessments(EmpID, AssessmentYear) SELECT EmpID, " & Me.cbxYear & " AS Yr FROM tblEmployee"
End If

Now if you need to create records for missing employees for past years already in tblAssessments, that is another issue.

Set the EmpID and AssessmentYear as compound unique index to prevent duplicate pairs in tblAssessments. Then running the above INSERT action will not create duplicates.

If you want to check for a specific employee then the DLookup would need additional search criteria.

[scott0_1]

If IsNull(DLookup("AssessmentYear", "tblAssessments", "AssessmentYear=" & Me.cbxYear)) Then
'code to add records
CurrentDb.Execute "INSERT INTO tblAssessments(EmpID, AssessmentYear) SELECT EmpID, " & Me.cbxYear & " AS Yr FROM tblEmployee"
End If

I just read and edited what I wrote... I need to read not skim. If I run this code on the cbxYear_Click event, will it account for those employees added midyear? And with the compound unique index, will it generate an error on duplicates every time? Can I suppress the error if so?

I just ran the code above and it's perfect if no entries for that year exist. If even one entry for the year exists, it will not add entries for other employees. So if I add a new employee after generating the new records, it will not add a new record for that employee in tblAssessments. It's getting closer though!

Set the EmpID and AssessmentYear as compound unique index to prevent duplicate pairs in tblAssessments. Then running the above INSERT action will not create duplicates.

I did not know how to do this. This is awesome!

Now if you need to create records for missing employees for past years already in tblAssessments, that is another issue.

Not an issue. This is only going forward. Thankfully!

Thanks for the help.. This is getting me closer to getting this project done!

Scott

[June7]

Code will pull every emp regardless of when added unless you include filter criteria in the SQL.

CurrentDb.Execute will not trigger error messages. Duplicates just won't append if compound index set

[scott0_1]

Code will pull every emp regardless of when added unless you include filter criteria in the SQL.

I had one record with AssessmentYear=2019 in the table. When I ran the exact SQL code above in the cbxYear_Click event, it did not add any records for 2019. Since DLookup found 2019, IsNull is false. I tried for 2017 and it added all employees (~1125) from tblEmployees perfectly as IsNull(DLookup) was true because there wasn't any 2017 records.

Here's what I've got now:

If DCount("EmpID", "tblAssessments", "AssessmentYear=" & cbxYear) < DCount("EmpID", "tblEmployees") Then

     'Check to see if records for ALL employees can be added:
     If IsNull(DLookup("AssessmentYear", "tblAssessments", "AssessmentYear=" & Me.cbxYear)) Then
          'code to add records
          CurrentDb.Execute "INSERT INTO tblAssessments(EmpID, AssessmentYear) SELECT EmpID, " & Me.cbxYear & " AS Yr FROM tblEmployee"
          Exit Sub
     Else
          'Some records for selected year exists. Add records for those missing:
          'THIS IS WHAT I NEED NOW FOR IT TO WORK!
     End If 
End If

I'm wondering if I'm going to have to create a ADODB Recordset instead, search each record for EmpID and AssessmentYear and insert/update as necessary??? I hate working with that code, so I'd rather not if I don't have to!

SO CLOSE!

[ssanfu]

How to set a compound index:

1) open the table in design view
2) In the Menu, click on the yellow "Table Tools"
3) Click on the INDEX icon in the ribbon
4) Enter an Index Name (I used EmpYear)
5) Select the fields using the combo box in the "Field Name" column
6 Select the index name (EmpYear in this case), then change the value in the "Unique" property to YES
7) Save and close





Then you should only need 1 line of code

CurrentDb.Execute "INSERT INTO tblAssessments(EmpID, AssessmentYear) SELECT EmpID, " & Me.cbxYear & " AS Yr FROM tblEmployee"

[June7]

You don't really even have to test if records exist. That was suggested for case of no compound index.

[scott0_1]

If DCount("PRI", "tblAssessments", "EndDate=" & strYear) < DCount("EmpID", "tblEmployee") Then
        CurrentDb.Execute "INSERT INTO tblAssessments(EmpID, EndDate) SELECT EmpID, " & strYear & " AS Yr FROM tblEmployee"
End If

I kept in the if statement so it doesn't try to run the INSERT statement every time as it will only be occasionally that new employees are added.

Thanks for all of the help! Between the compound index and the SQL, you saved my day! (compound index is my new favourite thing!)