By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,135 Members | 1,184 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,135 IT Pros & Developers. It's quick & easy.

Problems with default values from last record

P: 73

I used Allen Browne's method of assigning default values from the last record which is especially helpful when adding a new record to filtered records. Using his method I had to create a module:

Expand|Select|Wrap|Line Numbers
  1. Public Function CarryOver(frm As Form, strErrMsg As String, ParamArray avarExceptionList()) As Long
  2. On Error GoTo Err_Handler
  3.     'Purpose: Carry over the same fields to a new record, based on the last record in the form.
  4.     'Arguments: frm               = the form to copy the values on.
  5.     '           strErrMsg         = string to append error messages to.
  6.     '           avarExceptionList = list of control names NOT to copy values over to.
  7.     'Return:    Count of controls that had a value assigned.
  8.     'Usage:     In a form's BeforeInsert event, excluding Surname and City controls:
  9.     '               Call CarryOver(Me, strMsg, "Surname", City")
  10.     Dim rs As DAO.Recordset         'Clone of form.
  11.     Dim ctl As Control              'Each control on form.
  12.     Dim strForm As String           'Name of form (for error handler.)
  13.     Dim strControl As String        'Each control in the loop
  14.     Dim strActiveControl As String  'Name of the active control. Don't assign this as user is typing in it.
  15.     Dim strControlSource As String  'ControlSource property.
  16.     Dim lngI As Long                'Loop counter.
  17.     Dim lngLBound As Long           'Lower bound of exception list array.
  18.     Dim lngUBound As Long           'Upper bound of exception list array.
  19.     Dim bCancel As Boolean          'Flag to cancel this operation.
  20.     Dim bSkip As Boolean            'Flag to skip one control.
  21.     Dim lngKt As Long               'Count of controls assigned.
  23.     'Initialize.
  24.     strForm = frm.Name
  25.     strActiveControl = frm.ActiveControl.Name
  26.     lngLBound = LBound(avarExceptionList)
  27.     lngUBound = UBound(avarExceptionList)
  29.     'Must not assign values to the form's controls if it is not at a new record.
  30.     If Not frm.NewRecord Then
  31.         bCancel = True
  32.         strErrMsg = strErrMsg & "Cannot carry values over. Form '" & strForm & "' is not at a new record." & vbCrLf
  33.     End If
  34.     'Find the record to copy, checking there is one.
  35.     If Not bCancel Then
  36.         Set rs = frm.RecordsetClone
  37.         If rs.RecordCount <= 0& Then
  38.             bCancel = True
  39.             strErrMsg = strErrMsg & "Cannot carry values over. Form '" & strForm & "' has no recrods." & vbCrLf
  40.         End If
  41.     End If
  43.     If Not bCancel Then
  44.         'The last record in the form is the one to copy.
  45.         rs.MoveLast
  46.         'Loop the controls.
  47.         For Each ctl In frm.Controls
  48.             bSkip = False
  49.             strControl = ctl.Name
  50.             'Ignore the active control, those without a ControlSource, and those in the exception list.
  51.             If (strControl <> strActiveControl) And HasProperty(ctl, "ControlSource") Then
  52.                 For lngI = lngLBound To lngUBound
  53.                     If avarExceptionList(lngI) = strControl Then
  54.                         bSkip = True
  55.                         Exit For
  56.                     End If
  57.                 Next
  58.                 If Not bSkip Then
  59.                     'Examine what this control is bound to. Ignore unbound, or bound to an expression.
  60.                     strControlSource = ctl.ControlSource
  61.                     If (strControlSource <> vbNullString) And Not (strControlSource Like "=*") Then
  62.                         'Ignore calculated fields (no SourceTable), autonumber fields, and null values.
  63.                         With rs(strControlSource)
  64.                             If (.SourceTable <> vbNullString) And ((.Attributes And dbAutoIncrField) = 0&) _
  65.                                 And Not (IsCalcTableField(rs(strControlSource)) Or IsNull(.Value)) Then
  66.                                 If ctl.Value = .Value Then
  67.                                     'do nothing. (Skipping this can cause Error 3331.)
  68.                                 Else
  69.                                     ctl.Value = .Value
  70.                                     lngKt = lngKt + 1&
  71.                                 End If
  72.                             End If
  73.                         End With
  74.                     End If
  75.                 End If
  76.             End If
  77.         Next
  78.     End If
  80.     CarryOver = lngKt
  82. Exit_Handler:
  83.     Set rs = Nothing
  84.     Exit Function
  86. Err_Handler:
  87.     strErrMsg = strErrMsg & Err.Description & vbCrLf
  88.     Resume Exit_Handler
  89. End Function
  91. Private Function IsCalcTableField(fld As DAO.Field) As Boolean
  92.     'Purpose: Returns True if fld is a calculated field (Access 2010 and later only.)
  93. On Error GoTo ExitHandler
  94.     Dim strExpr As String
  96.     strExpr = fld.Properties("Expression")
  97.     If strExpr <> vbNullString Then
  98.         IsCalcTableField = True
  99.     End If
  101. ExitHandler:
  102. End Function
  104. Public Function HasProperty(obj As Object, strPropName As String) As Boolean
  105.     'Purpose: Return true if the object has the property.
  106.     Dim varDummy As Variant
  108.     On Error Resume Next
  109.     varDummy = obj.Properties(strPropName)
  110.     HasProperty = (Err.Number = 0)
  111. End Function
and then assigned this code within beforeinsert() in the form's properties:

Expand|Select|Wrap|Line Numbers
  1.     Private Sub Form_BeforeInsert(Cancel As Integer)
  2.         Dim strMsg As String
  3.         Call CarryOver(Me, strMsg)
  4.         If strMsg <> vbNullString Then
  5.             MsgBox strMsg, vbInformation
  6.         End If
  7.     End Sub
As I didn't want to assign default values to all fields I changed the line:

Expand|Select|Wrap|Line Numbers
  1. Call CarryOver(Me, strMsg, "Evidence", "EviDescrip")
This worked well in my form before.

I want to use the same code in a new form which is linked to three tables (many-to-many relationship) via a query. I've set it up in exactly the same way but when I put the code into beforeinsert() on the form all records are carrying over even though the line:

Expand|Select|Wrap|Line Numbers
  1. Call CarryOver(Me, strMsg, "Evidence", "EviDescrip")
is the same (the field names are from the same table on the previous form so it shouldn't have to be changed?).

Any help would be much obliged.

Jun 12 '12 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 5K+
P: 5,397
Oh, My!
Usually the stuff from Mr. Brown is fairly good and usable...

This is the method I use: I have used it many times to copy the values from the prior record into the new record.

Give it a whirl

Sorry you got lost in the shuffle... busy days tend to role the posts off the list so taking a cue from one of the other mods/admins just going back thru the posts :)

Aug 20 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.