Adding Records and Changing Data

You’ll probably design most forms so that you can insert new records, change field values, or delete records in Form view or in Datasheet view. The following sections explain procedures for adding new records and changing data.

Adding a New Record

The procedure for entering a new record varies depending on the design of the form. With a form that’s been designed for data entry only, you open the form and enter data in the (usually empty) data fields. Sometimes forms of this type open with default values in the fields or with data entered by a macro or Visual Basic procedure. In the Conrad Systems Contacts application, frmCompanyAdd and frmContactAdd are two examples of forms that open in Data Entry mode. You can see frmContactAdd in Figure 10–26.

Figure 10–26: The frmContactAdd form opens in Data Entry mode.

When you’re editing company information in frmCompanies and need to enter a new contact that doesn’t exist, the application opens this form to allow you to fill in the required information for the new contact. After you save the record, the new contact becomes available to assign to the company displayed in frmCompanies.

You’ll normally create a form that allows you to display and edit data and also add new records. The frmContactsPlain form is this type of form. On this form, you can go to a new record in several ways. Open the frmContactsPlain form and try the following:

• Click the Last Record button on the navigation bar at the bottom of the Form window and then click the Next Record button.

• Click the New Record button on the navigation bar at the bottom of the form.

• Click Go To in the Find group on the Home tab and then click New on the submenu.

• Click the New button in the Records group on the Home tab.

• Press Ctrl+Plus Sign.

• Click the Add New command button in the header area of the form. (This last method puts the form into Data Entry mode, something you cannot do from any standard command on the Ribbon.)

The first five methods take you to the empty record at the end of the recordset being edited by this form. This is similar to going to the blank row at the end of a table or query datasheet to begin entering data for a new row. The last method shifts the form into Data Entry mode as shown in Figure 10–27. Notice that there now appears to be only one record-the new record you’re about to enter, and that record displays the default value specified for Country even though you haven’t started to enter any data yet.

Figure 10–27: When you click the Add New button in the header of the frmContactsPlain form, Access displays the form in Data Entry mode.

Access places the insertion point in the first field when you start a new record. As soon as you begin typing, Access changes the indicator on the record selector (if your form shows the record selector) to a pencil icon to indicate that updates are in progress. Press Tab to move to the next field.

Note 

The frmContactsPlain form provides an Edit All button on the form to return to normal data display if you clicked the Add New button to enter Data Entry mode.

If you violate a field’s validation rule, Access notifies you as soon as you attempt to leave the field. You must provide a correct value before you can move to another field. Press Shift+Enter in any field in the record or press Tab in the last field in the record to save your new record in the table. If the data you enter violates a table validation rule, Access displays an error message and does not save the record. If you want to cancel a new record, press Esc twice. (There’s also a Cancel button on frmContactsPlain that clears your edits and closes the form.)

