In this lesson we’ll cover some basic but important concepts: entering data, working with data, navigation, making data entry more efficient, and changing the appearance of your tables’ datasheet views.
Contributed by Sage Adams Rating: / 14 December 29, 2004
The easiest way to enter new data is to simply click the New Record toolbar button and start entering data into the record.
Alternatively you may use the Records | Data Entry menu option. This option opens a blank datasheet screen allowing you to enter new records without having to see all the records you have already entered.
When entering data you might also want to use the zoom tool. Press SHIFT-F2 to zoom in on any field you are currently working on; you’ll get a larger view of the field, with more room for input. This is especially useful when used with fields of memo data types.
Copying and pasting data is important in Access. You can copy and move large (or small) chunks of data between Office applications or within an Access table or tables. The clipboard is a useful tool for doing this. When you copy or cut an item in Access it goes to the clipboard – an area used by all Office applications. The clipboard can hold up to 24 items at any one time. Right-click to open the Clipboard to see all the items you have copied or cut recently. To paste any of these items to the datasheet at any time just insert your cursor where you wish to paste the data and select the clipboard item you wish to paste from the list; it will be pasted in the specified location.
You can also insert data other than text into your tables. For example you can enter OLE objects such as pictures, sounds, charts, videos, or hyperlinks. Your objects can be linked to an Access table or embedded within it. Both of these choices have advantages and disadvantages. When you link an object, the object remains in its source application; Access merely points to it. Using linking, your Access databases will not become much larger (which is good) but you will be required to restore links to the OLE objects whenever you move the database (annoying). When embedding objects, Access stores a copy of the object in the database and the object becomes portable with the database (good); of course it also makes the database file larger (bad).
A hyperlink, in Access, is an address that can contain up to four parts separated by the # sign as in the following:
Texttodisplay#address#subaddress#screentip
To enter a new hyperlink address into your table, place your cursor where you want to enter the hyperlink and choose the Insert | Hyperlink menu command. To edit or delete a hyperlink you must right-click the link and click Edit Hyperlink or Remove Hyperlink respectively from the menu.
To navigate among the records you have already created use the navigation buttons at the bottom of the Datasheet window. You use these to scroll forward or backward between records. Alternatively, you can also use the ‘Go To’ field box in the top-left of the toolbar to specify exactly which record you want to view.
Making Data Entry more Efficient
Access has two tools to make your data entry more efficient: input masks and lookup fields. We have already discussed these tools briefly in a previous lesson, but I’m going to give you more detail here about both of these tools because they are incredibly useful.
Input masks make fields that are fill in the blank instead of just empty. This can be especially useful when you include phone numbers and other formatted numbers in your tables, because it provides a consistent way to enter data. An input mask only controls how the data is entered however, not how it is displayed (see the format property for this – covered briefly in the last lesson). You can set both the format property and an input mask on any field to specify how the data is entered, and how it is displayed, respectively.
Use the Input Mask Wizard to create an input mask for text or date/time fields. To do so, put your insertion point in the field in which you’d like to have the input mask and set the Input Mask property in the properties box in Table Design view. Click the Build button (…) to the right of the drop-down arrow in the Input Mask property field. A dialog box appears which allows you to select a previously defined input mask. Select the input mask that makes the most sense to you and click ‘Next.’ In the second dialog box decide whether to make any changes to the input mask template; click ‘Next’ when you are done. In the third dialog box choose whether or not to include literal characters with the data – this takes up more disk space, but may be worth it in the long run, depending on the size of your tables. Click ‘Finish’ to complete the wizard.
You can also create your own mask, if you don’t want to use the wizard. Instead of clicking the Build button in the Input Mask property field, put your insertion point into the property field and just type the input mask ‘code.’ The programming for creating input masks is pretty easy but there are too many options and symbols to list here; I suggest you use the Access help feature to learn more about creating your own input masks.
The other tool you can use to make your data entry more efficient is Lookup Fields. There are two types of lookup fields: lookup lists and value lists. The difference is lookup lists get their values from fields in existing tables or queries, and value lists get their values from data you type in when you create the value list.
Add a lookup field to your table design by putting your insertion point in the new row in which you want to create the field, and select Lookup Wizard from the Data Type list. The Lookup Wizard gives you several dialog boxes allowing you to choose the details of the lookup field. Choose what type of lookup field to create and choose the values to put in the lookup field. If you choose to create your own list of values a second dialog box will prompt you for those values. If you choose to use a table or query as your source several other dialog boxes will prompt you to select the table or query to use and the fields to use. You will also be asked to name the primary key field and the lookup field itself. Finally you must decide how the columns will look.
Once you’ve created your lookup field you can manipulate the properties of that field in the properties box, in Table Design View, with the lookup field highlighted. Some of the more interesting options you are given include the following:
Display Control: determines the type of control implemented such as list box or combo box.
Row Source Type: specifies where your values come from; either a value list or a table or query.
Row Source: gives the exact fields that comprise the list.
List Rows: specifies how many rows to display at any one time.
List Width: specifies the width of the list.
Limit To List: keeps the values entered into the list to those specified when the list was created.
The datasheet view of your table shows columns and rows and looks similar to an Excel spreadsheet. As in Excel you can make changes to the colors, fonts, and cells of the datasheet. You may also hide certain fields from view or choose to keep a row or column of data visible, even while you scroll through other records, a feature known as freezing. Use the formatting toolbar to change colors and fonts. Freeze a column using the Format | Freeze Columns menu option. Make sure to put your insertion point somewhere in the column you wish to freeze. Unfreeze the columns using the Format | Unfreeze All Columns menu option. To hide a column from view use the Format | Hide Columns option and the Format | Show Columns option to see them again.
Choose Format | Datasheet to format the gridlines and the cell appearance. This will allow you to raise or sink cells, show or hide gridlines, change gridline color and background color, change the style of gridlines, or change the column display.
The final thing I’d like to discuss in this lesson is subdatasheets. Subdatasheets are datasheets contained within records in your main table that hold additional information about each record. To add a subdatasheet to a table or query choose the Insert | Subdatasheet menu command. A dialog opens up giving you three options: Tables, Queries, or Both. Pick the one you want to use for the subdatasheet and select the table or query name from the list. Choose the foreign key, the primary key, and add other subdatasheets if you like. You can remove a subdatasheet using the Format | Subdatasheet | Remove command.
Microsoft Access 
/ 14 
Using Masks to Block Bad Data
