February 19, 2009

Easy tricks to make your Access forms run faster

  • Date: October 2nd, 2008
  • Author: Mary Ann Richardson

By giving a form less data to digest, you can make it run more efficiently. These two tips can help.


cialis tadalafil 5mg width=”100%” size=”2″ />
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:

 

  1. Open the form in Design View and click the Selector button.
  2. In the form’s property sheet, click the Data tab.
  3. 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:

  1. Add a control to your form and change its defaults according to your form’s requirements.
  2. 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.

Permalink • Print • Comment

More natural SQL display and efficient sorting in Access list controls

  • Date: October 27th, 2008
  • Author: Susan Harkins

Concatenating data can increase readability and friendliness without losing a bit of functionality.

cialis tabs


It’s common to populate a list control with a SQL statement, especially if you want to concatenate fields. For instance, the following SQL statement displays a list of names in multiple columns:

SELECT EmployeeID, LastName, FirstName

FROM Employees

ORDER BY LastName

october2008officeblog10fig1r.jpg

The control’s Column Count property is 3 and the Column Width property is 0. That way, the control can pass a record’s primary key value (EmployeeID) unseen.

The ORDER BY clause sorts the rows by LastName. The solution is typical, but Access can do better. We’re just not used to viewing names in columnar format. Concatenating the names into a single column, as follows, displays a more familiar format:

SELECT EmployeeID, LastName & ", " & FirstName

FROM Employees

ORDER BY LastName

october2008officeblog10fig2r.jpg

I want to caution you against sorting on the concatenated field as follows:

SELECT EmployeeID, LastName & ", " & FirstName

FROM Employees

ORDER BY LastName & ", " & FirstName

It’ll work, but if the data source contains a lot of data, it won’t perform well. Sorting by individual columns is more efficient, especially if the fields are indexed; Jet can’t use an index against the concatenated results of the ORDER BY clause. Besides, it’s awkward and unnecessary. Just specify sort fields in the ORDER BY clause as you normally would — displaying concatenated data doesn’t change the way you sort.

There’s still a way to improve the display. Perhaps it really isn’t an improvement, but a different way to present the data. Transpose the names, displaying the first name first, in a more natural format. You can still sort by last names, as follows:

SELECT EmployeeID, FirstName & " " & LastName

FROM Employees

ORDER BY LastName

october2008officeblog10fig3r.jpg

Don’t worry if a sort field isn’t in the SELECT clause’s field list — SQL doesn’t require that. SQL requires only that the sort field be in the underlying data source.

Permalink • Print • Comment

Repeat a table heading row at the beginning of each page

  • Date: October 24th, 2008
  • Author: Susan Harkins

When you have a Word table that spans multiple pages, you’ll probably want the header row to appear at the top of each page. Luckily, repeating a heading row across several pages takes only a few quick clicks.


cialis tablets for sale width=”100%” size=”2″ />

Sometimes, what seems to be a difficult problem has an easy built-in solution. I ran into that recently with a Word table that was several pages long. Of course, I wanted to repeat the table’s header row at the top of each page. That way, readers didn’t have to return to the table’s first page to learn what type of data was in a particular column. I had to stop and think for a minute, as I couldn’t remember ever doing this before.

Customized menus make it easy to miss built-in features. If you don’t use the item occasionally, Word doesn’t display the item on the menu! It’s less than intuitive, but I went back to the Table menu and clicked the double arrow to display all the menu items and there it was — Heading Rows Repeat.

To use this feature, simply click inside the header row and choose Heading Rows Repeat from the Table menu. It couldn’t be simpler. Word then displays the heading row at the top of every page as long as the table continues.

october2008officeblog9fig1r.jpg

Permalink • Print • Comment

Create an Access form that lets users specify which data to print

  • Date: October 28th, 2008
  • Author: Mary Ann Richardson

You can easily add a custom form button that enables your users to choose which records to print. Mary Ann Richards steps through the process.


When you want to enable your users to print only the records they need, you can set up a form that lets them do just that. For example, to create a form that generates a report listing only the records of employees hired during a specific period, follow these steps:

  1. In the Database Window, under objects, click Forms, click New, and then click OK. (In Access 2007, click Create and then click the Forms Design command in the Forms group.
  1. Click the Text box tool in the Toolbar options box. (In Access 2007, click the Text box control on the Design tab.)
  1. Click and drag to create the first unbound text box. Change the text box label to From:.
  1. Click the Text Box tool in the Toolbar options box. (In Access 2007, click the Text Box control on the Design tab.)
  1. Click and drag to create the second unbound text box. Change the text box label to To:.
  1. Right-click the first text box and select Properties. Click the All tab and then click in the Name property box and type StartDate.
  1. Right-click the second text box and select Properties. Click the All tab and then click in the Name property box and type EndDate.
  1. Click the Command tool in the Toolbar Options box. (In Access 2007, click the Command control on the Design tab.)
  1. Click and drag in the form to locate cialis soft the Command button. (Click Cancel to close the Command wizard, if necessary.)
  1. Right-click the Command tool and then click Properties.
  1. Click the All tab and then click in the Caption property box and type Print Report.
  1. Click the Event tab.
  1. Click the Build button in the On Click property box.
  1. Click Event Procedure.
  1. Enter the following code at the prompt, as shown in Figure A, and press Alt + Q:

Dim ReportName   ReportName = "Employees1"   DoCmd.OpenReport _   ReportName:=ReportName, _   view:=acViewPreview, _   WhereCondition:="[Hire Date] Between #" & _   Me.StartDate & "# AND #" & _   Me.EndDate & "#"

Figure A

custom button code

To use this form to generate a report for all employees hired from 2001 to 2003, you would enter 1/1/2001 in the From box and 1/1/2003 in the To box (Figure B). Then, just press the Print Report button.

Figure B

print report

Permalink • Print • Comment

Calculate the total word count of a document — including text boxes

  • Date: October 28th, 2008
  • Author: Mary Ann Richardson

If you need to know how many words are in a document, and some of them are inside a text box, you’ll have to perform at least one extra step. Here’s a look at how to get the numbers you need.


The Word Count command in Word 2002/2003 excludes text boxes when totaling the number of words in your document. As Figure A shows, the Word Count dialog shows a count of 0, even though the document contains two linked text boxes filled with text.

Figure A

word count

Since the text boxes are linked, you can find the number of words in those boxes by following cialis soft tabs vs cialis these steps:

  1. Click the left text box.
  1. Press Ctrl + A.
  1. Go to Tools | Word Count.

The Word Count box now shows the correct number of Words in the text boxes, as shown in Figure B.

correct word count

If the text boxes had not been linked, you would have needed to perform this operation on each box separately and then add the totals of each box to get a final count. Likewise, to calculate the total number of words in a document with one text box surrounded by other text, you would go to Tools | Word count to find the total for the surrounding text, follow steps 1 through 3 to find the total for the text box, and then add the two numbers for your total.

Permalink • Print • Comment
« Previous PageNext Page »
Made with WordPress and the Semiologic theme and CMS • Sky Gold skin by Denis de Bernardy