Speeding Up Combo Boxes on Forms and the loading of Forms that contain Combo

tmtony

标题:Speeding Up Combo Boxes on Forms and the loading of Forms that contain Combo Boxes. .

原作者:ATTAC Consulting Group

原文网址:http://ourworld.compuserve.com/homepages/attac-cg/AFormTip.htm#KEY

摘抄人:tmtony

翻译人:



Combo Boxes are one of the easiest way to allow the user to pick from a category of input options or to pick a specific record. However, if the combo box is filed with data such as a list of customers, products or orders, or any other data which can run into the thousands of records, or is based on a complex query, this can dramatically slow down both the time it takes to open the combo box itself, and the time it takes to open and load the Form which contains the combo box.

Form loading time can become a problem because Access runs the query and sorts the records to fill the combo box at least twice just when opening the form. If you have multiple combo boxes on your form, this can make the form seem sluggish. Here's some hints to speed up the process of form loading and opening of combo boxes with large number of records:

1. It may sound self evident but make sure that the sort and parameter fields in the underlying query are indexed fields in the tables used.

2. Limit the number of fields returned to the combo box in the underling query.

3. Leave the Row Source for the combo box blank in its properties sheet. Then add an event procedure to the "On Enter" event of the combo box. In that procedure set the combo box's row source, to the desired query or table by specifying:        Me!ComboBoxName.RowSource = "queryortablename"



This will make the form load faster by not running the combo box query until the user requests the data. (The combo box query is usually run again when the user selects it anyway.)

4. Set the "Auto Expand" property to No.

5. Limit the number of rows the combo box returns. Displaying 50 records is much faster than filling a combo box with 1000 or more records. There are many strategies to accomplish limiting the number of records that a combo box is filled with when it loads. One option is to make the combo box query dependent on, or filtered according to criteria of a text field on the same form. This is easy to do if you are using an alphabetical list such as a list of Customers.

Place a condition in the criteria field of the query which fills the combo which looks at another text control on the form for the first (and subsequent letters entered into the text box) such as:       Like [Form]![txtAlpha] & "*" (Note you don't need the formal form name in the criteria, Access will look to the current form for the field,) then in the OnChange event of the field txtAlpha enter code in an event procedure similar to "MyCombo.Requery". Another advantage to this technique is that since the query returns no rows when the form is opened, the form loads faster as well.

6. If the Bound Column of the Combo Box is a numeric column of the query, don't hide this column. (Access 2)

爱情插班生

标题:优化窗体组合框和加载含有组合框的窗体的速度

原作者:ATTAC Consulting Group

原文网址:http://ourworld.compuserve.com/homepages/attac-cg/AFormTip.htm#KEY

摘抄人:tmtony

翻译人:爱情(意译)

   用户都非常喜欢用到组合框来选择某项或某条特定纪录。 但是, 如果组合框被列满诸如顾客名单, 产品或订单, 或其他数以万计纪录的数据, 又或基于复杂的查询, 本身打开的时间加上表格的时间,运行明显减慢了组合框的速度和打开、加载窗体的速度.

   由于当窗体打开时组合框既要查询又要排序已限定的记录,如果有多个这样的组合框,这就严重的影响了窗体加载的时间, 运行起来就像老牛拉车一样慢吞吞.下面是一些优化的建议,希望对大家有用处.

1. 第一点大家都知道, 但仍要确认一下在其查询中用于排序和参数的字段为表中的已索引字段。

2. 内嵌的查询尽量限制返回到组合框的字段数量。

3. 组合框行来源属性设为空。 然后可以在其”进入”事件用代码来设置行来源所需的查询和指定的表。如: Me! 组合框名.RowSource = "查询或表名"  这样窗体加载时就不会运行组合框的查询,直至用需要户用到组合框的数据。 (通常当用户 选择组合框一次就会重新检索一遍。)

4. 用不到时,"自动展开"属性设置为否。

5. 限制组合框返回的行数。 显示50条记录比显示1000 条或更多记录要快得多。 有许多方法实现限制一个组合框加载时被载入记录的数量。(狸帮忙译一下下面的,实不知其含义.)

6.如果组合框的绑定列是查询的数字列, 不要掩藏这列。

[em01][em01][em01]

[此贴子已经被作者于2005-8-19 11:46:40编辑过]

海狸先生

5、限制组合框返回行数，在组合框中显示５０条记录远比显示１０００条或更多记录要快。这里一种选择是将组合框行来源设为一个查询，（注：查询中可以用同一窗体上某文本字段为筛选依据）。如果你要使用同类的列表，例如一个顾客列表，这将是非常容易的。 （我也是猜得很累，你参考一下吧）

chajiangliang

5.限制組合框中的行數。顯示50條記錄的速度一定比1000條的速度快好多﹐有很多方法可以在加載組合框時限制其行數. 一是設定組合框的Rowsource為查詢, 其查詢中的條件來自于窗體上的文本框。如果你的窗體上要使用有一個以字母排序的客戶清單, 那么就很容易操作。在組合框(mycombo)中Rowsource那個的查詢的設計模式的條件欄中寫入窗體上那個起限制作用的文本框﹐如ike [Form]![txtAlpha] & "*",(無需寫Forms, 因為Access會在當前窗體上找此控件的)﹐再在txtAlpha的onchange事件下使用mycombo.requery即可。此方法的另外一個優點是在窗體在加載是組合框的返回記錄是0﹐所以打開窗體一樣很快。In my opnion﹕1. not in the OnChange Event to requery, but in the AfterUpdate. 2. We'd better pass SQL sentence to Combobox.rowsource in the OnLoad Event of the Form, then no query time needed before the form opens.

duanpeng@

有意思,谢!

txdsdn

fghfghhgfh