Microsoft Access Tips for Serious Users

Provided by Allen Browne. 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:

1. Set the Default Value of each control so they offer the same value as soon as you move into the new record.

2. 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.)

The steps

To implement this tip in your form:

1. 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.

2. Copy the code below, and paste into the new module.

3. Verify that Access understands the code by choosing Compile from the Debug menu.

4. Save it with a name such as Module1. Close the code window.

5. Open your form in design view.

6. Open the Properties sheet, making sure you are looking at the properties of the Form (not those of a text box.)

7. On the Event tab of the Properties box, set the Before Insert property to:
       [Event Procedure]

8. Click the Build button (...) beside this Property. Access opens the code window.

9. 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
           End If
       End Sub

10. Save.

11. 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:
    =CarryOver([Form], "")

The code

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
    'Purpose: Carry over the same fields to a new record, based on the last record in the form.
    'Arguments: frm               = the form to copy the values on.
    '           strErrMsg         = string to append error messages to.
    '           avarExceptionList = list of control names NOT to copy values over to.
    'Return:    Count of controls that had a value assigned.
    'Usage:     In a form's BeforeInsert event, excluding Surname and City controls:
    '               Call CarryOver(Me, strMsg, "Surname", City")
    Dim rs As DAO.Recordset         'Clone of form.
    Dim ctl As Control              'Each control on form.
    Dim strForm As String           'Name of form (for error handler.)
    Dim strControl As String        'Each control in the loop
    Dim strActiveControl As String  'Name of the active control. Don't assign this as user is typing in it.
    Dim strControlSource As String  'ControlSource property.
    Dim lngI As Long                'Loop counter.
    Dim lngLBound As Long           'Lower bound of exception list array.
    Dim lngUBound As Long           'Upper bound of exception list array.
    Dim bCancel As Boolean          'Flag to cancel this operation.
    Dim bSkip As Boolean            'Flag to skip one control.
    Dim lngKt As Long               'Count of controls assigned.

    'Initialize.
    strForm = frm.Name
    strActiveControl = frm.ActiveControl.Name
    lngLBound = LBound(avarExceptionList)
    lngUBound = UBound(avarExceptionList)

    'Must not assign values to the form's controls if it is not at a new record.
    If Not frm.NewRecord Then
        bCancel = True
        strErrMsg = strErrMsg & "Cannot carry values over. Form '" & strForm & "' is not at a new record." & vbCrLf
    End If
    'Find the record to copy, checking there is one.
    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
        End If
    End If

    If Not bCancel Then
        'The last record in the form is the one to copy.
        rs.MoveLast
        'Loop the controls.
        For Each ctl In frm.Controls
            bSkip = False
            strControl = ctl.Name
            'Ignore the active control, those without a ControlSource, and those in the exception list.
            If (strControl <> strActiveControl) And HasProperty(ctl, "ControlSource") Then
                For lngI = lngLBound To lngUBound
                    If avarExceptionList(lngI) = strControl Then
                        bSkip = True
                        Exit For
                    End If
                Next
                If Not bSkip Then
                    'Examine what this control is bound to. Ignore unbound, or bound to an expression.
                    strControlSource = ctl.ControlSource
                    If (strControlSource <> vbNullString) And Not (strControlSource Like "=*") Then
                        'Ignore calculated fields (no SourceTable), autonumber fields, and null values.
                        With rs(strControlSource)
                            If (.SourceTable <> vbNullString) And ((.Attributes And dbAutoIncrField) = 0&) _
                                And Not (IsCalcTableField(rs(strControlSource)) Or IsNull(.Value)) Then
                                If ctl.Value = .Value Then
                                    'do nothing. (Skipping this can cause Error 3331.)
                                Else
                                    ctl.Value = .Value
                                    lngKt = lngKt + 1&
                                End If
                            End If
                        End With
                    End If
                End If
            End If
        Next
    End If

    CarryOver = lngKt

Exit_Handler:
    Set rs = Nothing
    Exit Function

Err_Handler:
    strErrMsg = strErrMsg & Err.Description & vbCrLf
    Resume Exit_Handler
End Function

Private Function IsCalcTableField(fld As DAO.Field) As Boolean
    'Purpose: Returns True if fld is a calculated field (Access 2010 and later only.)
On Error GoTo ExitHandler
    Dim strExpr As String

    strExpr = fld.Properties("Expression")
    If strExpr <> vbNullString Then
        IsCalcTableField = True
    End If

ExitHandler:
End Function

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
    'Purpose: Return true if the object has the property.
    Dim varDummy As Variant

    On Error Resume Next
    varDummy = obj.Properties(strPropName)
    HasProperty = (Err.Number = 0)
End Function

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 arguments

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 checks

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:

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.