Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-04-2016, 02:34 AM   #1
wrightyrx7
Newly Registered User
 
Join Date: Sep 2014
Posts: 104
Thanks: 7
Thanked 0 Times in 0 Posts
wrightyrx7 is on a distinguished road
Table data in subform slow

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

wrightyrx7 is offline   Reply With Quote
Old 05-04-2016, 02:55 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,394
Thanks: 40
Thanked 3,687 Times in 3,551 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Table data in subform slow

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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 05-08-2016, 09:37 PM   #3
yupstrips
Newly Registered User
 
Join Date: Mar 2016
Posts: 9
Thanks: 0
Thanked 1 Time in 1 Post
yupstrips is on a distinguished road
Re: Table data in subform slow

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

yupstrips is offline   Reply With Quote
Old 05-09-2016, 12:13 AM   #4
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,766
Thanks: 118
Thanked 1,546 Times in 1,451 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Table data in subform slow

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.

Galaxiom is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Subform Selection Slow gunslingor Forms 14 05-17-2018 12:08 AM
Passing data from main form to subform not saving data to table weilerdo Forms 0 03-02-2006 03:46 AM
subform very slow ice-9 Forms 0 11-08-2005 08:23 AM
Subform slow to update AmyB Forms 2 07-27-2005 07:22 AM
Slow subform Pauldohert General 2 06-03-2005 01:21 AM




All times are GMT -8. The time now is 07:53 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World