As a developer, you wear many hats. Producing efficient solutions should be high on your list of priorities. Sometimes that means reducing the number of keystrokes a user must make. One of the most common ways to reduce data input in Microsoft Access is to provide a default value — the most common value –for input controls. That way, when the value equals the default value, the user simply bypasses the control altogether. Occasionally, the default value becomes irrelevant as data changes. When that happens, you need a more versatile solution — one that allows the data to determine a control’s default value.

A contrary solution

It seems contrary to the nature of a default value to dynamically change it, but don’t let that stop you. Often, the normalized data will take care of repetitive values, but not always. The data entry form in Figure A is based on the Employees table in Northwind, the sample database that comes with Access. (Use a Form Wizard to create the example form.)

Figure A

This simple data entry form has several controls that could contain repetitive values.

If most of your employees are in the same city or ZIP code, you can set a default value that doesn’t change. However, let’s suppose this company hires many employees on any given day. That means the hire date will remain the same for several records. You might think you have no choice but to force users to enter and then re-enter the date of hire for each new employee. Fortunately for you, and your users, that’s not the case. Instead, using just one line of Visual Basic for Applications (VBA) code, you can update that control’s Default Value property each time the value changes.

With the data entry form in Form view, launch the form’s module (click the Code button or press Alt+F11) and enter the event procedure shown in Listing A.

Listing A

Private Sub txtHireDate_AfterUpdate()

'Change HireDate's default value


'when value changes.


txtHireDate.DefaultValue = "#" & txtHireDate & "#"

End Sub

It’s a good idea to rename bound controls if you plan to use code to manipulate them. Otherwise, VBA has trouble knowing whether you mean the control or the underlying field. Most of the time VBA can work things out, but occasionally, a function, especially an aggregate function, will fail to evaluate correctly.

Return to Access and view the form in Form view. Enter a new employee record, as shown in Figure B. When the hire date value is new, the control’s After Update event sets the underlying field’s Default Value (at the table level) to the current value. The user simply tabs pass the control allowing the form to fill in the date value until the user changes it, as shown in Figure C.

Figure B

Entering a hire date sets the control’s Default Value property to the newly entered date.

Figure C

Enter a new date to update the control’s Default Value property.

When applying this simple technique to your own forms, remember that the form stores the default value only for the current work session. It won’t permanently update a control’s Default Value property. In addition, the pound sign (#) delimits the Date/Time data type. Use single quotation marks (‘) when the value is text; numeric values don’t require a delimiter. Be sure to test the code thoroughly and add error-handling routines as necessary.

Dynamic default means less data entry

Strictly speaking, normalized data handles repetitive values, but not always. When the underlying structure isn’t there, for whatever reason, try a dynamic default value to reduce data entry keystrokes. It doesn’t seem like a lot to you, but your users will appreciate the effort. Re-entering the same values record after record is tedious, annoying, and unnecessary. Fortunately for you, the solution is quick to implement.