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

FORM not affected by MODULE

P: 1
MS Access: a MODULE in the common module bin is not recognized by the FORM for which I intended it. How do I inform the form to use the MODULE?
In the form properties I've used [Event Procedure] as ff (from Allen Browne (allen@allenbrowne.com))

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeInsert(Cancel As Integer)
  2.  Dim strMsg As String
  3.         Call CarryOver(Me, strMsg, "Itsohañe", "An-tsandry", "Toe'e", "Zoe-Afake")
  4.         If strMsg <> vbNullString Then
  5.             MsgBox strMsg, vbInformation
  6.         End If
  7. End Sub
in the Modules Bin of Access I copied the following, also from Allen Browne, and saved it as [COPYDOWN]
I've attached the full page from which I copied these two code items.

Thank you,
Steve
Attached Files
File Type: pdf Copy value from previous row.pdf (222.6 KB, 103 views)
Dec 8 '16 #1
Share this Question
Share on Google+
1 Reply


jforbes
Expert 100+
P: 1,107
Can you paste a copy of your Module? The instructions at Assign default values from the last record are great, but they might be a bit confusing.

This code should be placed in the Form:
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

This code is the code that should be in the 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.
  22.  
  23.     'Initialize.
  24.     strForm = frm.Name
  25.     strActiveControl = frm.ActiveControl.Name
  26.     lngLBound = LBound(avarExceptionList)
  27.     lngUBound = UBound(avarExceptionList)
  28.  
  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 records." & vbCrLf
  40.         End If
  41.     End If
  42.  
  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
  79.  
  80.     CarryOver = lngKt
  81.  
  82. Exit_Handler:
  83.     Set rs = Nothing
  84.     Exit Function
  85.  
  86. Err_Handler:
  87.     strErrMsg = strErrMsg & Err.Description & vbCrLf
  88.     Resume Exit_Handler
  89. End Function
  90.  
  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
  95.  
  96.     strExpr = fld.Properties("Expression")
  97.     If strExpr <> vbNullString Then
  98.         IsCalcTableField = True
  99.     End If
  100.  
  101. ExitHandler:
  102. End Function
  103.  
  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
  107.  
  108.     On Error Resume Next
  109.     varDummy = obj.Properties(strPropName)
  110.     HasProperty = (Err.Number = 0)
  111. End Function
Dec 8 '16 #2

Post your reply

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