Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2006

    Exclamation Unanswered: slow form load up due to repeating code

    Hi guys,

    i have an DB which is under development at the moment. I have a few forms designed for the db & they seem to work well.
    HOWEVER, when i tried to split the db into fe/be the form load up became ridiculously slow. i found the code which was causing the problem & the code does look up a complex query & calculate an award budget (using tlookup functions - equivalent to vlookup) but it didn't seem to be overly complicated.
    I then tried to replace the tlookup functions and use an ado recordset method to extract data from the tables but this didn't speed things up either.
    I then added breakpoints into the on open event of all forms (inc subforms) to see what was hapening. It turns out that the calculation code is repeated 5 times at various points inthe forms open process and i can't figure out why.

    i have been through most of the fe/be optimisation hints & tips that you guys have given me previously but these haven't helped. i'm assuming that the problem will be because of the code being repeated.

    I have attached the db to this msg. the db will need to be split as it is currently one file.
    The form in question is the 'frmStudent' form. The subform that contains the budget calculation code is 'frmNominations'. The code is in the 'on Current' event within this subform and is called 'GetBudgetSetBalance'. this is the code that gets repeated 5-6 times when the forms load up.

    I have commented out the original tlookup function method that i was using in this sub. you will find the beginnings of a ado recordset method but i have only put in one calculation but i need all 4 calculations that you can see in the tlookup version of the code.

    the idea behind the calculations is to show the full budget of an individual award & the current balance plus show the account code and the account budget for the award. I need this to update with each record.

    I know this is asking a lot but could someone havea look at the code and see what i'm doing wrong? do i need to order the form load up sequence some how? Is there a better way of coding what i want to do or a better way of optimising it?

    Any help would be really appreciated and useful for me with this & other projects.


    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2007
    Adelaide, South Australia
    Without looking at your database (lack of time atm) ... by the sound of it, what I would do is run code in the Form Load event procedure (happens once!) of the start-up form, storing the results of the calculation in a local table. Then use that local table to do your lookups in your On Current procedure.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Dec 2004
    Madison, WI
    I wasn't able to see the data in the tables (none attached) which made it a tad difficult but from the looks of it, tlookup was created to try and optimize the dlookup function and make the tlookup function callable with parameters. Offhand, without going through the entire process, I'm guessing as you say, this is slow. I personally try to avoid using any kind of dlookup process on large tables and I'm guessing as the table-size grows, this process is becoming as you say, a bit slow. If you're trying to replace the tlookup function with some ADO code, here's hopefully some ADO code which you may be able to utilize (but see my next post if this is difficult to understand):
    Dim rs as adodb.recordset
    set rs = new adodb.recordset
    Dim MyVariable as string <- if a string comparision
    Dim MyVariable as integer <- if an integer comparision
    MyVariable = somevalue <- what value you're trying to match against for FieldNameX in the strSQL below.
    dim strSQL as string
    strSQL = "Select MyFieldName from MyTableName where FieldNameX = ' " & MyVariable & " ' " < - for a string type match on FieldNameX against the variable: MyVariable (note: I put spaces between ' and " to show syntax)
    strSQL = "Select MyFieldName from MyTableName where FieldNameX = " & MyVariable & " " < - for a string type match on FieldNameX against the variable: MyVariable strSQL, currentproject.connection, adOpenKeyset, adLockReadOnly
    if rs.eof and rs.bof then
    msgbox "No matches."
    'Utilize rs!MyFieldName value as you would the return of utilizing the dlookup (or tlookup) function
    ex: me!SomeFieldName = rs!MyFieldName * SomeOtherVariable
    end if
    set rs = nothing

    This would be similar to if you were using the dlookup to find a value in a table. Instead it opens an ADO recordset and returns the value it found (rs!MyFieldName) depending on how the strSQL is constructed. In other words, you would construct your strSQL statement above to meet the same needs of what the dlookup (or tlookup) function was accomplishing.

    See next post...
    Last edited by pkstormy; 12-14-07 at 16:35.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Dec 2004
    Madison, WI
    Another way to put it and hopefully more understandable...

    Dim retDlookupTypeValue As Variant
    retDlookupTypeValue = DLookup("[LastName]", "Employees", "[EmployeeID] = 1")

    could be reconstructed using ADO code like:

    Function retDlookupTypeValue(VarPassedToFunction as integer) as Variant
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Dim strSQL As String
    strSQL = "Select LastName from Employees where EmployeeID = " & varPassedToFunction & " "
    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    If rs.EOF And rs.BOF Then
    msgbox "No matching recs."
    retDLookupTypeValue = Null
    retDLookupTypeValue = rs!LastName
    End If
    Set rs = Nothing

    End Function

    You would then call the function like this...
    me!SomeFieldName = retDLookupTypeValue(1) similar to if you wanted to set me!SomeFieldName = the dlookup function (DLookup("[LastName]", "Employees", "[EmployeeID] = 1")

    I think you'll find using ADO code to open the recordset (ie. the retDlookupTypeValue function above) based on the criteria can be much faster (especially if EmployeeID is indexed and the table is large) verses utilizing a dlookup type function. Of course the key would be constructing the correct strSQL statement which upon glancing at your code, may or may not be complex. I might suggest modifying the above retDlookupTypeValue function to perhaps meet the same needs and replace the tlookup function in your code.

    I've seen code similar to the tlookup function in your code to try and help speed up the dlookup function. I usually find (as it sounds like the route you're taking), opening a recordset using ADO (or DAO) code to be much faster. Again, indexing will help tremendously on the table. I couldn't see why the code is repeated 5 times though as I couldn't see it in action with data.

    Lastly, you might also want to consider creating an unbound text box on the form somewhere and utilize the function call once and set the value in the unbound text box so you wouldn't have to call the function again (unless something is changed) IF the return needs to be repeated for some reason or another with the same criteria. (So instead of calling the function 5 times to get the same value each time with the same criteria, you call it once, setting the value in the unbound text box and then utilize that text box value in further calculations until something is changed where you need to re-populate the value of the unbound text box) Does that make sense? Throwing a few unbound text boxes on the form, setting the value of them once and then using that value, can be a great speed enhancer if done properly! Then you can set the default value of a field to the unbound text value (or use it in other calculations) without always re-calling the function for each new record. Or as I think StarTrekker is suggesting, populate a local "temp" type table on the form's load event with certain values initially and then use the much smaller "temp" type table to do further lookups on values (I apologize if I misinterpreted you StarTrekker).

    Again, I hope this helps. Please let me know if it does.
    Last edited by pkstormy; 12-14-07 at 16:38.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Sep 2006


    Thanks guys,

    i found the probelm that was causing the repeating of the code:
    if you call the form code line 'filterOn=true' and '<name>.filter="XXX"' then this will cause the code to run through the form open, load or current events again so repeating it several times.
    Not sure why but when i remove this small piece of code my form loads up in 6-8 seconds instead of 30-37 seconds!
    If the code is problematic then it should be causing a perpetual loop but doesn't seem to do this. Strange code!

    My forms are loading up in around 7 seconds which is still pretty slow. this may be acceptabel but if you have any other hints or tips for speeding up the loading of forms then please do let me know.

    thanks again,


  6. #6
    Join Date
    Dec 2004
    Madison, WI
    I personally wouldn't be satisfied with a form taking 6-8 seconds to load (I don't have the patience and neither do most of my users.) Maybe 3 seconds max.

    Are your forms opening with all the records or do they open with criteria where they only return one record? It looks like they are opening with all the records. It would make sense that your form would load slowly if opening with all the records (and even slower if applying a filter when opening the form with all the records and applying the filter.) You remove the filter command and get a performance boost but you still get a delay because it's opening all the records. That combined with any dlookup type functions or other code which goes against a large recordset when the form is loading. How many records are there when the form opens? I noticed for example on the frmStudents you're setting the me.recordsource to all the records in the form's open event verses criteria on a specific record. I will often put an unbound "search" type combobox on a form and set the recordsource of the form = to the autonumber field value on the combobox or I will have a "Search" form and when the user selects a specific record, I open the data form based on criteria of the record they selected. Without fully diving into the code, instead of setting the recordsource of the frmStudents to all the records, instead set the recordsource to some criteria and don't set the recordsource in the form's open event but put the actual select statement in the form's recordsource property (I don't really understand why me.recordsource is populated in the form's open event with what seems like all the records verses using criteria to only return 1 record.) If tblStudents is extremely large, this will cause a slow opening of the form. But putting the select statement in the form's recordsource property (verses setting it in the onOpen event) will then allow you to put a select statement with criteria in each of the subform's recordsource verses setting the recordsource in every subform's onOpen events).

    Then instead of setting each recordsource of each subform in that subform's onOpen event, put the select statement in the recordsource property on the form and put criteria where it equals the joining ID field of the main form (even though you also have the subform's link child field and link master field property set.) For example, on your frmNominations form, put the actual select statement in recordsource of this form to "Select * from tblNominations where lngFKStudentId = " & Forms!frmStudents!lngFKStudentID & "" (again, also put the select statement in the recordsource for the frmStudents instead of setting it in the form's open event.) I think you'll find this will help with your form's loading time (and shave off a few seconds).

    You also have a few subforms (and sub-subforms) in some of the main forms and all those subforms are loading with xx records when your main form is opening (since it looks like your main forms are loading with all the records.) You don't necessarily need to have the sourceobject of the subform set to the subform name (bound)! For example, you can set the sourceobject of a subform when that tab is clicked. You will again be surprised about the form load time if it doesn't have to load each subform when the main form opens. There's really no need to have the subforms loading until they click that tab (since you use a lot of tabs) and the user really doesn't see that subform until that tab is clicked (unless you have code which populates or gets values from the subform so the sourceobject needs to be set to the subform name).
    For example on your frmNominations form, remove the sourceobject of the frmStudentCourse and frmAwardOverview and add this code:
    If me!tabControlNominations = 1 then me.frmStudentCourse.sourceobject = "frmStudentCourse"
    If me!tabControlNominations = 3 then me.frmAwardOverview.sourceobject = "frmAwardOverview"
    (or you can use a select case statement)

    This will help your main forms loading time dramatically!! but again, this assumes fields on the subform's are not needed for the main form.

    Do you have any default values of any fields which utilizes some kind of dlookup (or tlookup) type function? Looking through a few fields I didn't see any but you may want to check.

    Having a lot of comboboxes on the form can also be big killers for form load time! It looks like most of your comboboxes don't have a rowsource but you're setting the rowsource in the form's load event (and each combobox in the subforms load event). If these are based on a large recordset, this will cause delays. For example - having a combobox on a lastname field on a table which has a few hundred thousand lastnames. Try putting the select statement in the rowsource in the combobox and set the query "Recordset Type" property of the rowsource query from a "Dynaset" to a "Snapshot" recordset type instead of setting the rowsource in each of the form's load event (since it looks like the comboboxes are unbound and not updating a field.) It looks like all the subforms set most of the comboboxes rowsources when each form/subform loads.

    Also, have you consider unbound forms? I know it means quite a bit of coding but it will definately be fast! (keeping in mind that comboboxes will still cause delays but your form will load quicker if it isn't opening with all the data of a large table.) I used unbound forms on tables which were in the 5+ million size (SQL Server tables) and the forms loaded within a second or two. The field values were populated once the user select the record from a "search" type combobox. When the forms were bound to the table and I opened them with all the records, they took 30+ seconds. I decreased the load time by opening the form based on 1 record but it was still 15+ seconds. Once I went to unbound forms, less than a couple of seconds. I made all the comboboxes on the form to textboxes and it was less than a second. Of course I also removed any code in the form loading event which queried against any large recordsets.

    I didn't see the actual joins between the tables in your relationships diagram (because I couldn't see the tables/fields). Maybe it's on the backend but make sure your tables have relationships between the key fields and are actually joined. This not only prevents orphaned records but will help with speed of queries. I'm assuming you have joins between all your tables on the key fields. I once had a problem in that that some tables I had in a db were joined together by 2 fields in each table instead of 1 field. Making the join between the tables to 1 field greatly helped performance. Not sure how your tables are joined on the backend.

    Also, try changing the query properties (recordset type) of some of your queries utilized in any tlookup or dlookup type functions or queries which are not editing data from a "Dynaset" recordset type (default) to a "Snapshot" recordset type. This will help with execution speed of those queries.

    Lastly, I'm not sure about your network set up but slow network cards and daisy chain hubs verses a switchbox can be a performance killer on a networked Access database. Memory on the PC can be a factor. The computers which had little free memory and opened the networked database were slower than the computers which had more memory (or a faster processor). Access databases will also open slower if the networked drive is overburdened (for example, don't have SQL Server, backups, etc.. on the same box as your mdb's.) Preferably you want your mdb's to be on a fast running network drive which doesn't have a lot of other stuff going on if accessed via the network. But the entire mdb will open slow (not just the forms) if this is the case. Just thought I'd mention it though.

    And again, indexing fields can be a tremendous performance booster and I'm assuming your table's key fields are all indexed. Especially on fields which are looked up/utilized by the tlookup/dlookup type functions.
    Last edited by pkstormy; 12-15-07 at 05:57.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Dec 2002
    Préverenges, Switzerland
    how about loading the form unbound then binding it with the timer.
    very little code
    very quick 'visual' for the user - unbound form displays instantly
    definitely only one trip to the data
    the form is bound when the dust settles.

    in form design: no recordsource; timer interval = 1

    in code:

    'formwide status flag defaults to False at declare allows "normal"
    'use of the timer as well as using it to bind the form after load
    dim amLoaded as boolean

    private sub Form_Load()
    'do anything you need to do EXCEPT stuff to do with data
    'NB the timer will not tick until this event has completed
    end sub

    private sub Form_Timer()
    if amLoaded then
    'use the timer to do whatever you normally do with the timer if
    'you are using the timer, otherwise this never happens
    'the form is newly loaded (amLoaded = false)
    'this code is running 1 millisecond after _Load() completed
    'we do not want this code to run again
    amLoaded = true
    'if you are using the timer for other stuff, set
    'the 'real' interval, otherwise zero to turn off the timer
    me.timerinterval = 0
    'add a recordsource to get the data onto your form
    screen.mouspointer = 11
    me.recordsource = "SELECT yada yada yada yada
    screen.mousepointer = 0
    'form is now bound
    end sub

    add error handling to make sure you are not left with the eggtimer cursor etc

    currently using SS 2008R2

  8. #8
    Join Date
    Sep 2006


    Hi Guys,

    thanks PKSTORMY & IZYRIDER for your help. Really appreciate it.
    I apologise for attaching the wrong copy of the application so i have attached the correct version this time round. Please note this has not been split into FE/BE so will be much quicker than when it is split.

    PKSTORMY: you mentioned a few times about the use of the recourdsource setting in the form's open event. I started to do this because another MS Access expert had suggested that removing the recordsource from the form & adding it to the code helped speed the loading process of the form. This includes the combo boxes.
    I remember trying it out on a previous project & it did seem to help ALTHOUGH on this project i haven't noticed a difference.

    I'm not sure whether it is a good thing or bad thing now. what do you think?
    I will try to implement all your suggestions and see what i get.


    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts