Table data in subform slow (1 Viewer)

wrightyrx7

Registered User.
Local time
Today, 00:40
Joined
Sep 4, 2014
Messages
104
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
Code:
IIf(IsNull([Date Closed]),"Open",IIf([Date Closed]<=Date(),"Closed","Open"))

AND

Length (working days) which is color co-ordinated by values (conditional formatting).
Code:
 IIf(IsNull([Date Closed]),weekdays([Case Start Date],Date()),weekdays([Case Start Date],[Date Closed]))


Code:
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

Super Moderator
Staff member
Local time
Today, 07:40
Joined
Feb 19, 2013
Messages
16,553
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

New member
Local time
Today, 00:40
Joined
Mar 26, 2016
Messages
9
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

Super Moderator
Staff member
Local time
Today, 18:40
Joined
Jan 20, 2009
Messages
12,849
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

Top Bottom