Provided by Allen Browne, email@example.com. Created May 2006. Updated April 2010. (An older, less powerful version of this tip is archived here.)
Assign default values from the last record
Sometimes you need to design a form where many fields will have similar values to the last record entered, so you can expedite data entry if all controls carry data over. There are two ways to achieve this:
Set the Default Value of each control so they offer the same value as soon as you move into the new record.
Use the BeforeInsert event of the form so they all inherit the same values as soon as the user starts typing in the new record.
The first is best suited to setting a particular field. Dev Ashish explains the process here: Carry current value of a control to new records.
This article takes the second approach, which has these advantages:
Since the new record is blank until the first keystroke, the user is not confused about whether this is a new or existing record.
Values are inserted even for the first entry after the form is opened (assuming there are records.)
The code is generic (does not need to refer to each control by name), so can be reused for any form.
The default value is not applied to the control that the user is trying to type into when they start the new record.
Note: The code works with Access 2007 and later if the form does not contain controls bound to multi-valued fields (including Attachment.)
To implement this tip in your form:
Open a new module.
In Access 95 - 2003, click the Modules tab of the Database window and click New.
In Access 2007 and later, click the Create ribbon, drop-down the right-most icon in the Other group and choose Module.
Copy the code below, and paste into the new module.
Verify that Access understands the code by choosing Compile from the Debug menu.
Save it with a name such as Module1. Close the code window.
Open your form in design view.
Open the Properties sheet, making sure you are looking at the properties of the Form (not those of a text box.)
On the Event tab of the Properties box, set the Before Insert property to:
Click the Build button (...) beside this Property. Access opens the code window.
Set up the code like this:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strMsg As String
Call CarryOver(Me, strMsg)
If strMsg <> vbNullString Then
MsgBox strMsg, vbInformation
Repeat steps 5 - 9 for any other forms.
If there are specific fields you do not wish to carry over, add the name of the controls in quotes inside the brackets, with commas between them. For example to leave the Notes and EmployeeID fields blank, use:
Call CarryOver(Me, strMsg, "Notes", "EmployeeID")
The code is intelligent enough not to try to duplicate your AutoNumber or calculated fields, so you do not need to explicitly exclude those. Similarly, if the form is a subform, any fields named in LinkChildFields will be the same as the record we are copying from, so you do not need to explicitly exclude those either.
If you do not wish to see any error messages, you could just set the Before Insert property of the form to:
Here is the code for the generic module (Step 2 above.)
Public Function CarryOver(frm As Form, strErrMsg As String, ParamArray avarExceptionList()) As Long
On Error GoTo Err_Handler
Dim rs As DAO.Recordset
Dim ctl As Control
Dim strForm As String
Dim strControl As String
Dim strActiveControl As String
Dim strControlSource As String
Dim lngI As Long
Dim lngLBound As Long
Dim lngUBound As Long
Dim bCancel As Boolean
Dim bSkip As Boolean
Dim lngKt As Long
strForm = frm.Name
strActiveControl = frm.ActiveControl.Name
lngLBound = LBound(avarExceptionList)
lngUBound = UBound(avarExceptionList)
If Not frm.NewRecord Then
bCancel = True
strErrMsg = strErrMsg & "Cannot carry values over. Form '" & strForm & "' is not at a new record." & vbCrLf
If Not bCancel Then
Set rs = frm.RecordsetClone
If rs.RecordCount <= 0& Then
bCancel = True
strErrMsg = strErrMsg & "Cannot carry values over. Form '" & strForm & "' has no recrods." & vbCrLf
If Not bCancel Then
For Each ctl In frm.Controls
bSkip = False
strControl = ctl.Name
If (strControl <> strActiveControl) And HasProperty(ctl, "ControlSource") Then
For lngI = lngLBound To lngUBound
If avarExceptionList(lngI) = strControl Then
bSkip = True
If Not bSkip Then
strControlSource = ctl.ControlSource
If (strControlSource <> vbNullString) And Not (strControlSource Like "=*") Then
If (.SourceTable <> vbNullString) And ((.Attributes And dbAutoIncrField) = 0&) _
And Not (IsCalcTableField(rs(strControlSource)) Or IsNull(.Value)) Then
If ctl.Value = .Value Then
ctl.Value = .Value
lngKt = lngKt + 1&
CarryOver = lngKt
Set rs = Nothing
strErrMsg = strErrMsg & Err.Description & vbCrLf
Private Function IsCalcTableField(fld As DAO.Field) As Boolean
On Error GoTo ExitHandler
Dim strExpr As String
strExpr = fld.Properties("Expression")
If strExpr <> vbNullString Then
IsCalcTableField = True
Public Function HasProperty(obj As Object, strPropName As String) As Boolean
Dim varDummy As Variant
On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
How it works
You can use the code without understanding how it works, but the point of this website is help you understand how to use Access.
The code goes in a general module, so it can be used with any form. Passing in the form as an argument allows the code to do anything that you could with with Me in the form's own module.
The second argument is a string that this routine can append any error messages to. Since the function does not pop up any error messages, the calling routine can then decide whether it wants to display the errors, ignore them, pass them to a higher level function, or whatever. I find this approach very useful for generic procedures, especially where they can be called in various ways.
The final argument accepts an array, so the user can type as many literals as they wish, separated by commas. The ParamArray keyword means any number of arguments to be passed in. They arrive as a variant array, so the first thing the function does is to use LBound() to get the lower array bound (usually zero) and UBound() to get the upper array bound - standard array techniques.
The code checks that the form is at a new record (which also verifies it is a bound form). Then it checks that there is a previous record to copy, and moves the form's RecordsetClone to the last record - the one we want to copy the field values from.
It then loops through all the controls on the form. The control's Name can be different from its ControlSource, so it is the ControlSource we must match to the field in the RecordsetClone. Some controls (labels, lines, ...) have no ControlSource. Others may be unbound, or bound to an expression, or bound to a calculated query field, or bound to an AutoNumber field - all cases where no assignment can be made. The code tests for these cases like this:
|Controls with no ControlSource (command buttons, labels, ...)
||The HasProperty() function tests for this property, recovers from any error, and informs the main routine whether to skip the control.
|The control the user is typing into (so we do not overwrite the entry)
||Compare the control's Name with Screen.ActiveControl.Name.
|Controls named in the exception list
||Compare the control's Name with names in the exception list array.
||Test if the ControlSource property is a zero-length string.
|Controls bound to an expression (cannot be assigned a value)
||Test if the ControlSource starts with "=".
|Controls bound to a calculated query field
||In the form's RecordsetClone, the Field has a SourceTable property. For fields created in the query, this property is is a zero-length string.
|Controls bound to a calculated table field
||In the form's RecordsetClone, the Field has an Expression property that is not just a zero-length string.
|Controls bound to an AutoNumber field
||In the form's RecordsetClone, the Attributes property of the Field will have the dbAutoIncrField bit set.
|Fields that were Null in the record we are copying from
||We bypass these, so Access can still apply any DefaultValue.
If the control has not been culled along the way, we assign it the Value of the field in the form's RecordsetClone, and increment our counter.
The return value
Finally, the function returns the number of controls that were assigned a value, in case the calling routine wants to know.
If an error occurs, we return information about the error in the second argument, so the calling routine can examine or display the error message to the user.