Office solution: How to use the current input value as a control's default value in an Access form

This week, learn the solution to last week's Office challenge: How to use input values as default values in an Access form.

Last week, we asked you to simplify a data entry task by using the current input value as the control's default value for subsequent records.  Cmelhem was the first to share my preferred solution - use VBA to set the control's Default Value property to the current input value. Doing so is simple. Just add the following code to the control's After Update event:

Me!control.DefaultValue = Me!control.Value

where control identifies the specific control by name. You'll also need to consider delimiters. For example, the following two procedures would accomplish this task for a text and numeric value, respectively:

Private Sub txtRegion_AfterUpdate()

'Use current input value as default value

'for subsequent records.

Me!txtRegion.DefaultValue = Chr(39) & Me!txtRegion.Value & Chr(39)

End Sub

Private Sub txtSalespersonID_AfterUpdate()

'Use current input value as default value

'for subsequent records.

Me!txtSalespersonID.DefaultValue = Me!txtSalespersonID.Value

End Sub

In the first procedure, the Chr(39) functions return a single quote to delimit the current text input value. To delimit a date or time value, use Chr(35) to return the pound sign (#). Numeric values don't require a delimiter. Sirtimtoo offered the same solution and even included the code for us!

The After Update value sets the control's default value property to the current input value. For instance,  if the user enters 123 as the salesperson's id and Texas for the region, the next new record will use those values as defaults, and your user can skip those controls during the input process for the new record. The form will continue to use these default values until your user changes one or both. If the values are no longer accurate, the user simply changes them. This particular solution is good for batch input chores where several values will remain the same for several records.

Thanks to everyone for the great suggestions!


Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks