Ms Office Tune Up

If you haven’t thought much about importing and exporting, it’s probably because you don’t need to use these featuresyet. Many databases are completely happy living a quiet, solitary life. However, importing and exporting might come in handy for a few reasons. Sooner or later, one of these reasons will apply to you.

Understanding Exports

Exporting is the easier part of the equation. Exporting’s simpler than importing, because it involves moving information from a stricter storage location (the data-base) to one with fewer rules (another type of document).

Here are some of the most common reasons people decide to export information:

• You want to email some information to a friend. You don’t want to send the Access database because your friend doesn’t have a copy Access, or you want him to see only some not all of the data.

• You’re creating a presentation in PowerPoint. The easiest way to dazzle and convince your peers is to show them some impressive information from your database.

• You want to analyze the information in Excel. Access is great for storing and managing your data, but it doesn’t give you the tools to help you figure out what it all means. If you want to crunch the numbers with heavy duty formulas and slick charting features, it makes sense to move it to Excel.

Read the rest of this entry »

Popularity: 2% [?]

By now, you’ve learned how to create a number of common forms. Access gives you one other way to build a form: using the Form wizard. The Form wizard has an uncanny similarity to the report wizard you used in Chapter 8. It asks you a series of questions and then builds a form to match. However, the questions are fairly rudimentary, and the form it builds is little more than a good starting point for further customization.

Here’s how to put the Form wizard through its paces:

1. Choose Create Forms More Forms Form Wizard.

   The first step of the Form wizard appears.

2. From the drop-down list, choose the table you want to use.

   In the Available Fields list, the wizard shows all the fields that are in your table.

   UP TO SPEED The Access Form Family

   Access forms manage to please just about everyone. If you’re in a hurry, then you can create a ready-made form with a basic layout and a dash of formatting. Or, if you’re feeling creative, you can pull your fields out of the standard layouts and place them absolutely anywhere. In other words, forms are flexibletimepressed types get the convenience they need, while serious artistes get the creative control they demand. Here’s a roundup of some of your form choices: A simple form shows one record at a time in a basic stacked layout. To create a simple form, choose Create Forms Form. A layout-less form lets you place controls anywhere you want on a form. It’s up to you whether you want to show a single record at once, or several records at a time. When creating a layout-less form, you need to do all the work. You can get started by choosing Create Forms Form Design (to start in Design view) or Create Forms Blank Form (which starts you in Layout view). A tabular form shows records in a tabular layout. Usually, tabular forms show several records at once (which gives the appearance of a table). To quickly create one of these babies, choose Create Forms Multiple Items. A datasheet form looks exactly like the Datasheet view you get with a table. This form’s not as powerful as other form types, but it’s still useful if you want a customized datasheet-like view of your data. You can create a datasheet form that shows fewer columns, uses filtering to hide certain records, prevents record insertions, uses different formatting, and so on. To create a datasheet form, choose Create Forms More Forms Datasheet. A split form combines two types of form in one window. One portion of the window shows the current record in a simple form. The other portion of the window shows a datasheet with several records. To create a split form, choose Create Forms Split Form. The advanced form types, pivot table and modal dialog, are covered in depth in Access 2007: The Missing Manual.

3. Add the fields you want to include, as shown in Figure 9-12. When you’re finished, click Next.

   You can choose fields from more than one table, provided these tables are related. Read the rest of this entry »

Popularity: 2% [?]

One of form filtering’s limitations is that Access remembers only your most recent set of filters. If you’ve perfected a complex filter expression that you want to reuse later, this quality’s a problem. As soon as you apply a different filter, you’ll lose all your hard work.

Fortunately, you have several solutions to this dilemma. One option’s to create a whole new query that performs the filtering, and use that query in a whole new form. This choice is a good one if you want to use your filter criteria to perform a specific task, and you also want to customize the way the form works or the way it displays its data.

On the other hand, if you don’t plan to use your filtering settings very often, but you just want to have them on hand for the next time you need them (or if you need to store dozens of different filter settings, and you don’t want to be stuck with dozens of nearly identical forms), there’s a better option. You can save your filter settings as a query in your database. Then, when you want them back, you can load them up and apply them to your form.

