Access Tips

Back to Hints and Tips Index                                                Back to Main Index

Hiding duplicates in query results (Access 97/2000/2002)

It's easy to hide duplicate entries when you run a query, even though Access doesn't go out of its way to call attention to this ability.

• To do so, set up a query as usual using the design grid.

• Then, choose View | Properties from the menu bar to display the Query Properties dialog box.

• Change the Unique Values property to Yes.

• Access displays unique records based on each field returned by the query.

• It's worth mentioning a few points about the record set that the query returns. First, the results are automatically sorted, based on the order of the fields in the query design grid. Also, you should be aware that the record set isn't updateable.

Paste makes waste

Do you support users who paste data directly into Access tables? If so, you probably often get calls from users who receive a message that Access is adding data to the Paste Errors table when they're trying to paste. When that happens, you can usually explain and resolve the problem pretty quickly by considering all the usual suspects.

When you hear, "Some of the fields are missing," chances are good that the user tried to paste more fields than the datasheet contains and Access ignored them. Users who are in a hurry will try to paste without selecting destination fields or try to paste into the wrong destinations.

Dirty data is a frequent cause of paste errors. If the data layout of the Access table has changed (and no one told the user), or if the layout of the foreign file changed (and no one updated the Access table), then values may get kicked out if they're not compatible with the data type for the field in which they attempted to land.

Access may also reject records for dozens of other reasons, such as violating validation rules. For users who frequently paste data from other sources into their datasheets, the best advice may be: Make a clean backup copy of your table before you paste, just in case.

Suppress Text Box Labels

Every time you add a text box to a form or report, Access assumes you want to include a label with that text box. Many Access users just don't like these text box labels, and they delete them as soon as Access creates them. If you prefer not to use labels with your text boxes, you don't have to spend time manually deleting them. You can tweak the text box control's Auto Label property so that Access won't generate these labels by default.

This tip doesn't help much if you've already placed a text box and its label on your form or report. But once you change the Auto Label property, you'll change how Access creates new text boxes for the current form or report.

To make the change, open the form in Design view and click the Toolbox's Text Box control. Go to View | Properties, which opens the Default Text Box dialog box. On the Format tab, locate the Auto Label property and change the setting to No.

From this point on, each time you create a text box for this form or report, Access won't automatically generate a corresponding label. This setting will remain in effect for this form or report only unless you have used it as your template. (See the next Quill Newsletter for more on creating form or report templates.)

Note that this technique can be applied to the default property setting of any of the toolbox controls.

Back to Hints and Tips Index                                                Back to Main Index