Training
Certifications
Books
Special Offers
Community




 
Microsoft® Office Access 2003 Step by Step
Author Online Training Solutions Inc.
Pages 368
Disk 1 Companion CD(s)
Level Beg/Int
Published 08/27/2003
ISBN 9780735615175
Price $24.99
To see this book's discounted price, select a reseller below.
 

More Information

About the Book
Table of Contents
Sample Chapter
Index
Related Series
Related Books
About the Author

Support: Book & CD

Rate this book
Barnes Noble Amazon Quantum Books

 


Chapter 4: Simplifying Data Entry with Forms



In this chapter you will learn to:

  • Create a form by using a wizard.
  • Refine form properties.
  • Refine form layout.
  • Add controls to a form.
  • Use Visual Basic for Applications to enter data in a form.
  • Create a form by using an AutoForm.
  • Add a subform to a form.

A database that contains the day-to-day records of an active company is useful only if it can be kept up to date and if particular items of information can be found quickly. Although Microsoft Office Access 2003 is fairly easy to use, entering, editing, and retrieving information in Datasheet view is not a task you would want to assign to someone who's not familiar with Access. Not only would these tasks be tedious and inefficient, but working in Datasheet view leaves far too much room for error, especially if details of complex transactions have to be entered into several related tables. The solution to this problem, and the first step in the conversion of this database to a database application, is to create and use forms.

A form is an organized and formatted view of some or all of the fields from one or more tables or queries. Forms work interactively with the tables in a database. You use controls in the form to enter new information, to edit or remove existing information, or to locate information. Like printed forms, Access forms can include label controls that tell users what type of information they are expected to enter, as well as text box controls in which they can enter the information. Unlike printed forms, Access forms can also include a variety of other controls, such as option buttons and command buttons that transform Access forms into something very much like a Microsoft Windows dialog box or one page of a wizard.


TIP:
Some forms are used to navigate among the features and functions of a database application and have little or no connection with its actual data. A switchboard is an example of this type of form.

As with other Access objects, you can create forms by hand or with the help of a wizard. Navigational and housekeeping forms, such as switchboards, are best created by hand in Design view. Forms that are based on tables, on the other hand, should always be created with a wizard and then refined by hand—not because it is difficult to drag the necessary text box controls onto a form, but because there is simply no point in doing it by hand.

In this chapter, you will create some forms to hide the complexity of the GardenCo database from the people who will be entering and working with its information. First you will discover how easy it is to let the Form Wizard create forms that you can then modify to suit your needs. You'll learn about the controls you can place in a form, and the properties that control its function and appearance. After you have created a form containing controls, you will learn how to tell Access what to do when a user performs some action in a control, such as clicking or entering text. You will also take a quick look at subforms (forms within a form).


SEE ALSO:
Do you need only a quick refresher on the topics in this chapter? See the Quick Reference entries on pages xxxiii–xxv.


IMPORTANT:
Before you can use the practice files in this chapter, you need to install them from the book’s companion CD to their default location. See "Using the Book’s CD-ROM" on page xiii for more information.

Creating a Form by Using a Wizard

Microsoft Office Specialist

Before you begin creating a form, you need to know what table it will be based on and have an idea of how the form will be used. Having made these decisions, you can use the Form Wizard to help create the basic form. Remember though, that like almost any other object in Access, after the form is created you can always go into Design view to customize the form if it does not quite meet your needs.

In this exercise, you'll create a form that will be used to add new customer records to the Customers table of The Garden Company's database.

BE SURE TO start Access before beginning this exercise.

USE the GardenCo database in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Access 2003 SBS\Forms\FormByWiz folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Access 2003 Step by Step.

OPEN the GardenCo database and acknowledge the safety warning, if necessary.

  1. On the Objects bar, click Forms.
  2. Double-click Create form by using wizard to display the first page of the Form Wizard.
  3. g04ac01.jpg

  4. Click the down arrow to the right of the Tables/Queries box and click Table: Customers to display the fields from that table in the Available Fields list.
  5. Click the >> button to move all the fields from the Available Fields list to the Selected Fields list, and then click Next.
  6. The second page of the Form Wizard appears, in which you choose the layout of the fields in the new form. When you select an option on the right side of the page, the preview area on the left side shows what the form layout will look like with that option applied.

  7. If it is not already selected, select Columnar, and then click Next.
  8. The third page of the wizard appears, in which you can select a style option to see how the style will look when applied to the form.

  9. Click the Sumi Painting style in the list, and click Next.
  10. Because this form is based on the Customers table, Access suggests Customers as the form’s title. Accept this suggestion, leave the Open the form to view or enter information option selected, and click Finish.
  11. The new Customers form opens, displaying the first customer record in the Customers table.

    g04ac02.gif

  12. Use the navigation controls at the bottom of the form to scroll through a few of the records.
  13. Close the form.

