I've amended the code to calculate the holidays to a simple DSum instead of the recordset. I've left everything in so you can see what I was originally using.
I also tried using a date, rather than Year of a date, but that seemed slower.
That has at least made it a little faster to display the holidays remaining.
I am not sure if I even have to recalculate each time I move dates, as the names remain static and I would only really need to calculate after holidays have been added/deleted or amended, but will have to take some time getting to understand it again.
For now it is sufficient for my needs..
: I also indesed the start and end dates in the tblHolidayDates.
: Just that there is a noticable pause before the calculated days are displayed, when the images are pretty much instant, but they come from a query on a single table.
: It was hard enough for me to modify for the holidays, so will leave the images as they are for now.
: It is my function that was slow. Query for the rest of the data appears to be fine.
Public Function NewHolidaysRemaining(pEmployeeID As Long, pDate As Date)
On Error GoTo Err_Handler
Dim iHolidaysAllowed As Integer, iHolidaysUsed As Integer
Dim strSQL As String, strCriteria As String
Dim db As Database
Dim rst As Recordset
'Dim dtStart As Date, dtEnd As Date
Set db = CurrentDb()
iHolidaysAllowed = Nz(DLookup("AllowedHolidays", "tblEmployees", "[EmployeeID]=" & pEmployeeID), 0)
'iHolidaysUsed = Nz(DSum("Holidays", "tblHolidayDates", "[EmployeeID]=" & pEmployeeID & " AND [HolidayType]=5"), 0)
'dtStart = DateSerial(Year(pDate), 1, 1)
'dtEnd = DateSerial(Year(pDate), 12, 31)
strCriteria = "EmployeeID = " & pEmployeeID & " AND YEAR([StartDate])= " & Year(pDate) & " AND YEAR([EndDate])= " & Year(pDate)
'strCriteria = "EmployeeID = " & pEmployeeID & " AND [StartDate]>= #" & dtStart & "# AND [EndDate]<= #" & dtEnd & "#"
iHolidaysUsed = Nz(DSum("Holidays", "tblHolidaydates", strCriteria), 0)
'strSQL = "SELECT Workdays([StartDate],[Enddate],'tblPublicHols','HolidayDate') AS HolsBooked, tblHolidayDates.EmployeeID, tblHolidayDates.StartDate, tblHolidayDates.EndDate "
'strSQL = strSQL & " FROM tblHolidayDates"
'strSQL = strSQL & " WHERE (((Year([StartDate]))=" & Year(pDate) & ") AND ((Year([EndDate]))=" & Year(pDate) & ")"
'strSQL = strSQL & " GROUP BY tblHolidayDates.EmployeeID"
'strSQL = strSQL & " HAVING ((tblHolidayDates.EmployeeID)= " & pEmployeeID & "))"
'Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
'If Not rst.EOF Then
'iHolidaysUsed = 0
' Do While Not rst.EOF
'iHolidaysUsed = iHolidaysUsed + rst!HolsBooked
NewHolidaysRemaining = iHolidaysAllowed - iHolidaysUsed
Set db = Nothing
Set rst = Nothing
MsgBox "Error " & Err.Number & " " & Err.Description