Software

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!

About

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.

3 comments
KaKaKen
KaKaKen

Just getting more into the details and customization of the Office suite, Thanks for the great tips!

msdrafter84
msdrafter84

This is a great way to eliminate keystrokes. Some companies and non-programmers take for granted the extra prescience that goes into small workings such as this that make life a lot easier, that could have far-reaching effects, than initially determined on a project. This can be the difference between a good programmer and a great one, or making or breaking a deal, too. Ease of use: Keep it forefront.

ssharkins
ssharkins

We're fond of saying "know the data" -- but knowing how people will use the data and how people will maintain that date for the people who will use the data is just as important.