CLOSE the GardenCo database.

Refining Form Properties

Microsoft Office Specialist

As with tables, you can work with forms in multiple views. The two most common views are Form view, which you use to view or enter data, and Design view, which you use to add controls to the form or change the form's properties or layout.

When you use the Form Wizard to create a form in a column format, every field you select from the underlying table is represented by a text box control and its associated label control. A form like this one, which is used to enter or view the information stored in a specific table, is linked, or bound, to that table. Each text box—the box where data is entered or viewed—is bound to a specific field in the table. The table is the record source, and the field is the control source. Each control has a number of properties, such as font, font size, alignment, fill color, and border. The wizard assigns default values for these properties, but you can change them to improve the form's appearance.

In this exercise, you will edit the properties of the Customers form so that it suits the needs of the people who will be using it on a daily basis.

USE the GardenCo database and the tgc_bkgrnd graphic in the practice file folder for this topic. These practice files are located in the My Documents\Microsoft Press\Access 2003 SBS\Forms\Properties folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Access 2003 Step by Step.

OPEN the GardenCo database and acknowledge the safety warning, if necessary.

  1. With Forms selected on the Objects bar, click Customers in the list of forms, and click the Design button to open the form in Design view.
  2. bia049

    g04ac03.jpg

    When a form is created, some of its properties are inherited from the table on which it is based. In this example, the names assigned to the text boxes (FirstName, LastName, and so on) are the field names from the Customers table, and the labels to the left of each text box reflect the Caption property of each field. The size of each text box is determined by the Field Size property.


    TIP:
    After a form has been created, its properties are not bound to their source. In previous versions of Access, changing the table’s field properties had no impact on the corresponding form property, and vice versa. Now in Access 2003, when you modify an inherited field property in Table Design view, you can choose to update the property in all or some controls that are bound to the field.

  3. Click the Customer ID label (not its text box). Then on the Formatting toolbar, click the down arrow to the right of the Font button, and click Microsoft Sans Serif. (If you don’t see Microsoft Sans Serif, click MS Sans Serif.)
  4. With the label still selected, click the down arrow to the right of the Font Size box, and click 8 to make the font slightly smaller.
  5. Right-click the CustomerID text box (not its label), and click Properties on the shortcut menu to display the Properties dialog box for the CustomerID text box.
  6. g04ac04.gif

    All the settings available on the toolbar (plus a few more) are also available in a Properties dialog box that is associated with each control. You can use this dialog box to display the properties of any object in the form, including the form itself: simply click the down arrow to the right of the box at the top of the dialog box, and click the object whose properties you want to display.

    You can display related types of properties by clicking the appropriate tab: Format, Data, Event, or Other. You can also display all properties by clicking the All tab.

  7. Click the Format tab, scroll to the Font Name property, and change it to Microsoft Sans Serif (or MS Sans Serif). Then set Font Size to 8, and set Font Weight to Bold.
  8. On the form behind the dialog box, you can see how these changes affect the CustomerID text in the text box (you might have to move the dialog box).


    TIP:
    When you are working in Design view with the Properties dialog box open, you can drag the dialog box by its title bar to the side of the screen so that you can see the changes you’re making to the form.

  9. Click the down arrow to the right of the box at the top of the Properties dialog box, and click FirstName_Label box to select the label to the left of the FirstName text box.
  10. Repeat step 5 to change the font settings for this control.
  11. These different ways of selecting a control and changing its properties provide some flexibility and convenience, but you can see that it would be a bit tedious to apply any of them to a few dozen controls in a form. The next two steps provide a faster method.

  12. Click anywhere in the form, and then press CRTL + A to select all the controls in the Detail section of the form.

  13. TIP:
    You can also select all the controls in a form by opening the Edit menu and clicking Select All, or by dragging a rectangle over some portion of all the controls.

    Small black handles appear around all the controls to indicate that they are selected. The title bar of the Properties dialog box now displays Multiple selection, and the Objects list is blank. Only the Format settings that have the same settings for all the selected controls are displayed. Because the changes you made in the previous steps are not shared by all the selected controls, the Font Name, Font Size, and Font Weight settings are now blank.

  14. To apply the settings to all the selected controls, set the Font Name, Font Size, and Font Weight properties as you did in step 5.
  15. With all controls still selected, on the Format tab, click Back Style, and set it to Normal.
  16. The background of the labels will no longer be transparent.

  17. Click Back Color, and then click the … button at the right end of the box to display the Color dialog box.
  18. g04ac05.gif

  19. Click pale yellow (the second option in the top row), click OK, and then press ENTER to accept the change.
  20. The background of all the controls changes to pale yellow.


    TIP:
    If you don’t see a color you want to use, click Define Custom Colors, work with the various settings until you have specified the desired color, and then click Add to Custom Colors.

  21. Set Special Effect to Shadowed, and set Border Color to a shade of green.
  22. You can either click the … button and make a selection, or type a color value such as 32768 in the Border Color box.

  23. Click the Detail section in the form to deselect all the controls.
  24. g04ac06.gif

  25. Click the label to the left of FirstName, and in the Properties dialog box, scroll up to the Caption box and change First Name to Name.
  26. Repeat step 15 to change Phone Number to Phone.

  27. TIP:
    You can edit the Caption property of a label or the Control Source property of a text box by selecting it, clicking its text, and then editing the text as you would in any other Windows program. However, take care when editing the Control Source property, which defines where the content of the text box comes from.

  28. Remove the label to the left of LastName by clicking it and then pressing the DELETE key.
  29. Select all the labels, but not their corresponding text boxes, by holding down the SHIFTkey as you click each of them. Then in the Properties dialog box, scroll down and set the Text Align property to Right.
  30. On the Format menu, point to Size, click To Fit to size the labels to fit their contents, and then click anywhere in the form, but outside the controls, to deselect them.
  31. g04ac07.gif


    TIP:
    The order in which you make formatting changes, such as the ones you just made, can have an impact on the results. If you don’t see the expected results, click the Undo button or press CTRL+Z to step back through your changes, and then try again.

  32. Hold down the SHIFT key while clicking each text box to select all the text boxes but not their corresponding labels, and in the Properties dialog box, change the Left setting to 1.5" to insert a little space between the labels and the text boxes.
  33. Change the Font Weight property to Normal, and then click anywhere in the form, but outside the controls, to deselect them.
  34. To change the background to one that better represents The Garden Company, click the down arrow to the right of the box at the top of the Properties dialog box, click Form, click the Picture property—which shows (bitmap)—and then click the … button to open the Insert Picture dialog box.
  35. Navigate to the My Documents\Microsoft Press\Access 2003 SBS\Forms\Properties folder, and double-click tgc_bkgrnd. (If you don’t see this file listed, change the Files of type setting to Graphics Files.)
  36. The form’s background changes, and the path to the graphic used for the new background is displayed in the Picture property box.

    g04ac08.gif

  37. Click the Save button to save the design of your Customers form.
  38. Save
    Save

  39. Close the form. (The Properties dialog box closes when you close the form.)

CLOSE the GardenCo database.

Refining Form Layout

Microsoft Office Specialist

The forms created by a wizard are functional, not fancy. However, it's fairly easy to customize the layout to suit your needs. You can add and delete labels, move both labels and text controls around the form, add logos and other graphics, and otherwise improve the layout of the form to make it attractive and easy to use.

As you work with a form's layout, it is important to pay attention to the shape of the pointer, which changes to indicate the manner in which you can change the selected item. Because a text box and its label sometimes act as a unit, you have to be careful to notice the pointer's shape before making any change. This table explains what action each shape indicates:

Pointer Shape Action
g04ac09.jpg Hand Drag to move both controls together, as one.
g04ac10.jpg Pointing finger Drag to move just the control.
g04ac11.jpg Vertical arrows Drag the top or bottom border to change the height.
g04ac12.jpg Horizontal arrows Drag the right or left border to change the width.
g04ac13.jpg Diagonal arrows Drag the corner to change both the height and width.

In this exercise, you will rearrange the label and text box controls in the Customers form to make them more closely fit the way people will work with them.

USE the GardenCo database in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Access 2003 SBS\Forms\Layout folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Access 2003 Step by Step.

OPEN the GardenCo database and acknowledge the safety warning, if necessary.

  1. Open the Customers form in Design view.
  2. If necessary, drag the lower-right corner of the Form window down and to the right until you can see the form footer at the bottom of the form and have an inch or so of blank area to the right of the background.
  3. g04ac14.jpg

    The form is divided into three sections: Form Header, Detail, and Form Footer. Only the Detail section currently has anything in it.

  4. Point to the right edge of the Detail background, and when the pointer changes to a two-way arrow, drag the edge of the background to the right until you can see about five full grid sections.
  5. Click the LastName text box, and then slowly move the pointer around its border, from black handle to black handle, noticing how it changes shape.
  6. Move the pointer over the LastName text box and when it changes to a hand, drag it up and to the right of the FirstName text box.
  7. One by one, select each control, resize it, and move it to the location shown in the following graphic. (Don’t worry if you don’t get everything aligned exactly as shown here.)
  8. g04ac15.gif


    TIP:
    To fine-tune the position of a control, click it and then hold down the CTRL key while pressing the appropriate arrow key to move the control in small increments. To fine-tune the size of a control, use the same process but hold down the SHIFT key.

  9. On the Format menu, click AutoFormat to display the AutoFormat dialog box.
  10. g04ac16.gif

  11. Click the Customize button to display the Customize AutoFormat dialog box.
  12. Click Create a new AutoFormat based on the Form 'Customers', and then click OK.
  13. New in Office 2003
    Support for Windows XP Theming


    TIP:
    Form controls inherit whatever theme is set in the operating system. To change the theme, open the Control Panel, click Display, click the Themes tab, select a new theme, and then click OK.

  14. In the New Style Name dialog box, type The Garden Company as the name of the new style, and then click OK.
  15. Back in the AutoFormat database, the new style appears in the Form AutoFormats list. From now on, this style will be available in any database you open on this computer.
  16. Click OK to close the AutoFormat dialog box.

  17. TIP:
    Access saves data automatically as you enter it, but layout changes to any object must be manually saved.

  18. Click the Save button.
  19. Save
    Save

  20. Close the form.

