Provided by Allen Browne, firstname.lastname@example.org. Created May 2006. Updated Nov 2013. (An older, less powerful version of this tip is archived here.)
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 '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 records." & 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
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.|
|Unbound controls||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.
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.
|Home||Index of tips||Top|