Seems like I found an alternate solution "Carry data over to new
record" at:
http://www.everythingaccess.com/tutorials.asp?ID=Carry-data-over-to-new-record
This is for those who read this posting and are looking to do the same
as I was looking for.
PC
===============================================
Assign default values from the last record
http://www.everythingaccess.com/tutorials.asp?ID=Carry-data-over-to-new-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.
The steps
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, 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:
[Event Procedure]
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
End If
End Sub
Save.
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 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
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:Control Action
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 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.0