CLOSE the GardenCo database.

Adding Controls to a Form

Microsoft Office Specialist

Every form has three basic sections: Form Header, Detail, and Form Footer. When you use a wizard to create a form, the wizard adds a set of controls for each field that you select from the underlying table to the Detail section and leaves the Form Header and Form Footer sections blank. Because these sections are empty, Access collapses them, but you can size all the sections by dragging their selectors. Although labels and text box controls are perhaps the most common controls found in forms, you can also enhance your forms with many other types of controls. For example, you can add groups of option buttons, check boxes, and list boxes to present people with choices instead of making them type entries in text boxes.

The most popular controls are stored in the Toolbox. Clicking the More Controls button displays a list of all the other controls on your computer. The controls displayed when you click the More Controls button are not necessarily associated with Access or even with another Microsoft Office program. The list includes every control that any program has installed and registered on your computer.


IMPORTANT:
Some controls, such as the Calendar Control, can be very useful. Others might do nothing when you add them to a form, or might do something unexpected and not entirely pleasant. If you feel like experimenting, don’t do so in an important database.

In this exercise, you will add a graphic and a caption to the Form Header section of the Customers form from the GardenCo database. You will also replace the Country text box control in the Detail section with a combo box control.

USE the GardenCo database and the tgc_logo2 graphic in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Access 2003 SBS\Forms\Controls folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Access 2003 Step by Step.

