Slow Load Time

[JohnD]

Hello all,
I have a continuous form that is slow on loading a portion of the code. The form is designed to pull students who need to be either warned or terminated based off of their GPA. Below is the code that is delayed in being displayed on the form:

Public Function warnOrTerminate(creditAttempt As Integer, GPA As Double)
' these numbers are from the "satisfactory progress" portion of the catalog

'warning if the following is true
'GPA<1.5     and ([cAttempt]<=15)
'GPA<1.9     and ([cAttempt]>=16 and [cAttempt]<=30)
'GPA<2.2     and ([cAttempt]>=31 and [cAttempt]<=45)
'GPA<=2.4    and ([cAttempt]>=46)

'terminate if the following is true
'GPA<1       and ([cAttempt]<=15)
'GPA<1.5     and ([cAttempt]>=16 and [cAttempt]<=30)
'GPA<1.75    and ([cAttempt]>=31 and [cAttempt]<=45)
'GPA<=2      and ([cAttempt]>=46)

    Select Case creditAttempt
        Case Is <= 15
            If GPA < 1 Then
                warnOrTerminate = "Terminate (1.0)"
            ElseIf GPA < 1.5 Then
                warnOrTerminate = "Warning (1.5)"
            End If
            
        Case 16 To 30
            If GPA < 1.5 Then
                warnOrTerminate = "Terminate (1.5)"
            ElseIf GPA < 1.9 Then
                warnOrTerminate = "Warning (1.9)"
            End If
            
        Case 31 To 45
            If GPA < 1.75 Then
                warnOrTerminate = "Terminate (1.75)"
            ElseIf GPA < 2.2 Then
                warnOrTerminate = "Warning (2.2)"
            End If
            
        Case Is >= 46
            If GPA < 2 Then
                warnOrTerminate = "Terminate (2.0)"
            ElseIf GPA < 2.4 Then
                warnOrTerminate = "Warning (2.4)"
            End If
            
    End Select

Everything works fine - but the field that shows the end user to either warn or terminate the student shows up very delayed. Any thoughts or ideas on speeding this up??

 

[MarkK]

If your subform is linked you can sometimes dramatically improve performance by loading the subform after the mainform opens. Normally the subform open event fires first, all records are loaded into the subform, then the mainform opens, loads its records, and then the links are enforced and the subform requeries its records based on these links. This is the default behavior of linked Access subforms, and can drag on for quite a while if calculations are performed on every record of the subform.

If you load the subform after the mainform, a slow subform can be sped up significantly since links to the mainform are enforced before the data is loaded resulting in a much shorter recordset.

In mainform design view, clear the Source Object property of the subform control, and save the form. In the Form_Open() event handler of the mainform load the subform.

Me.ChildControl1.SourceObject = "frmMySubform"

Lemme know if that helps. Otherwise there must be ways to improve the performance of your 'warnOrTerminate' function. Generally, including the logic in that procedure directly into the query that drives the subform would help a lot too. SQL is very fast, whereas calling a function from a query slows things dramatically.

Cheers,

 

[jwhite]

From what you provided, it is very basic code. Add " As String" at the end of the function declaration so VBA knows explicitly how to return the value.
2. Does 'End Function' come after End Select or is there more code?

I suspect the problem isn't with this function. Where does creditAttempt and GPA come from? What code is performed before and after this function?

Do need more input.

 

[Dreamweaver]

If your subform is linked you can sometimes dramatically improve performance by loading the subform after the mainform opens. Normally the subform open event fires first, all records are loaded into the subform, then the mainform opens, loads its records, and then the links are enforced and the subform requeries its records based on these links. This is the default behavior of linked Access subforms, and can drag on for quite a while if calculations are performed on every record of the subform.

Thanks lagbolt I didn't know that And have had one form that loads data from a 1.6 million details tabel somethimes it would drage Now I know I'll load the subform with all the details on the OnOpen Of The Main Form Greate Thanks.

Mick

 

[MarkK]

Ya. It can be a drawback during development, 'cause you can't open the mainform and then drill down on the subforms because they aren't there. Also during development, if Access prompts you to save a whole bunch of objects, sometimes the result of an error you've resolved, you'll inadvertantly resave those SubformControl.SourceObject properties as containing the subform again. Go back and clear them.

Cheers,
Mark