June 2, 2011

10 mistakes to avoid when designing Access objects

June 1, 2011, 6:28 AM PDT

Takeaway: There are lots of false assumptions floating around the world of Access development. Susan Harkins has rounded up some of the worst object design mistakes that developers make — and offers a best practice to counter each one.

When you develop an Access application, normalizing your data and creating relationships is just the beginning. The next step is adding the UI objects that users will interact with and the queries that will populate those objects. There isn’t a set of rules, like normalization rules, to guide you during this stage. It’s easy to make assumptions that will haunt you sooner or later… usually both. The following are 10 assumptions you shouldn’t make when adding queries, forms, and reports to your database application.

1: Object names don’t really matter

Object names indicate the object’s purpose and often provide an easy way to organize objects in a logical manner. Object names can also help a developer work a bit more efficiently. A descriptive name, such as Employees, is adequate for users. But it doesn’t help the developer much. Some developers use a prefix or tag to identify objects by type. This practice can come in handy during programming. For example, you can easily cycle through collections and find all subforms by checking the name property for an appropriate prefix or tag. The details are up to you; I don’t advocate a one-size-fits-all convention. Find one that helps you work efficiently and apply it consistently.

2: Tables are for storing data, period

Tables are an interface used to display data, but there’s more to them than data. Field properties, such as data type and field size, help you maintain data integrity. An input mask provides a pattern for data input, and a default value can reduce input. Indexing the appropriate fields will help performance. Even the field description, which many developers ignore, is helpful. Access displays the description text in the status bar, providing a helpful hint to users. Setting the Required property to Yes eliminates the need to deal with null values (although null values are acceptable values). In addition, forms and reports inherit these attributes and properties. That means you can set them once at the table level instead of repeatedly throughout all your UI objects.

3: You can just replicate your paper forms

Paper forms are for paper — not your Access forms. Trying to replicate paper forms in Access might work, and it might not. You could end up making a lot of extra work for yourself. Let the data and the process dictate form design.

4: You need just one big form

Access will let you build a form that’s 22 inches wide, but how are you going to display it? Too much of a good thing is a nuisance. When designing forms, break processes down into small tasks and use forms to perform them. Don’t try to use one form to do it all. The larger the form, the slower it will perform, and it’s sure to overwhelm the users.

5: Wizards produce reports you can use as-is

Access wizards are pretty slick. Most of them will save you time, prevent errors, and produce an acceptable object. Unfortunately, the results of the report wizards are ugly. These wizards provide a nice start, but if you distribute one of these reports without some serious tweaks, you’ll look incompetent. Just don’t do it.

6: Wizards creates subforms, so go ahead and use them

If you run a form wizard on a multi-table query, the wizard will most likely generate a main/subform arrangement to display related data. The arrangement is sound, but controls load faster than subforms. Often, list controls are a better choice for displaying related data than subforms. Don’t settle.

7: Users don’t need to know what’s going on

One of the most grievous mistakes developers can make is to ignore the user. Impatient users are apt to do things they shouldn’t. When they don’t know what else to do, they press [Esc], [Enter], [F1], and even [Ctrl][Alt][Delete] — oops! Give users some kind of visual feedback. Let them know that a task has been successfully completed (or not). If the latter, tell them what they need to do to continue. When users need to wait for a task to complete, show them a simple meter or status message. Don’t leave them hanging.

8: Basing forms and reports on a table is just fine

Base your forms, subforms, reports, and subreports on queries rather than tables. You can easily restrict queries to return just the fields and records you really need to populate the object. In addition, you can easily adjust the underlying query to adapt to changing requirements.

9: Users should ignore empty reports

Don’t display empty reports. Users will assume something’s wrong. It might not occur to them that there’s just no data to report. Use the report object’s NoData event procedure to display an explanatory message and cancel the report as follows:

Private Sub Report_NoData(Cancel As Integer)     MsgBox "The report has no data.", vbOKOnly + vbInformation     Cancel = True End Sub

10: You’ll remember what you did

Sure you will. Anytime you strategize and choose between possible solutions, consider documenting that decision. You don’t have to write a book; most of what you do is self-explanatory. If you’re writing code, you can add a few comments, but there’s no such vehicle for objects. Consider adding an invisible text control (visible in Design view only) with a short explanation — at least leave the next guy a few breadcrumbs. (That next guy might be you!)

Additional resources

Permalink • Print • Comment

February 19, 2009

Use a form to display a list of reports and queries in an Access database

  • Date: December 9th, 2008
  • Author: Mary Ann Richardson

When you need to know what objects are being created for a database, you can run generic soft tabs cialis a query against an Access system table to generate a list of all current reports and queries.