OPEN the GardenCo database and acknowledge the safety warning, if necessary.

  1. Open the Customers form in Design view.
  2. Point to the horizontal line between the Form Header section selector and the Detail section selector, and when the pointer changes to a double-headed arrow, drag the Detail section selector down a little over an inch.
  3. g04ac17.jpg

  4. If the Toolbox isn’t displayed, click the Toolbox button on the Form Design toolbar.
  5. Toolbox
    Toolbox

    You can also open the View menu and select the Toolbox check box. To keep the Toolbox open but out of the way, you can drag it off to the side, and dock it on one edge of the screen.

  6. To get an idea of the controls that are available, move the pointer over the buttons in the Toolbox, pausing just long enough to display each button’s ScreenTip.
  7. Click the Image control in the Toolbox, and then drag a rectangle about 1 inch high and 3 inches wide at the left end of the Form Header section.
  8. Image
    Image

    When you release the mouse button, Access displays the Insert Picture dialog box, in which you can select an image to insert in the control.

  9. Navigate to the My Documents\Microsoft Press\Access 2003 SBS\Forms\Controls folder, and double-click tgc_logo2. (If you don’t see this file listed, change the Files of type setting to Graphics Files.)
  10. The Garden Company logo appears inside the image control.

    g04ac18.gif


    TIP:
    If the control isn’t large enough, the image is cropped. You can enlarge the control to display the entire image. (You might also have to enlarge the Form Header section.)

  11. To add a caption to the header, click the Label control in the Toolbox, and then drag another rectangle in the header section.
  12. Label
    Label

    Access inserts a label control containing the insertion point, ready for you to enter a caption.

  13. Type the caption Customers, and press ENTER.
  14. The Customers label takes on the formatting of the other labels.

  15. With the Customers label selected, press the F4 key to display the Properties dialog box.
  16. Change the Font Size property to 18, and change the Text Align property to Center. Then close the Properties dialog box.
  17. On the Format menu, point to Size, and then click To Fit.
  18. Adjust the size and position of the two controls you added so that they are side-by-side.
  19. g04ac19.gif

  20. If the Control Wizards button is active (orange) in the toolbox, click it to deactivate it.
  21. Control Wizards
    Control Wizards

    With the Control Wizards button turned off, you can create a control with all the default settings without having to work through the wizard’s pages.

  22. Insert a combo box in the Details section by clicking the Combo Box control in the Toolbox and then dragging a rectangle just below the current Country text box.
  23. Combo Box
    Combo Box

    When you release the mouse button, Access displays a combo box control, which is unbound (not attached to a field in the Customers table).


    TROUBLESHOOTING:
    Access provides a number for each control as it is created, so don’t be concerned if the number displayed in your control is different from what you see in the graphics in this book.

  24. Copy the formatting of the Country text box to the new combo box control by clicking the Country text box, clicking the Format Painter button on the Form Design toolbar, and then clicking the combo box control.
  25. Format Painter
    Format Painter

    Both the combo box control and its label take on the new formatting.

  26. Right-click the combo box and click Properties on the shortcut menu to display the Properties dialog box.
  27. Click the Data tab, set the Control Source property to Country, and then type the following in the Row Source box:
  28. SELECT DISTINCT Customers.Country FROM Customers;

    (Note that there is no space between Customers and Country; there is only a period. There is also a semi-colon at the end of the text.)

    This line is a query that extracts one example of every country in the Country field of the Customers table and displays the results as a list when you click the box’s down arrow.

    (You might have to widen the Properties dialog box to display the whole query.)

    g04ac20.gif


    TIP:
    If you need to add a new customer from a country that is not in the list, you can type the new country’s name in the combo box. After the record is added to the database, that country shows up when the combo box list is displayed.

  29. If necessary, set the Row Source Type to Table/Query.
  30. Click the label to the left of the combo box (if necessary, drag the Properties dialog box to see the combo box label). Then click the dialog box’s Format tab, change the caption to Country, and close the dialog box.
  31. Delete the original Country text box and its label, and move the new combo box and label into their place, resizing them as needed.
  32. Click the View button to see your form.
  33. View
    View

    g04ac21.gif

  34. Scroll through a couple of records, and display the combo box’s list to see how you can select a country.
  35. You don’t need the record selector—the gray bar along the left edge of the form—so return to Design view, and display the Properties dialog box for the entire form by clicking the Form selector (the box at the junction of the horizontal and vertical rulers) and pressing F4. Then on the Format tab, change Record Selectors to No. While you’re at it, change Scroll Bars to Neither. Then close the Properties dialog box.
  36. Save the form’s new design, and switch to Form view for a final look.
  37. Close the form.

CLOSE the GardenCo database.

Using Visual Basic for Applications to Enter Data in a Form

Microsoft Office Specialist

