locked
How to open form fast without data RRS feed

  • Question

  • I have several forms that take a long time to open because they show all records if no filters are selected.  But users will most likely filter it with one of the several filters I have built on the form.

    1) Split Forms: What is the best way to have split forms open fast, showing no records, until data is entered into one of the filters (combo boxes)?

    2) Unbound Form with Subform:  Same question.

    I sometimes solve this issue by putting a default in one of the combo filters, say "Select a store." in a combo filter.  I have also solved this by making the sub unbound with its source only being added once some action or filter takes place.  But both of these methods are a bit awkward.  What other ideas are there to do this?

    Thanks,

    Matt

    Thursday, July 7, 2011 4:21 PM

Answers

  • Try opening the form with code like this:

    Docmd.OpenForm "frmCustomers",,,"id = 0"


    It not clear if you application is split and using a network – if it is, then you probably want to test/try a persistent connection trick. This trick only applies to when you using a network, and is included in the following list of tips to check out that may suggest as to why your form is loading slow.

    http://www.granite.ab.ca/access/performancefaq.htm

    I load up forms attached to tables with 200,000 records and the form loads in well under one second if you restrict the records being loaded. So, perhaps you have combo boxes, sub forms or something else dragging a lot of records, but the long delay you mention is not typical and suggests some other issue or detail that we here do not know about.

    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada

    • Proposed as answer by Macy Dong Thursday, July 21, 2011 6:25 AM
    • Marked as answer by Macy Dong Thursday, July 21, 2011 6:25 AM
    Monday, July 11, 2011 6:58 AM
  • <Bill,>
    <I just tried setting the default value to one of the filters in the header to speed up loading.>

    <And it doesn't work.  It ignores the value entered and loads all records, even though the default value is clearly visible.>

    ?Have you allowed filtering? On the Forms property sheet select Filter On Load and Allow Filters. Put your filter expression here. I have had some luck with that.

    • Marked as answer by Macy Dong Thursday, July 21, 2011 6:25 AM
    Monday, July 11, 2011 1:25 PM

All replies

  • Matt

    Actually, your methods are pretty much the standard. Not loading the recordsource of the main form until a store is selected will make sure a filter is always applied.


    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals
    Thursday, July 7, 2011 4:52 PM
  • Hi Bill,

    Can you expound on this for those of us not in the know?

    Thursday, July 7, 2011 6:11 PM
  • Bill,
    I just tried setting the default value to one of the filters in the header to speed up loading.  And it doesn't work.  It ignores the value entered and loads all records, even though the default value is clearly visible.

    My guess is that this is happening because it is a SPLIT form and the sequence of events are not like the form/subforms I have used this trick on before.

    Any ideas what to do?

    I don't want to play around with the record source because that opens a can of worms with other things on the form.

    I tried setting the value for the filters with OnOpen and OnLoad events and the same thing happens.  Requerying seems to defeat the purpose of speeding it up.

    Thanks,

    Matt

    Thursday, July 7, 2011 6:35 PM
  • hi Matt,

    I was having a similar problem.

    It turns out I had conflicting codes.

    On Open I set filter to Last record.

    On Load I set filter to First record.

    One was a macro the other vba. Now the form loads for multi users in a couple of seconds instead of minutes.

    Thursday, July 7, 2011 9:16 PM
  • I put this:

    DoCmd.GoToRecord , , acFirst

    In the OnOpen event of the form.  It takes 10 seconds to open with it and 14 without it.  I see all the records when it is opened, because it is a split form.  This is good because it doesn't confuse the user.  But it is still a bit slow.  I'm not sure what will happen when the data grows.

    The code also stops the conditional format blinking.  Which is great.

    But what happens if there are no records?  Will that code give me an error?  How should it be edited for errors?

    Thanks,

    Matt

     

    Friday, July 8, 2011 4:17 PM
  • Try opening the form with code like this:

    Docmd.OpenForm "frmCustomers",,,"id = 0"


    It not clear if you application is split and using a network – if it is, then you probably want to test/try a persistent connection trick. This trick only applies to when you using a network, and is included in the following list of tips to check out that may suggest as to why your form is loading slow.

    http://www.granite.ab.ca/access/performancefaq.htm

    I load up forms attached to tables with 200,000 records and the form loads in well under one second if you restrict the records being loaded. So, perhaps you have combo boxes, sub forms or something else dragging a lot of records, but the long delay you mention is not typical and suggests some other issue or detail that we here do not know about.

    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada

    • Proposed as answer by Macy Dong Thursday, July 21, 2011 6:25 AM
    • Marked as answer by Macy Dong Thursday, July 21, 2011 6:25 AM
    Monday, July 11, 2011 6:58 AM
  • <Bill,>
    <I just tried setting the default value to one of the filters in the header to speed up loading.>

    <And it doesn't work.  It ignores the value entered and loads all records, even though the default value is clearly visible.>

    ?Have you allowed filtering? On the Forms property sheet select Filter On Load and Allow Filters. Put your filter expression here. I have had some luck with that.

    • Marked as answer by Macy Dong Thursday, July 21, 2011 6:25 AM
    Monday, July 11, 2011 1:25 PM