Make your Access forms run faster
Thursday, November 20, 2008 03:19 PM
By giving a form less data to digest, you can make it run more efficiently. These two tricks can help.
Microsoft Access
Make your Access forms run faster
The less data Access has to load into memory when you open a form, the better the performance.
For example, if a form will be used solely for entering data rather than for data searches, you should change the form's data entry property so a blank record opens directly. Otherwise, Access will read in all the records in the file before it displays the blank record at the end of the record set.
Follow these steps to change the form's data entry property:
- Open the form in Design View and click the Selector button.
- In the form's property sheet, click the Data tab.
- Click in the DataEntry property text box and select Yes.
Another way you can improve performance is to use only default formatting and properties for most or all of the form controls. Your form will load faster because Access does not have to load the non-default form and control properties. If you must change the defaults for most of the controls in your form, create one control with the desired properties and make that the default control.
To do so, follow these steps:
- Add a control to your form and change its defaults according to your form's requirements.
- With the control selected, go to Format | Set Control Defaults.
Now, when you add the control to your form, it will have the same properties as the first one. Access saves only the properties of the default control; it does not need to store each control's individual properties.
Microsoft Word
Create different mailings from the same list using Word 2007's Edit Recipients filter
You may have one Access or Excel table containing your customers' names and addresses. From this table, you want to create separate mailing lists based on certain field values. With the Edit Recipients feature, you don't have to depend on the table's application filter to create the new lists.
Word 2007 has added a Filter feature to its Edit Recipients dialog box that lets you create a number of lists from the same data file. For example, suppose you need to send a letter to all your customers in Illinois.
Follow these steps:
- Click on the Edit Recipients button in the Mailings tab.
- In the Mail Merge Recipients dialog box (Figure A), click Filter.
- Click the first drop-down box and select State.
- Press Tab twice, enter IL, and then click OK (Figure B).
Figure A
Figure B
As Figure C shows, only those records whose State field equals IL will be listed.
Figure C
You can also use this feature to omit any records where a specified field is blank. For example, say you want to send a statement of the customer's balance. If you don't want to print a statement for any customers whose Balance field is empty, follow these steps:
- Click on the Edit Recipients button in the Mailings tab.
- Click Filter.
- Click the first drop-down box and select Balance.
- Click the Comparison box drop-down arrow and click Is Not Blank.
- Click OK.
Microsoft Excel
Calculate the value of your inventory in one step in Excel
Let's say your company keeps a record of its inventory in the worksheet shown in Figure A.
Figure A
You need to calculate the total value of the inventory-on-hand from this worksheet. Your first instinct may be to create a new column that multiplies the cost of each item by its quantity and then sum the values obtained. An alternative way is to use Excel's SUMPRODUCT function, which adds and multiplies in one step.
Using this example, click in G2 and enter the following formula, as shown in Figure B:
=SUMPRODUCT(B2:B11,C2:C11)
Figure B
SUMPRODUCT multiples the values in the range B2:B11 by the values in C2:C11 and then returns the sum of those products.
There are currently no comments for this post.