As you might have suspected by now, almost everything in Access, including the Access program itself, is an object. One of the characteristics of objects is that they can recognize and respond to events, which are essentially actions. Different objects recognize different events. The basic events, recognized by almost all objects, are Click, Double Click, Mouse Down, Mouse Move, and Mouse Up. Most objects recognize quite a few other events. A text control, for example, recognizes about 17 different events; a form recognizes more than 50.


TIP:
You can see the list of events recognized by an object by looking at the Event tab on the object’s Properties dialog box.

While you use a form, objects are signaling events, or firing events, almost constantly. However, unless you attach a macro or Microsoft Visual Basic for Applications (VBA) procedure to an event, the object is really just firing blanks. By default, Access doesn't do anything obvious when it recognizes most events. So without interfering with the program's normal behavior, you can use an event to specify what action should happen. You can even use an event to trigger the running of a macro or a VBA procedure that performs a set of actions.

Sound complicated? Well, it's true that events are not things most casual Access users tend to worry about. But because knowing how to handle events can greatly increase the efficiency of objects such as forms, you should take a glimpse at what they're all about while you have a form open.

For example, while looking at customer records in the GardenCo database, you might have noticed that the CustomerID is composed of the first three letters of the customer's last name and the first two letters of his or her first name, all in capital letters. This technique will usually generate a unique ID for a new customer. If you try to enter an ID that is already in use, Access won't accept the new entry, and you'll have to add a number or change the ID in some other way to make it unique. Performing trivial tasks, such as combining parts of two words and then converting the results to capital letters, is something a computer excels at. So rather than typing the ID for each new customer record that is added to The Garden Company's database, you can let VBA do it instead.

In this exercise, you will write a few lines of VBA code, and attach the code to the After Update event in the LastName text box in the Customers form. When you change the content of the text box and attempt to move somewhere else in the form, the Before Update event is fired. In response to that event, Access updates the record in the source table, and then the After Update event is fired. This is the event you are going to work with. This is by no means an in-depth treatment of VBA, but this exercise will give you a taste of VBA's power.

USE the GardenCo database and the AftUpdate text file in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Access 2003 SBS\Forms\Events folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Access 2003 Step by Step.

OPEN the GardenCo database and acknowledge the safety warning, if necessary.

  1. With Forms selected on the Objects bar, click Customers in the list of forms, and click the Design button.
  2. bia049

  3. Click the LastName text box to select it, and if necessary, press F4 to open the Properties dialog box.
  4. Click the Event tab to see the options.
  5. This tab lists the events to which the LastName text box control can respond to.

  6. Click After Update in the list, and then click the … button.
  7. g04ac22.gif

    The Choose Builder dialog box appears, offering you the options of building an expression, a macro, or VBA code.

  8. Click Code Builder, and then click OK to open the VBA Editor.
  9. g04ac23.gif

    The Project Explorer pane lists any objects you have created to which you can attach code; in this case, only the Customers form (Form_Customers) is listed. As you create more forms and reports, they will appear here.

    The Code window displays a placeholder for the procedure that Access will use to handle the After Update event for the LastName text control. This procedure is named Private Sub LastName_AfterUpdate(), and at the moment it contains only the Sub and End Sub statements that mark the beginning and end of any procedure.

  10. Launch a text editor, such as Microsoft Notepad, navigate to the My Documents \Microsoft Press\Access 2003 SBS\Forms\Events folder, open the AftUpdate practice file, and copy the following lines of text to the Clipboard. Then ALT+TAB back to the Code window and paste the text between the Private Sub LastName_AfterUpdate() and End Sub statements:
  11.      'Create variables to hold first and last names
       ' and customer ID
         Dim fName As String
         Dim lName As String
         Dim cID As String
    
         'Assign the text in the LastName text box to
         '   the lName variable.
         lName = Forms!customers!LastName.Text
    
         'You must set the focus to a text box before
         '   you can read its contents.
         Forms!customers!FirstName.SetFocus
         fName = Forms!customers!FirstName.Text
    
         'Combine portions of the last and first names
         '   to create the customer ID.
         cID = UCase(Left(lName, 3) & Left(fName, 2))
    
         ' Don't store the ID unless it is 5 characters long
         '   (which indicates both names filled in).
         If Len(cID) = 5 Then
             Forms!customers!CustomerID.SetFocus
    
             ' Don't change the ID if it has already been
             ' entered; perhaps it was changed manually.
             If Forms!customers!CustomerID.Text = "" Then
                Forms!customers!CustomerID = cID
             End If
         End If
    
         'Set the focus where it would have gone naturally.
         Forms!customers!Address.SetFocus


    IMPORTANT:
    When a line of text is preceded by an apostrophe, the text is a comment that explains the purpose of the next line of code. In the VBA Editor, comments are displayed in green.

  12. Save the file, click the View Microsoft Access button to return to the Access window, and then close the Properties dialog box.
  13. View Microsoft Access
    View Microsoft Access

  14. Switch to Form view and size the window as necessary. Then on the Navigation bar, click the New Record button to create a new record.
  15. New record
    New Record

  16. Press the TAB key to move the insertion point to the text box for the FirstName field, type John, press TAB to move to the text box for the LastName field, type Coake, and then press TAB again.
  17. If you entered the VBA code correctly, COAJO appears in the CustomerID text box.

  18. Change the first and last name to something else and notice that the CustomerID text box doesn’t change even if the names from which it was derived do change.
  19. Press the ESC key to remove your entry, and then try entering the last name first, followed by the first name.
  20. Access does not create a Customer ID. The code does what it was written to do but not necessarily what you want it to do, which is to create an ID regardless of the order in which the names are entered. There are several ways to fix this problem. You could write a similar procedure to handle the After Update event in the FirstName text box, or you could write one procedure to handle both events and then jump to it when either event occurs. You won’t do either in these exercises, but if you are interested, you can look at the code in the database file for the next exercise to see the second solution.

  21. Press ESC to clear your entries, and then close the Customers form.
  22. Press ALT+TAB to switch to the VBA Editor, which is still open, and close the editor.

