Understanding form loading on Open
in [Form]
|
From: QB on 22 Feb 2010 10:46 1. If I understand properly. For a single form that is set to use as its Record Source a table, it must load all the records in the table before displaying the form. 2. What about form with subforms? Do the subforms do the same or do they only load the data pertaining to the current link child master record? I am interested in trying to optimize a form that has 20+ subforms and is taking 10-20 seconds or so to load. I have read that I should remove the default record source and populate the record source based on my lookup field so it only has to access one record at a time. Does this make sense? What else should I be trying to do to improve performance? Thank you, QB
From: Dorian on 22 Feb 2010 11:04 You should definitely not have your main form load all the records in a table. I'd have a search form upfront that takes search criteria and displays a list of records matching the criteria. Then a button to show the details of an individual record and on that details form some navigation buttons tro scroll thru the found records one at a time. If you have 20 subforms on a form, it seems excessive. Are all the subforms relevant in all cases to every record on the form? -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "QB" wrote: > 1. If I understand properly. For a single form that is set to use as its > Record Source a table, it must load all the records in the table before > displaying the form. > > 2. What about form with subforms? Do the subforms do the same or do they > only load the data pertaining to the current link child master record? > > I am interested in trying to optimize a form that has 20+ subforms and is > taking 10-20 seconds or so to load. I have read that I should remove the > default record source and populate the record source based on my lookup field > so it only has to access one record at a time. Does this make sense? What > else should I be trying to do to improve performance? > > Thank you, > > QB
From: QB on 22 Feb 2010 11:33 In this case, yes, there is a need for all the sub-form to get the full picture for various tasks. Thank you for confirming the proper approach! QB "Dorian" wrote: > You should definitely not have your main form load all the records in a table. > I'd have a search form upfront that takes search criteria and displays a > list of records matching the criteria. Then a button to show the details of > an individual record and on that details form some navigation buttons tro > scroll thru the found records one at a time. > If you have 20 subforms on a form, it seems excessive. Are all the subforms > relevant in all cases to every record on the form? > -- Dorian > "Give someone a fish and they eat for a day; teach someone to fish and they > eat for a lifetime". > > > "QB" wrote: > > > 1. If I understand properly. For a single form that is set to use as its > > Record Source a table, it must load all the records in the table before > > displaying the form. > > > > 2. What about form with subforms? Do the subforms do the same or do they > > only load the data pertaining to the current link child master record? > > > > I am interested in trying to optimize a form that has 20+ subforms and is > > taking 10-20 seconds or so to load. I have read that I should remove the > > default record source and populate the record source based on my lookup field > > so it only has to access one record at a time. Does this make sense? What > > else should I be trying to do to improve performance? > > > > Thank you, > > > > QB
From: Albert D. Kallal on 22 Feb 2010 13:04 "QB" <QB(a)dicussions.microsoft.com> wrote in message news:487B4D9C-978F-4616-B667-FCE50D0660D4(a)microsoft.com... > 1. If I understand properly. For a single form that is set to use as its > Record Source a table, it must load all the records in the table before > displaying the form. > No, the above is incorrect, and is even incorrect for when using sql server. so, if you launch the form with an where clause (or a filter), and on your cheap-o office network the table has 500,000 records, and you pass the where clause with one ID, then ONLY the one record is pulled down the network wire. So docmd.Openform "frmCustomer",,,"id = 34642" This means you can safely use bound forms to the large table (or query - makes no difference performance wise) as you have now. So, in the above example the above from customer is bound, but ONLY the one record comes down the network connection. this is the cause for a file share or when using odbc to sql server. > 2. What about form with subforms? Do the subforms do the same or do they > only load the data pertaining to the current link child master record? Only the correct records pertaining to the current parent ID will come down the wire. > I have read that I should remove the > default record source and populate the record source based on my lookup > field > so it only has to access one record at a time. Does this make sense? No, it does not make sense, and it is the wrong advice. What you do is place the sub-forms behind tab controls and simply don't load the sub-form at all until the tab is clicked on. That way, if you have 1, or 2000 sub-forms the form load time will be the same. In this case, you will set the source object of the sub-form control to the form you want to view. Thus, no records for any sub-form not being viewed will not be transferred. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal(a)msn.com
From: QB on 22 Feb 2010 15:18
I understand your previous post, and thank you for explicitly explaining certain aspects for me! This brings up a question, probably a bad approach on my behalp. Right now, when the user click on the button to open the form, it open the form and then goes to a new record. So in fact, I do not filter to a new record (If I understand correctly, with my current method it is loading all the records, then goinf to a new one). Since this is not the right approach, how could I load the form on a new record without loading all the records? I can't filter for a non-existant rec id in this case. Also, assuming I open the form as you mentioned using a WHERE clause, how then do I allow the user to move to another record using a cbo to make his selection from? Do you close the form and reopen it with the new WHERE clause or is there a simpler approach that I am missing? Thank you for enlightening me.... again! And thank you for your time. QB "Albert D. Kallal" wrote: > "QB" <QB(a)dicussions.microsoft.com> wrote in message > news:487B4D9C-978F-4616-B667-FCE50D0660D4(a)microsoft.com... > > 1. If I understand properly. For a single form that is set to use as its > > Record Source a table, it must load all the records in the table before > > displaying the form. > > > > No, the above is incorrect, and is even incorrect for when using sql server. > > so, if you launch the form with an where clause (or a filter), and on your > cheap-o office network the table has 500,000 records, and you pass the where > clause with one ID, then ONLY the one record is pulled down the network > wire. > > So > > docmd.Openform "frmCustomer",,,"id = 34642" > > This means you can safely use bound forms to the large table (or query - > makes no difference performance wise) as you have now. So, in the above > example the above from customer is bound, but ONLY the one record comes down > the network connection. this is the cause for a file share or when using > odbc to sql server. > > > > 2. What about form with subforms? Do the subforms do the same or do they > > only load the data pertaining to the current link child master record? > > Only the correct records pertaining to the current parent ID will come down > the wire. > > > I have read that I should remove the > > default record source and populate the record source based on my lookup > > field > > so it only has to access one record at a time. Does this make sense? > > No, it does not make sense, and it is the wrong advice. > > What you do is place the sub-forms behind tab controls and simply don't load > the sub-form at all until the tab is clicked on. That way, if you have 1, or > 2000 sub-forms the form load time will be the same. In this case, you will > set the source object of the sub-form control to the form you want to view. > Thus, no records for any sub-form not being viewed will not be transferred. > > -- > Albert D. Kallal (Access MVP) > Edmonton, Alberta Canada > pleaseNOOSpamKallal(a)msn.com > |