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

Debugging Error Invalid use of Me Keword

P: 2
I am trying to get my form to autofill the values from the last record to the new record. In the form properites on the BeforeInsert, I used the following module

I have been away from VB for several years and am finding it challenging to understand. Can you all help? The other part of the code debugs. See below

I keep getting the same error when I debug this
Compile Error: Invalid use of Me Keyword
Here is the code. What comes back highlighted is the 3rd line, the word Me

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

Expert 5K+
P: 8,616
  1. In the future, kindly use Code Tags for readability purposes.
  2. I just browsed at your code for a minute, but the syntax for passing the Current Form appears to be OK.
  3. Try two things before we proceed any further:
    • Pass the Absolute Reference to the Form as the Function Argument, namely:
      Expand|Select|Wrap|Line Numbers
      1. Call Carry_Over(Forms("<Form Name>"), strMsg)
    • Explicitly Declare the Parameter in the Function Declaration as Access.Form, namely:
      Expand|Select|Wrap|Line Numbers
      1. Public Function CarryOver(frm As Access.Form, strErrMsg As String, ParamArray avarExceptionList()) As Long
    • I do not see the ParamArray() Values being passed to the Function.
  4. Let us know how you make out.
Apr 30 '10 #2

P: 2
Thank you for your help. Again I am just getting back to Access and am finding the terms confusing. When I add code above and run, it still stops at Me This seems to be a sticking point I can't get around.
May 1 '10 #3

Expert 5K+
P: 8,616
Expand|Select|Wrap|Line Numbers
  1. Call CarryOver(Me, strMsg)
  1. What is the Value of strMsg being passed?
  2. Where are the ParamArray() Values being passed?
May 2 '10 #4

Expert Mod 15k+
P: 31,307
I would guess your problem is that module7 is not a form module. The Me keyword, referring as it does to the associated form - IE the form the module is a part of, is only available in form modules. Standard modules could not sensibly have any meaning for Me, so they cannot use it.

Welcome to Bytes!
May 2 '10 #5

P: 37
Is Paramarray a optional argument? I don't see that statement anywhere. Either way, nothing wrong with passing reference of any object from a form class to a function in a code module as long as your scopes are correct which seems to be the case.
May 2 '10 #6

Post your reply

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