CLOSE the GardenCo database.

Creating a Form by Using an AutoForm

Microsoft Office Specialist

Although a form doesn't have to include all the fields from a table, when it is used as the primary method of creating new records, it usually does include all of them. The quickest way to create a form that includes all the fields from one table is to use an AutoForm. And as with the forms created by a wizard, you can easily customize these forms.

In this exercise, you will create an AutoForm that displays information about each of the products carried by The Garden Company.

USE the GardenCo database in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Access 2003 SBS\Forms\AutoForm folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Access 2003 Step by Step.

OPEN the GardenCo database and acknowledge the safety warning, if necessary.

  1. On the Objects bar, click Forms.
  2. On the database window’s toolbar, click the New button to display this New Form dialog box, which lists all the ways you can create a form.
  3. bia151

    g04ac24.gif

  4. Click AutoForm: Columnar in the list of choices, click the down arrow to the right of the box at the bottom of the dialog box, click Categories, and then click OK.
  5. The dialog box closes, and after a moment a new Categories form is displayed in Form view.

  6. Click the Save button, accept the default name of Categories in the Save As dialog box, and click OK to view the form.
  7. Save
    Save

    g04ac25.gif


    TIP:
    When AutoForm creates a form, Access applies the background style you selected the last time you used the Form Wizard (or the default style, if you haven’t used the wizard). If your form doesn’t look like this one, switch to Design view, and on the Format menu, click AutoFormat. You can then select The Garden Company style from the list displayed.

  8. This form looks pretty good as it is, but switch to Design view so that you can make a few minor changes.
  9. Delete the word Category from the Category Name label.
  10. The CategoryID value is provided by Access and should never be changed, so you need to disable that text box control. Click the control and if necessary, press F4 to display the control’s Properties dialog box.
  11. On the Data tab, change Enabled to No, and close the dialog box.
  12. Disabling the CategoryID text box changes it, and the label text, to gray.

  13. Switch to Form view, and scroll through a few categories. Try to edit entries in the Category ID field to confirm that you can’t.
  14. You don’t need scroll bars or a record selector in this form, so return to Design view, and display the form’s Properties dialog box by clicking the Form selector and pressing F4. On the Format tab, change Scroll Bars to Neither and Record Selectors to No, and then close the dialog box.
  15. Save and close the Categories form.

CLOSE the GardenCo database.


Next


Last Updated: June 17, 2004
Top of Page

   Contact Us    Free Newsletters   
   © 2004 Microsoft Corporation. All rights reserved. Terms of Use.    Privacy Statement    Accessibility