Here’s how to pull this trick off: Read the rest of this entry »

Popularity: 2% [?]

One other filtering technique works with forms: filter by form. Essentially, “filter by form” transforms your form into a full-fledged search form. Using this search form, you supply one or more criteria. Then you apply the filter to see the matching record (or records).

Although you can use “filter by form” with the datasheet, it really shines with forms. “Filter by forms” is particularly useful for searching out a single hard-to-find record. (If you want to use filtering to pull out a whole group of records, one of the other filtering options is generally easier.)

Here’s how to use the “filter by form” feature:

1. Choose Home Sort & Filter Advanced Filter Options Filter By Form.

   Access changes your form to search mode. In search mode, your form looks exactly the same, except all the fields are blank.

   If you’ve already used the “filter by form” feature and you’re returning to change the filter settings, then you should start by clearing the previous set of filters. To do so, right-click a blank spot on the form surface, and then choose Clear Grid.

2. Move to the field you want to use for filtering.

   A drop-down arrow appears in the field.

3. Click the drop-down arrow, and then choose the value you want to include in your results.

   The drop-down list shows all the values from the different records in the table (Figure 9-10). When you choose one, it appears in the field box in quotation marks.

4. If you want to apply a filter to more than one field, then return to step 2.

   Use multiple filter conditions if a single filter condition may result in more matches than you want. If you don’t remember a customer’s last name, you could apply a FirstName filter. But if that customer has a common first name, then you may also want to apply a filter on another field, like City. Read the rest of this entry »

Popularity: 2% [?]

Sorting and filtering are two indispensable features that Access gives you with Form view. Learning how to use them could hardly be easierin fact, you already learned everything you need to know when you tackled the datasheet in Chapter 3. The creators of Access took great care to ensure that filtering and sorting work the same in forms as they do in the datasheet. You use the same commands, on the same part of the ribbon, to put them into action.

Sorting a Form

As you’ve probably realized by now, forms show your data in raw, unsorted order. So records appear in the order you created them. (The only exception is if you create a form that gets its data from a query, and that query uses sorting.)

Fortunately, sorting’s easy. In fact, you can sort the records that are shown in a form in exactly the same way you sort records in a datasheet. Choose the field you want to use for sorting, right-click it, and then choose one of the sorting options. In a text-based field, you’ll see the sorting choices “Sort A to Z” (for an alphabetical sort) and “Sort Z to A” (for a reverse-alphabetical sort). You can also use the Ascending and Descending buttons on the ribbon’s Home Sort & Filter section.

Filtering a Form

Filtering’s a feature that lets you cut down the total number of records so you see only those that interest you. Filtering can pick out active customers, in-stock products, expensive orders, and other groups of records based on specific criteria.

In a form, you have the following filtering choices: Read the rest of this entry »

Popularity: 2% [?]

When you find a record that shouldn’t exist, you can wipe it out in seconds. The easiest way to delete the current record is to choose Home Records Delete. But you have another option. You can select the whole record by clicking the margin on the form window’s left side. Then you can liquidate it by pressing Delete.

Figure 9-8. Top: Here, the Picture field shows a bobblehead doll’s picture. Access sizes the picture to fit the Attachment control box (without unnaturally stretching or skewing the picture).
Bottom: When you select the Picture field, you see a minibar with additional options appear right above the image. The arrows let you step through all the attached files for this record. The paper clip icon opens the Attachments window, where you can add or remove attachments, or open them in a different program. (The Attachments window’s described in Section 2.3.8.)

No matter what approach you use, Access asks you for confirmation before it removes a record. You can’t recover deleted records, so tread carefully.

Read the rest of this entry »

Popularity: 2% [?]

As you already know, you add a new record in datasheet view by scrolling to the very bottom of the table, and typing just underneath the last row. In Form view, the concept’s similarscroll to the very end of your table, just past the last record.

You’ll know you’ve reached the magic ready-to-add-a-record spot when all the fields in your form are blank (Figure 9-7). To save yourself the scrolling trip, use the New Record button at the bottom of the form (marked in Figure 9-7).

