Thread: [SOLVED] Speed this up at all?
View Single Post
Old 07-18-2017, 01:34 AM   #10
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 1,447
Thanks: 178
Thanked 202 Times in 188 Posts
Gasman will become famous soon enough
Re: Speed this up at all?

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..

CJ_London: I also indesed the start and end dates in the tblHolidayDates.

The _Doc_Man: 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.

static: It was hard enough for me to modify for the holidays, so will leave the images as they are for now.

Galaxiom: It is my function that was slow. Query for the rest of the data appears to be fine.

Thanks everyone.

Code:
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
    'rst.MoveFirst
    'iHolidaysUsed = 0
   ' Do While Not rst.EOF
        'iHolidaysUsed = iHolidaysUsed + rst!HolsBooked
       ' rst.MoveNext
    'Loop
'End If

NewHolidaysRemaining = iHolidaysAllowed - iHolidaysUsed

ExitFunction:
    Set db = Nothing
    Set rst = Nothing

Err_Exit:
    Exit Function
    
Err_Handler:
    MsgBox "Error " & Err.Number & " " & Err.Description
    Resume ExitFunction

End Function
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote