Saturday, May 26, 2018

Set default values for fields or controls

Set default values for fields or controls

This article explains how to set a default value for a table field or for a control on a form in an Access database. The default values that you set will appear in the field or control whenever you create a new record in your database.

In this article

Understand default values

Set a default value for a table field

Set a default value for a control

Set a default row for a list box or combo box

Examples of default values

Understand default values

You add a default value to a table field or form control whenever you want Access to enter a value in a new record automatically. For example, you can have Access always add the current date to new orders.

Typically, you add a default value to your table fields. You add the value by opening the table in Design view and then entering a value in the Default Value property for the field. If you set a default value for a table field, Access applies your value to any controls that you base on that field. If you don't bind a control to a table field, or you link to data in other tables, you set a default value for your form controls itself.

You can set a default value for table fields set to the Text, Memo, Number, Date/Time, Currency, Yes/No, and Hyperlink data types. If you don't supply a value, the field remains null (blank) until you enter a value. After you define a default value, Access applies that value to any new records that you add. If you want, you can change the value in a record from the default value to another value, unless a validation rule prohibits this.

Top of Page

Set a default value for a table field

When you set a default value for a table field, any controls that you bind to that field will display the default value.

Set a default value

  1. In the Navigation Pane, right-click the table that you want to change, and then click Design View.

  2. Select the field that you want to change.

  3. On the General tab, type a value in the Default Value property box.

    The value you that you can enter depends on the data type that is set for the field. For example, you can type =Date() to insert the current date in a Date/Time field. For examples of default values, see Examples of default values, later in this article.

  4. Save your changes.

Top of Page

Set a default value for a control

Typically, you set a default value for a control only when you don't bind that control to a table field, or when you link to data in another table.

Set a default value

  1. In the Navigation Pane, right-click the form that you want to change, and then click Design View.

  2. Right-click the control that you want to change, and then click Properties or press F4.

  3. Click the All tab in the property sheet, locate the Default Value property, and then enter your default value.

  4. Press CTRL+S to save your changes.

Top of Page

Set a default row for a list box or combo box

By default, list box and combo box controls display two types of lists: value lists and lookup lists. A value list is a hard-coded list of items that resides in the Row Source property of a list box or combo box control. In contrast, a lookup list takes its data from a lookup field (a field that uses a query to retrieve data from another table), and then loads that data into a combo box control.

For value lists and lookup lists, you can specify which list item appears by default, but you follow a different procedure for each type of list. The following steps explain how to set default values for a value list and a lookup list.

Set a default for a value list

  1. In the Navigation Pane, right-click the form that you want to change, and then click Design View.

  2. Right-click the list box or text box control, and then click Properties or press F4.

  3. Ensure that the Row Source property contains a value list. The items in a value list are surrounded by double quotation marks and separated by semicolons. For example, you might see a list like this: "Good";"Fair";"Poor".

  4. If the Row Source property contains a query (a string of text that begins with SELECT), go to the next set of steps.

  5. In the Default Value property box, type the following:

    [control_name].ItemData(n)

    In this case, control_name is the name of the list box or combo box control, and n is the number of the list item that you want to make the default. If you don't know the name of the control, look at the value in the Name property, located at the top of the property sheet.

    For example, suppose you have a combo box control named Owner, and you want to use the third value in the list as the default value. You type the following in the Default Value property box:

    [Owner].ItemData(2)

    You type 2 instead of 3 because ItemData is zero-based, meaning it starts counting at zero, not one.

  6. Save your changes, and then switch back to Form view. Your selected default value should appear in the list or combo box when you add a record to your database.

Set a default value for a lookup list

  1. Open the table that provides the source data for your lookup field in Datasheet view.

  2. Note the key value that is associated with the item that you want to make the default value.

    Note: By default, Access hides primary and foreign key fields. If you don't see the key field, right-click any cell in the header row of the table, click Unhide Columns, and in the Unhide Columns dialog box, select your key field and click Close.

  3. In the Navigation Pane, right-click the table that contains your lookup field, and then click Design View.

  4. Select the lookup field, and on the General tab, in the Default Value property box, type the key value that you noted in steps 1 and 2. This is the value that corresponds to the list item that you want to make the default.

    For example, suppose you have 10 suppliers, and you want the name of your most-used supplier to appear by default. To do so, you locate the key value that uniquely identifies the supplier, and you enter that key value in the Default Value property box of the foreign key field.

  5. Save your changes, switch to Datasheet view, and enter a new record. Your default value appears in your lookup field when you add the record.

Top of Page

Examples of default values

The following table lists and describes some default values. You can adapt these examples for use with your own data.

Expression

Default field value

1

1

"MT"

MT

"New York, N.Y."

New York, N.Y. (note that you must enclose the value in quotes if it includes punctuation)

""

A Zero-length string

Date( )

Today's date

=Yes

"Yes" is displayed in the local language of the computer

Top of Page

No comments:

Post a Comment