Figure 9-7. When you create a new record, you start off with a clean slate that shows your form’s formatting but no values. If you’ve set any default values for the table (Section 4.1.2), then you see them appear instead of the blank values. In the Products table, the UnitsInStock field has a default value of 10.

Read the rest of this entry »

Popularity: 2% [?]

Rare is the record that never changes. Depending on the type of data you’re storing, most of your work in Form view may consist of hunting down a specific record and making modifications. You may need to ratchet up the price of a product, change the address details of an itinerant customer, or reschedule a class.

Before you can make any of these changes, you need to find the right record. In Form view, you have four ways to get to the record you need. The first three of these methods use the navigation controls that appear at the bottom of the form window.

• By navigating. If your table’s relatively small, then the fastest way to get going is to click the arrow buttons to move from one record to the next. Section 3.2.1 has a button-by-button breakdown.

• By position. If you know exactly where your record is, then you can type in the number that represents the position (for example, 100 for the one-hundredth record), and then hit Enter. If you don’t get exactly where you want, then you can also use the navigation buttons to move to a nearby record.

• By searching. The quick search feature finds a record with a specific piece of text (or numeric value) in one of its fields. To use quick search, type the text you want to find in the search box, as shown in Figure 9-6. If you want a search that examines a specific field or gives you additional options, then use the Home Find Find command.

  

  Figure 9-6. When you use the quick search box, you don’t need to hit Enter. Access finds the next match as you type

• By filtering. Using filtering, you can narrow down the displayed records to a small set. Filtering’s best-kept secret’s that you can use a feature called filter by form to quickly hunt down a single record. You’ll see how that works in Section 9.2.3.

Once you’ve found the record you want to change, you can edit it in the same way you would in the datasheet. If you make a change that breaks a rule (like typing the text “Exasperated Bananas” in a date field), then you get the same familiar error messages.

Access commits any change you make as soon as you move to another record or field. To back out of a change, hit Esc before you move on. When you do, the original value reappears in the cell, and Access tosses out your changes. And if you do commit a change by accident, then you can use the Undo button in the Quick Access toolbar (above the ribbon), or hit Ctrl+Z, to reverse it.

Popularity: 2% [?]

Now that you’ve created your first form, it’s time to take it for a test spin. All forms have three different viewing modes:

• Layout view. This is the view you’ve been using so far. It lets you see what your form looks like (with live data), rearrange fields, and apply formatting.

• Design view. While Layout view provides the simplest way to refine your form, Design view gives you complete power to fine-tune it. In Design view, you don’t see the live data. Instead, you see a blueprint that tells Access how to construct your form.

• Form view. Both Layout view and Design view are there to help you create and refine your form. But once you’ve perfected it, it’s time to stop designing your form and start using it to browse your table, review the information it contains, make changes, and add new records.

To try out the form you created, switch it to Form view if you’re not already there. Just right-click the tab title, and choose Form View.

In Form view, you can perform all the same tasks you performed in the datasheet when you worked with a table. With a simple form, the key difference is that you see only one record at a time.

Most people find forms much more intuitive than the datasheet grid. The following sections give a quick overview of how you can use Form view to perform some common tasks.

Popularity: 2% [?]

As with reports, Access gives you an easy and a more advanced way to construct a form. The easy way creates a ready-made form based on a table or query. Keen eyes will notice that this process unfolds in more or less the same way as when you automatically generate a simple report (Section 8.1.1).

Here’s how it works:

1. In the navigation pane, select the table or query you want to use to generate the form.

   Try the Products table from the Boutique Fudge database.

   ---

   Note: If you create a form for a parent table that’s linked to other tables, then you wind up with a slightly different type of form. If you create a form for the Categories table (a parent of the Products table), then you end up with a two-part form that lets you view and modify the category record and the linked product records in each category.
   

   ---

2. Choose Create Forms Form.

   A new tab appears, with your form in Layout view. The simple form shows one record at a time, with each field on a separate line (Figure 9-1). If your table has lots of fields, then Access creates more than one column (Figure 9-2). Read the rest of this entry »

Popularity: 2% [?]