Originally Posted by
pbaldy
I would always lean towards a query based solution rather than a code based solution, much as I like coding. How do you determine each of those criteria? Presuming the data is in tables, I'd think there would be a way to query it out. Perhaps that would end up with active employees with a site, so each site could query against that with a criteria for site.
I do have a table where it stores the employee ID number, temporary work location, start and end dates.
The module CheckInactive is basically running the following query for me and creating a record set:
Code:
SELECT tblEmployeeAssignments.EmpID, tblEmployeeAssignments.StartDate, tblEmployeeAssignments.EndDate
FROM ((tblEmployeeAssignments INNER JOIN tblOffices ON tblEmployeeAssignments.OfficeID = tblOffices.ID) INNER JOIN tblEmployee ON tblEmployeeAssignments.EmpID = tblEmployee.EmpID) INNER JOIN tblOffices AS tblOffices_1 ON tblEmployee.Office = tblOffices_1.ID
WHERE (((tblEmployeeAssignments.StartDate)<=Format(Now(),"Short Date")) AND ((tblEmployeeAssignments.EndDate)>=Format(Now(),"Short Date")) AND ((IIf([tblOffices_1].[SiteID]<>[tblOffices].[SiteID],-1,0))=-1) AND ((IIf(FindSiteID()<>[tblOffices].[SiteID],-1,0))=-1))
Followed by:
Code:
CurrentDb.Execute "UPDATE tblEmployee SET Active=0 WHERE EmpID=" & rs!EmpID & ";"
Not eloquent at all, but it seems to be working. I am not great with SQL, so using VBA with the record set is a bit of a work around for me!
But that's why I'm asking!!! Like I said, it seems like doing it this way is very resource intensive.