Compile Error: Method or data member not found

Question

Having an issue with line 8 of the following code

Compile Error: Method or data member not found

Private Sub Search_Click()
    Const cInvalidDateError As String = "You have entered an invalid date."
    Dim strWhere As String
    Dim strError As String
    
    strWhere = "1=1"
     
    ' If Assigned To
    If Not IsNull(Me.AssignedTo) Then
        'Create Predicate
        strWhere = strWhere & " AND " & "Issues.[Assigned To] = " & Me.AssignedTo & ""
    End If
    
    ' If Opened By
    If Not IsNull(Me.OpenedBy) Then
        'Add the predicate
        strWhere = strWhere & " AND " & "Issues.[Opened By] = " & Me.OpenedBy & ""
    End If
    
    ' If Status
    If Nz(Me.STATUS) <> "" Then
        'Add it to the predicate - exact match
        strWhere = strWhere & " AND " & "Issues.Status = '" & Me.STATUS & "'"
    End If
    
     ' If Category
    If Nz(Me.Category) <> "" Then
        'Add it to the predicate - exact match
        strWhere = strWhere & " AND " & "Issues.Category = '" & Me.Category & "'"
    End If
    
     ' If Priority
    If Nz(Me.Priority) <> "" Then
        'Add it to the predicate - exact match
        strWhere = strWhere & " AND " & "Issues.Priority = '" & Me.Priority & "'"
    End If
      
    ' If Opened Date From
    If IsDate(Me.OpenedDateFrom) Then
        ' Add it to the predicate - exact
        strWhere = strWhere & " AND " & "Issues.[Opened Date] >= " & GetDateFilter(Me.OpenedDateFrom)
    ElseIf Nz(Me.OpenedDateFrom) <> "" Then
        strError = cInvalidDateError
    End If
    
    ' If Opened Date To
    If IsDate(Me.OpenedDateTo) Then
        ' Add it to the predicate - exact
        strWhere = strWhere & " AND " & "Issues.[Opened Date] <= " & GetDateFilter(Me.OpenedDateTo)
    ElseIf Nz(Me.OpenedDateTo) <> "" Then
        strError = cInvalidDateError
    End If
    
    ' If Due Date From
    If IsDate(Me.DueDateFrom) Then
        ' Add it to the predicate - exact
        strWhere = strWhere & " AND " & "Issues.[Due Date] >= " & GetDateFilter(Me.DueDateFrom)
    ElseIf Nz(Me.DueDateFrom) <> "" Then
        strError = cInvalidDateError
    End If
    
     ' If Due Date To
    If IsDate(Me.DueDateTo) Then
        ' Add it to the predicate - exact
        strWhere = strWhere & " AND " & "Issues.[Due Date] <= " & GetDateFilter(Me.DueDateTo)
    ElseIf Nz(Me.DueDateTo) <> "" Then
        strError = cInvalidDateError
    End If
    
    ' If Title
    If Nz(Me.Title) <> "" Then
        ' Add it to the predicate - match on leading characters
        strWhere = strWhere & " AND " & "Issues.Title Like '*" & Me.Title & "*'"
    End If
    

    If strError <> "" Then
        MsgBox strError
    Else
        'DoCmd.OpenForm "Browse Issues", acFormDS, , strWhere, acFormEdit, acWindowNormal
        If Not Me.FormFooter.Visible Then
            Me.FormFooter.Visible = True
            DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
        End If
        Me.Browse_All_Issues.Form.Filter = strWhere
        Me.Browse_All_Issues.Form.FilterOn = True
    End If
End Sub

 

Answer 1

I got the error when I places the code into a module and the error occured on the variable "ME".  When you use ME it referes to the to the parent class object. In VBA it is normally it is used in a FORM code and referes to the FORM.  If you already have a FORM the the error is occuring because the object that is being reference is not on the FORM.  for example, if you got the error on the object Me.DueDateTo then there isn't an object "DueDate" (like a Listbox with that name) on the form.

---

jdweng

Answer 2

Hi,

you have spaces in controls' real names, e.g. "Assigned To". In VBA such names are transformed by replacing a space with an underscore.

So, they should be Me.Assigned_To, Me.Due_Date_To, Me.Opened_By etc . That's why I prefer not to use special characters in control/field name. It's better explicitly name them, for example, "Assigned_To" and you won't be confused by such situations.

---

Andrey V Artemyev | Saint-Petersburg, Russia
Russian blog artemyev.biztoolbox.ru
English blog enblog.biztoolbox.ru

Answer 3

To be honest, none of those fields are on any object in the database.  Is there any way to search for field names?

This is actually a subform, could it be referencing another part of the main form?

Answer 4

Where is this search button located?

If it is on the main form - Me means this main form. Otherwise, in case of subform, Me means this subform.

You can open both of these form modules, type "Me." and check which fields are being proposed by IntelliSense.

---

Andrey V Artemyev | Saint-Petersburg, Russia
Russian blog artemyev.biztoolbox.ru
English blog enblog.biztoolbox.ru

Answer 5

A control including a button can have spaces in the name.  Adding an underscore isn't necessary.   If you view the form in the Datasheet view you can easily see all the control names but can't change the names.  I don't think there is any easy way of finding the control without click each control one by one and then checking the property of the control.  If all the controls have captions you can write a simple marco to get the caption name to help located which control is wrong.

---

jdweng

Answer 6

A control including a button can have spaces in the name. Adding an underscore isn't necessary. 

Nobody here says they can't. It was just a piece of advice.

I don't think there is any easy way of finding the control

I don't think this one is very difficult having all the forms opened:

Public Sub ctrlNames()
Dim frm As Form, ctrl As Control
    For Each frm In Forms
        Debug.Print frm.Name
        For Each ctrl In frm.Controls
            Debug.Print "---" & ctrl.Name & ": " & ctrl.Properties(2) 'controltype
        Next
    Next
End Sub

 

---

Andrey V Artemyev | Saint-Petersburg, Russia
Russian blog artemyev.biztoolbox.ru
English blog enblog.biztoolbox.ru