Use a temporary default value to streamline data entry in Access

You can set a default value for fields, saving data entry personnel a few keystrokes. Setting a temporary default value takes the idea even further.

There are a lot of opportunities for reducing data entry, but here's one you might not have considered - entering temporary default values. Doing so will reduce keystrokes when records share the same value such as the same zip code, the same city, the same customer, and so on, but that shared value changes from time to time.

This situation probably arises more than you realize. For instance, suppose a data entry operator enters orders processed by sales personnel who support specific ZIP codes, cities, or regions. The data entry operator knows that each order in a specific pile will have the same ZIP code, city, and so on. Or, perhaps your data entry operator receives piles of work order forms from service managers who service only one company. In that case, every form in the pile will share the same customer value.

When a data entry operator enters several records with the same value one after the other, you can ease the data entry burden just a bit, by making that related value the default value for that field--temporarily. That way, the operator doesn't have to re-enter the value for each new record - it's already there!

TechRepublic's Microsoft Office Suite newsletter, delivered every Wednesday, is designed to help your users get the most from Word, Excel, and Access. Automatically sign up today!

Setting up this solution is easier than you might think—it takes just a bit of code in the control's AfterUpdate event. Using the example form below, we'll use this technique to create temporary defaults for three controls named txtCustomer, txtCreatedBy, and dteSubmittedDate. At the table level, the SubmittedDate field's default value is Date(). (You can work with most any form, just be sure to update the control names accordingly.)

To add the event procedures for the three controls, open the form in Design View and then click the Code button in the Tools group to open the form's module. Enter the following code:

Private Sub dteSubmittedDate_AfterUpdate(Cancel As Integer)
  'Set current date value to default value.
  dteSubmittedDate.DefaultValue = Chr(35) & dteSubmittedDate.Value & Chr(35)
  Debug.Print dteSubmittedDate.DefaultValue
End Sub
Private Sub txtCreatedBy_AfterUpdate(Cancel As Integer)
  'Set current value to default value.
  txtCreatedBy.DefaultValue = Chr(34) & txtCreatedBy.Value & Chr(34)
End Sub
Private Sub txtCustomer_AfterUpdate(Cancel As Integer)
  'Set current customer value to default value.
  txtCustomer.DefaultValue = Chr(34) & txtCustomer.Value & Chr(34)
End Sub

When you open the form in Form view, the AutoNumber field will display (New) and the Submitted Date control will display the current date. Enter a new record. Doing so will trigger the AfterUpdate events, which will use the values you enter as the default values for the corresponding controls:

  • ABC, International is now the default value for txtCustomer.
  • Susan Harkins is now the default value for the txtCreatedBy.
  • 1/20/2011 is now the default value for the dteSubmittedDate. The default value was generated by Date().

When you click the New Record button, the newly-set default values automatically fill in the controls. The only value the data entry operator has to enter is the service code.

That means that the data entry operator can bypass three controls for each new record until until a value changes. For instance, when the data entry operator moves on to the stack of order forms for RabbitTracks, he or she will update the Customer, CreatedBy, and SubmittedDate value for the first record in that batch. Doing so will reset the temporary-default values. That's why this is such a useful technique for batch input—as the operator works through the pile of forms, the default values update to match the new input values, automatically.

It's important to remember that this code updates the control's default value property at the form level. This form-level setting takes precedence over a table-level equivalent. However, it does not overwrite the table property. If you delete the form-level setting, the table-level property kicks right in.

When you close the form, it saves the temporary default value. Consequently, when you next open the form, it will use the last set of default values. If you want the form to clear these properties from session to session, add the following code to the form's module:

Private Sub Form_Open(Cancel As Integer)
  'Set Default Value properties to nothing.
  dteSubmittedDate.DefaultValue = vbNullString
  txtCreatedBy.DefaultValue = vbNullString
  txtCustomer.DefaultValue = vbNullString
End Sub
When you open the form, the Open event will clear the three previously-set default values. That means that txtCustomer and txtCreatedBy will be blank and dteSubmittedDate will display the current date (the result of Date(), the field's table-level Default Value setting).

This technique might not seem like much to you. But, some users spend a lot of time entering data, so anything you can do to eliminate even a few steps will be a welcome enhancement.