# Table data in subform slow (1 Viewer)

#### wrightyrx7

##### Registered User.
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
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
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
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.