If you’re adding a new record in a form that has an Attachment or OLE Object data type, you’ll encounter a special situation. You’ll notice when you tab to the Photo attachment control that you can’t type anything in it. This is because the field in the underlying table is an Attachment data type, and this control is an attachment. In fact, when the focus is in this control and you happen to type one of the keyboard navigation letters on one of the command buttons (C for Close, (M for Cancel, and so on), you’ll “click” that command button and execute the action programmed in the command button. You’ll notice this same problem if you open the frmContactsPlain form in the Contacts2Upsize.accdb database and tab into the Photo bound object frame control.

To enter data in this type of field in a new record, you must create the object in another application before you can store the data in Access. To insert a file in an attachment field, follow the instructions given earlier to right-click the attachment control and click Manage Attachments on the shortcut menu. Click Add in the Attachments dialog box shown in Figure 10–17 on page 532 to open the Choose File dialog box where you can select the file that you want to store in the attachment field.

If you want to see how to work with an OLE Object field, you’ll have to close the Contacts.accdb database and reopen the Contacts2Upsize.accdb database. Open the frmContactsPlain form and go to a new record. To create and store a new file in a bound object frame, right-click on the bound object frame control and click the Insert Object command on the shortcut menu. Access displays the Microsoft Office Access dialog box, shown in Figure 10–28. To create a new object, select the object type you want (in this case, Bitmap Image), and click OK. Access starts the application that’s defined in the Windows registry as the default application for this type of data (for bitmaps, usually the Paint application).

Figure 10–28: The Microsoft Office Access dialog box allows you to enter data into an OLE object field.

If you have an appropriate file available to copy into the OLE object field in Access, select the Create From File option in the Microsoft Office Access dialog box. Access replaces the Object Type list with a File box where you can enter the path name and file name, as shown in Figure 10–29. You can click the Browse button to open the Browse dialog box, which lets you search for the file you want. After you select a file, you can select the Link check box to create an active link between the copy of the object in Access and the actual file. If you do so, whenever you change the file, the linked object in Access will also change. Select the Display As Icon check box to display the application icon instead of the picture in the bound object frame. Your picture will still be stored or linked in your table even when you choose to display the icon.

Figure 10–29: You can insert an object from a file using the Create From File option in the Microsoft Office Access dialog box.

If you opened the Contacts2Upsize.accdb database to experiment with editing in a bound object frame, close it and reopen the Contacts.accdb database.

The frmContactsPlain form also includes two text boxes that let you specify the e-mail address or the Web site address of the contact. To add or edit a hyperlink, you can tab to the hyperlink field (remember that if the link field contains a valid link, clicking in it activates the link!), right-click the link field, click Hyperlink on the shortcut menu, and then click Edit Hyperlink on the submenu. Access displays the dialog box shown in Figure 10–30, which lets you edit or define the link.

You can enter the descriptor in the Text To Display box at the top. We clicked the ScreenTip button to open the Set Hyperlink ScreenTip dialog box you see in Figure 10–30. The ScreenTip appears when you rest your mouse pointer on the hyperlink. You can type the document address directly into the Address box.

Figure 10–30: The Insert Hyperlink dialog box shows a link to the AccessJunkie.com Web site.

The second option on the left in the Insert Hyperlink dialog box, E-Mail Address, lets you enter an e-mail address or choose from a list of recently used addresses. This generates a Mailto: hyperlink that will invoke your e-mail program and start a new e-mail message to the address you specify here. You can also optionally enter Subject text for the new e-mail, which adds a question mark after the e-mail address followed by the subject in the stored hyperlink.

Click OK to save your link. See “Working with Hyperlinks” on page 397, for more details and cautions about hyperlinks.

Try adding a new record by using the frmContactsPlain form. Open the form, click the Go To command in the Find group on the Home tab, and then click New. You should see a form similar to the one shown earlier in Figure 10–27. Make any selection you like from the Contact Type drop-down list. Because this field is a Multi-Value Lookup Field, you can select more than one contact type. Click the OK button at the bottom of the list when you have finished selecting one or more contact types. You must enter at least a last name (the only required field in tblContacts). Tab to the Photo field, and follow the procedure discussed previously in “Attachment Controls” on page 530 to add a new picture attachment to this record. You can find several appropriately sized bitmap pictures of contacts on the companion CD in the Pictures subfolder.

To begin adding some events for your new contact, click the Events tab to reveal the appropriate subform. Note that when you click in the subform, Access saves the contact data you entered in the main form. Access does this to ensure that it can create a link between the new record in the main form and any record you might create in the subform. (The new contact ID has to be saved in the main form before you can create related contact category records in a subform.)

Select an event type, as shown in Figure 10–31, or type a new one. As soon as you select an event type, Access automatically fills in the Date/Time field with the current date and time on your computer. You can correct this value or click the small calendar button to open a calendar form to set a new date and time graphically. If you enter an event type that isn’t already defined, code behind the form prompts you to ask whether you want to add a new event type. If you click Yes, you’ll see a dialog form open to allow you to enter the details for your new event type.

When you press Tab in the last field or press Shift+Enter in any field, Access adds the new event for you. Access also inserts the information required to link the record in the main form and the new record in the subform. Access fetches the ContactID from the record in the outer form and creates the new record in the tblContactEvents table with the related ContactID value. You can’t see or edit the ContactID field on either the outer form or the subform. You don’t need to see it in the outer form because ContactID in tblContacts is an AutoNumber field. Because Access automatically copies the value for you for new rows in the subform, you don’t need to see it there either.

Inside Out-Dealing with AutoNumber Primary Keys and Potentially Duplicate Data 

As discussed in Article -1 on the companion CD, “Designing Your Database Application,” it’s usually preferable to use a combination of data fields that have a unique value in each row to create a primary key. However, choosing a combination of fields in tblContacts that would be guaranteed to be unique in all rows could prove difficult. The combination of first name and last name could easily result in a duplicate when two different people have the same name. Adding postal code might help, but you still might run into two people named John Smith who live in the same area.

The simple solution for tables in an Access 2007 desktop database is to use an AutoNumber field as the primary key. In fact, in the Conrad Systems Contacts database, tblCompanies, tblContacts, tblInvoices, and tblProducts all use an AutoNumber field for the primary key. This guarantees that all rows will have a unique primary key, but it doesn’t guard against duplicate records-entering the same person twice.

To avoid potential duplicates, you should consider writing Visual Basic code to check a new row just before Access saves it. We included some simple code to perform a Soundex check on the last name in both frmContacts and frmContactsPlain. (Soundex is an algorithm created by the United States National Archive and Records Administration to generate a code from a name to identify names that sound alike.) If you try to add a person in a new row with the last name Camred, you’ll see a warning about a potential duplicate (Conrad, for example) and a list of all similar names. The warning allows you to cancel saving the new row. You can learn how this code works in Chapter 20.

One last point about using AutoNumber: As soon as you begin to enter new data in a table that has an AutoNumber field, Access assigns a new number to that field. If you decide to cancel the new record before saving it, Access won’t reuse this AutoNumber value. Access does this to ensure that multiple users sharing a database don’t get the same value for a new table row. So, if you want primary key numbers to remain consecutive, you should not use AutoNumber.

You can define very functional forms using combo boxes, tab controls, and subforms. To really make your application user-friendly, you need to further automate your forms with Visual Basic. For details about how many of the forms in the sample databases are automated with Visual Basic, see Chapter 20.

Figure 10–31: You can add a new contact event record on the Events tab in the frmContactsPlain form.

Changing and Deleting Data

If your form permits updates, you can easily change or delete existing data in the underlying table or query. If you design the form to be used in Datasheet view, you can use the same techniques you learned in Chapter 7, “Creating and Working with Simple Queries,” to work with your data.

In Form view, your data might appear in one of several formats. If you design the form as a single form, you can see the data for only one record at a time. If you design the form as a continuous form, you might be able to see data for more than one record at a time.

As with datasheets, you must select a field in the form in order to change the data in the field. To select a field, either tab to the field or click in the field with the mouse. (Remember, if the field contains a hyperlink, clicking in it will activate the link. To edit a hyperlink, either tab to the field or right-click the field to open the shortcut menu, from which you can click commands to edit the hyperlink.) After you select a field, you can change the data in it by using the same techniques you used for working with data in a datasheet. You can type over individual characters, replace a sequence of characters, or copy and paste data from one field to another.

You might find that you can’t tab to or select some fields in a form. When you design a form, you can set the properties of the controls on the form so that a user can’t select the control. These properties prevent users from changing fields that you don’t want updated, such as calculated values or fields from the one side of a query. You can also set the tab order to control the sequence of field selection when you use Tab or Shift+Tab to move around on the form. See Chapter 12, “Customizing a Form,” for details.

Deleting a record in a single form or in a continuous form is different from deleting a record in a datasheet. First, you must select the record as you would select a record in a datasheet. If the form is designed with record selectors, simply click the record selector to select the record. If the form does not have record selectors, click the Select command in the Find group on the Home tab, and then click Select on the menu. To delete a selected record, press the Delete key or click the arrow next to the Delete command in the Records group on the Home tab, and then click Delete Record. You can also click the Delete Record command to delete the current record without first having to select it.

When a record you’re trying to delete contains related records in other tables, you will see an error message unless the relationship defined between the tables tells Access to cascade delete the related fields and records. See Chapter 4, for details about defining relationships between tables. In frmContacts and frmContactsPlain in the Conrad Systems Contacts application, Visual Basic code behind the forms checks to see if dependent rows exist in other tables. This code issues a custom error message, shown in Figure 10–32, that gives you specific information about the problem. (The standard Access error message is not very user-friendly.) Rather than automatically delete dependent records (you might have asked to delete the contact record in error), the application requires you to specifically go to the tabs that show the related records and delete all these records first. You can see how this code works in Chapter 24.

Figure 10–32: The Conrad Systems Contacts application shows you a custom error message when you attempt to delete a contact that has dependent records in other tables.