Ever wonder exactly what reports and queries are being created for a database? As an administrator, you may need a quick way of determining what reports and queries are being run against a database. Fortunately, Access stores the names of all the top-level database objects in a system table called MSysObjects. You can run queries against MSysObjects just as you would any other table in the database.  Follow these steps:

  1. Open the database and create a form with two unbound list boxes, as shown in Figure A.

Figure A

two unbound boxes

  1. In Design view, right-click the first list box and select Properties.
  2. Under the Data tab, right-click the RowSource property box and select Zoom.
  3. Enter the code as shown in Figure B and click Close.

Figure B

rowsource property

  1. Right-click the second list box and select Properties.
  2. Under the Data tab, right-click the RowSource property box and select Zoom.
  3. Enter the code as shown in Figure C.

Figure C


When you run the form, Access outputs all current reports and queries for that database to the respective list boxes (Figure D).

Figure D

object query

To access other object types, use their type value. For example, to list all the tables in the database, enter 6. To list forms, enter -32768. To list macros, enter -32766.

Permalink • Print • Comment

Let Access add your name to a report

  • Date: December 9th, 2008
  • Author: Mary Ann Richardson

It’s useful to include some basic information in Access report footers, such as the name of the person who prepared the report. Here’s a simple way to prompt for the user’s name and insert it in the page footer.

Since a report is just a snapshot of a database at a certain date and time, generic cialis review it is helpful to include that information in the report printout. In addition to a time and date stamp, it’s also nice to have the preparer add his or her name to the end each report page. Follow these steps:

  1. Open the report in Design view.
  1. Click the Text Box control tool and then click and drag in the Page footer section of the report.
  1. Select the text in the Label control, type Prepared by:, and format it as desired.
  1. Click in the text box and enter the formula below, as shown in Figure A:

=[What is your name?]

Figure A

text box formula

When you run the report, you’ll see the dialog box shown in Figure B.

Figure B

name prompt

After the preparer enters his/her name, the report will output with a page footer like the one shown in Figure C.

Figure C

report footer

Permalink • Print • Comment

Refresh your Access combo boxes with the Requery shortcut

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

You can update your combo boxes on the fly with the help of an Access shortcut. generic cialis overnight Mary Ann Richardson explains how it works.

Combo boxes let you type an entry into a field and select the entry again whenever you need it for another record. However, Access runs the query to the combo box only when the form is opened. If you add a new entry to the field, it will not appear in the list until you reopen the form. Fortunately, there’s a quick way to update your combo box without having to open and close the form.

For example, say you are entering data for a number of employees whose zip code is 07056. You enter the code for one employee. Then, when you enter the next one, you find that zip code is not on the list (Figure A).

Figure A

zip code

Follow these steps:

  1. After entering the new zip code for the first time, move to the next record.
  1. Press Shift + F9.

This is the Requery shortcut, which takes you back to the first record. When you click on the combo box to update any record, 07056 is now displayed, as shown in Figure B.

Figure B

combo box update

You can use the Requery key to update any control based on SQL data.

Permalink • Print • Comment

Save a million keystrokes by turning Access text boxes into combo boxes

  • Date: November 18th, 2008
  • Author: Mary Ann Richardson

If you repeatedly type the same text in the same field, you’re working too hard. See how to set up a combo box that will speed data entry and spare you a TON of tedious typing.

Do you find yourself constantly typing the same data in the same field? For example, say you work with an Employee Data form, and you find that you’re typing the same three Zip codes repeatedly. Since most of your employees live near your company’s three offices, you seldom need to type any other codes. By converting the Zip code text box into a combo box, you’ll eliminate the retyping. Follow these steps:

  1. Open the form in Design view.
  1. Right-click the Zip/Postal Code text box.
  1. Move to Change To and click Combo Box (Figure A).

Figure A


  1. Right-click the Zip/Postal Code combo box.
  1. Click Properties (Figure B).

Figure B


  1. On the Data tab, click the Build button in the Row Source field (Figure C).

Figure C

build buttondaily cialis results button” width=”302″ height=”287″ />

  1. Add the Employees table.
  1. In the field list, double-click Zip/Postal Code.
  1. Click in the Criteria box under Zip/Postal Code and enter Is Not Null (Figure D).
  1. Close and save the query.

Figure D


Now you can simply select one of the three zip codes from the drop-down list in the combo box, as shown in Figure E.

Figure E

combo box

Permalink • Print • Comment
Next Page »
Made with WordPress and a healthy dose of Semiologic • Sky Gold skin by Denis de Bernardy