Table data in subform slow

[wrightyrx7]

Hi All,

I have a huge amount of data in a subform and i am trying to speed up how quickly the data is loaded when i open the form.

Any suggestions to speed it up would be great, maybe there is nothing i can do but worth a try.

All the data is pulled from a table but there are two formula fields that i have added to the subform which are below.

Open/Closed

IIf(IsNull([Date Closed]),"Open",IIf([Date Closed]<=Date(),"Closed","Open"))

AND

Length (working days) which is color co-ordinated by values (conditional formatting).

 IIf(IsNull([Date Closed]),weekdays([Case Start Date],Date()),weekdays([Case Start Date],[Date Closed]))

Public Function Weekdays(ByRef startDate As Date, ByRef endDate As Date) As Integer
    ' Returns the number of weekdays in the period from startDate
    ' to endDate inclusive. Returns -1 if an error occurs.
    ' If your weekend days do not include Saturday and Sunday and
    ' do not total two per week in number, this function will
    ' require modification.
    
    ' The number of days inclusive.
    Dim varDays As Variant
    ' The number of weekend days.
    Dim varWeekendDays As Variant
    ' Temporary storage for datetime.
    Dim dtmX As Date

    
    On Error GoTo Weekdays_Error
    
    ' The number of weekend days per week.
    Const ncNumberOfWeekendDays As Integer = 2
    
    ' If the end date is earlier, swap the dates.
    If endDate < startDate Then
        dtmX = startDate
        startDate = endDate
        endDate = dtmX
    End If
    
    ' Calculate the number of days inclusive (+ 1 is to add back startDate).
    varDays = DateDiff(Interval:="d", _
        date1:=startDate, _
        date2:=endDate) + 1
    
    ' Calculate the number of weekend days.
    varWeekendDays = (DateDiff(Interval:="ww", _
        date1:=startDate, _
        date2:=endDate) _
        * ncNumberOfWeekendDays) _
        + IIf(DatePart(Interval:="w", _
        Date:=startDate) = vbSunday, 1, 0) _
        + IIf(DatePart(Interval:="w", _
        Date:=endDate) = vbSaturday, 1, 0)
    
    ' Calculate the number of weekdays.
    Weekdays = (varDays - varWeekendDays)
    
Weekdays_Exit:
    Exit Function
    
Weekdays_Error:
    Weekdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Weekdays"
    Resume Weekdays_Exit
End Function

 

[CJ_London]

do you need to bring everything from the table through? The weekdays function will slow things down - can you apply a criteria (not filter) to the recordsource to limit the number of rows.

Also, look to apply your two functions to the query and bind the controls to the fields rather than calculating in the form

 

[yupstrips]

have a front end ACCDB that has linked tables (also ACCDB). Of these linked tables there is one main table and ~20 ancillary data tables (not including lookup tables). The master table has the primary records with a two column primary key. The 20 ancillary tables store data

 

[Galaxiom]

Get rid of unnecessary overheads in the function. Testing for the parameters being the wrong way around is a waste of time. Using variants to store integers adds significant overheads.

Maybe you have a reason but testing for DateClosed being in the future seems odd.