Default Values
 

Introduction

When you create a Microsoft Access database, to assist the person doing data entry, you can provide a value for a certain field so the user would not need to change it if not necessary. This is referred to as the default value. To be efficient, the default value should be the most commonly used value for that particular field. For example, when creating a table of employees that has a field called Country, since employees are most likely to reside in one country, you can provide the name of the country as the default value.

To provide a default value, when creating the field in the Design View of the table (or form), you can access the Default Value property of the field and type the desired value.
 
  1. If you want to follow this example, create a database called Drug Control
  2. Use the Table Wizard to create a table based on the Employees table and select the following fields: EmployeeID, FirstName, LastName, HomePhone, Address, City, State, PostalCode, and Country
  3. From the Mailing List Sample Tables, add the following two fields: EmailAddress and Notes
  4. Click Next a few times and accept the name of the table as Employees then click Finish
  5. Switch the table to Design View
  6. Change the name of the PostalCode field to ZIPCode
     
  7. Set the Default Value of Country to USA
  8. Save and close the table
  9. Using AutoForm, generate a form for the Employees table and design it as you see fit:
     
  10. Save the form as Employees and switch it to Design View

Conditional Default Values

In some cases, you may want to provide a default value but the value would depend on another value. For example, if you are familiar with the system of telephone numbers in US and Canada, you may know that telephone numbers are defined using the following formula 
(000) 000-0000. They also follow certain rules:

  1. The first three digits must be unique in the country and they cover a certain area inside of a state or province. For example, at the time of this writing, 202 is used for Washington DC while 301 covers part of (southern) Maryland
  2. The rest of the combination (the second three digits followed by the last four digits) must by unique in the area covered by the first three digits. For example, a telephone number should be provided to only one entity such as one household or family 

Of course, there are other rules we will not care about at this time.

Based on this, when the user enters a telephone number, you may want to provide a default value for the state or province. To do this:

  1. While the form is in Design View, double-click the PhoneNumber text box to access its Properties window
  2. In the Properties window, click the Events tab. Right-click After Update and click Build...
  3. In the Choose Builder dialog box, click Code Builder and click OK
  4. Implement the AfterUpdate() event of the HomePhone text box as follows:
      
    Private Sub HomePhone_AfterUpdate()
    If Left(Me.HomePhone, 3) = "202" Then
        Me.State = "DC"
    ElseIf Left(Me.HomePhone, 3) = "301" Then
        Me.State = "MD"
    ElseIf Left(Me.HomePhone, 3) = "703" Then
        Me.State = "VA"
    End If
End Sub
  5. Return to Microsoft Access
  6. In the Home Phone text box, type 3016211106:
     

  7. When you press Enter or Tab, notice that the State text box gets filled with MD, acknowledging that a Maryland telephone number was entered
  8. Save the form and switch it back to Design View

Advanced Default Values

The default value offered by Microsoft Access can be used only in the Default Value property of a field and can be specified in the Design View of the table or the form. If you want to use a conditional default value as we have done above, you would think of writing an expression either in the Default Value of the field in the table Design View or better, the Record Source of the field in the form's Design View. Unfortunately, Microsoft Access doesn't acknowledge this. This is why you have to use VBA.

Even if you create a Text Box control and use it to hold the desired value. Once you write an expression in its Record Source or directly in one of its events in the Properties window, the user would not be allowed to enter any value in the field. This is one more reason VBA would be used.

The main advantage of using VBA for a default value is that you can still let the user change the value of a field even if you had provided your suggestion through a default value. For example, if you create a table for employees and decide to let the user assign a username or an Email Address to employees, imagine you create an expression as a default value that uses the employees last name preceded by the first letter of his or her first name, there is still a chance that the company would have Jeannine Walter and Jimmy Walter in the database. While it is impossible to predict all possible scenarios, which is the reason most (if not all) companies employ a person in charge of assigning usernames and/or email address, you can provide preliminary code that can assist the user. Once your expression has been applied, the user can still change your suggestion if it doesn't fit or should not be accepted.

  1. To create a suggested email address for a new employee, on the form, click the Last Name text box
  2. In the Properties window, right-click After Update and click Build...
  3. In the Choose Builder dialog box, double-click Code Builder and implement it as follows:
      
    Private Sub LastName_AfterUpdate()
    Me.EmailAddress = LCase(IIf(Me.FirstName = "", "", Left(FirstName, 1)) _
                      & Me.LastName) & "@drugcontrol.md.us"
End Sub
  4. Return to Microsoft Access. Click First Name, type Germaine and press Enter. Click Last Name, type Dufois
  5. Press Enter. Notice that the Email Address text box gets filled with gdufois@drugcontrol.md.us
  6. To modify the suggested email address, change the string in the Email Address text box to gdufois1244@stopdrugus.net and press Enter
  7. Notice that you are able to change the default value
  8. To create more elaborate or other default values, return to Visual Basic and change the After Update event of the HomePhone field as follows:
      
    Private Sub HomePhone_AfterUpdate()
    If Left(Me.HomePhone, 3) = "202" Then
        Me.State = "DC"
    ElseIf Left(Me.HomePhone, 3) = "301" Or Left(Me.HomePhone, 3) = "240" _
            Or Left(Me.HomePhone, 3) = "410" Or Left(Me.HomePhone, 3) = "443" Then
        Me.State = "MD"
    ElseIf Left(Me.HomePhone, 3) = "703" Or Left(Me.HomePhone, 3) = "540" _
            Or Left(Me.HomePhone, 3) = "804" Or Left(Me.HomePhone, 3) = "434" Then
        Me.State = "VA"
    End If
    
    If Left(Me.HomePhone, 3) = "202" Then
        Me.City = "Washington"
    ElseIf Left(Me.HomePhone, 3) = "240" Then
        Me.City = "Silver Spring"
    ElseIf Left(Me.HomePhone, 3) = "410" Or Left(Me.HomePhone, 3) = "443" Then
        Me.City = "Baltimore"
    ElseIf Left(Me.HomePhone, 3) = "434" Then
        Me.City = "Charlottesville"
    End If
End Sub
  9. Close Visual Basic and test the form
 
Home Copyright © 2003-2007 FunctionX, Inc. FunctionX