Table data in subform slow (1 Viewer)


Registered User.
Local time
Today, 09:53
Sep 4, 2014
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.

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

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)
    Exit Function
    Weekdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Weekdays"
    Resume Weekdays_Exit
End Function


Super Moderator
Staff member
Local time
Today, 17:53
Feb 19, 2013
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


New member
Local time
Today, 09:53
Mar 26, 2016
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


Super Moderator
Staff member
Local time
Tomorrow, 02:53
Jan 20, 2009
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.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom