CODEWITHSUNDEEP
ms-accessvba
user4403907
user4403907

Reputation: 1

VBA Compile error: Expected end of statement

I am trying to code a combo box in a form such that it automatically filters the options as the user types. I have a code but I keep getting a compile error. Here is my code:

Private Sub UTIL_FCLTY_ID_Change()
    If Nz(Me.UTIL_FCLTY_ID.Text) = “” Then
        Me.Form.Filter = “”
        Me.FilterOn = False
    ElseIf Me.UTIL_FCLTY_ID.ListIndex <> -1 Then
        Me.Form.Filter = “[UTILITY FACILITY TYPE NAME:] = ‘” & _
                 Replace(Me.UTIL_FCLTY_ID.Text, “‘”, “””) & “‘”
        Me.FilterOn = True
    Else
        Me.Form.Filter = “[UTILITY FACILITY TYPE NAME:] Like ‘*” & _
                 Replace(Me.UTIL_FCLTY_ID.Text, “‘”, “””) & “*'”
        Me.FilterOn = True

    End If

    Me.UTIL_FCLTY_ID.SetFocus
    Me.UTIL_FCLTY_ID.SelStart = Len(Me.UTIL_FCLTY_ID.Text)

End Sub

Upvotes: 0

Views: 3503

Answers (1)

Maciej Los
Maciej Los

Reputation: 8591

As @HansUp mentioned, you use non-standard quotes and non-standard apostrophe. Replace them with correct one. Second issue is with this statement: """. VBA compiler sees it as "" " (empty string and unclosed quote). You need to use double quote inside quote to workaround it: """""

Finally your procedure should look like:

Private Sub UTIL_FCLTY_ID_Change()
    If Nz(Me.UTIL_FCLTY_ID.Text) = "" Then
        Me.Form.Filter = ""
        Me.FilterOn = False
        Exit Sub 'there's nothing to proceed ;)
    End If

    If Me.UTIL_FCLTY_ID.ListIndex <> -1 Then
        Me.Form.Filter = "[UTILITY FACILITY TYPE NAME:] = '" & _
                 Replace(Me.UTIL_FCLTY_ID.Text, "'", """") & "'"
        Me.FilterOn = True
    Else
        Me.Form.Filter = "[UTILITY FACILITY TYPE NAME:] Like '*" & _
                 Replace(Me.UTIL_FCLTY_ID.Text, "'", """") & "*'"
        Me.FilterOn = True
    End If

    Me.UTIL_FCLTY_ID.SetFocus
    Me.UTIL_FCLTY_ID.SelStart = Len(Me.UTIL_FCLTY_ID.Text)

End Sub

Upvotes: 1

Related Questions