Results 1 to 11 of 11
  1. #1
    Luchino is offline Novice
    Windows 10 Access 2013
    Join Date
    Dec 2015
    Posts
    11

    Angry Filter As You Type Combo Box Not Working

    Hi guys,



    I've pulled this code from Microsoft:


    Code:
    Private Sub cboFilter_Change()
      ‘ If the combo box is cleared, clear the form filter.
      If Nz(Me.cboFilter.Text) = “” Then
        Me.Form.Filter = “”
        Me.FilterOn = False
        
      ‘ If a combo box item is selected, filter for an exact match.
      ‘ Use the ListIndex property to check if the value is an item in the list.
      ElseIf Me.cboFilter.ListIndex <> -1 Then
        Me.Form.Filter = “[Company] = ‘” & _
                         Replace(Me.cboFilter.Text, “‘”, “””) & “‘”
        Me.FilterOn = True
        
      ‘ If a partial value is typed, filter for a partial company name match.
      Else
        Me.Form.Filter = “[Company] Like ‘*” & _
                         Replace(Me.cboFilter.Text, “‘”, “””) & “*'”
        Me.FilterOn = True
      End If
      
      ‘ Move the cursor to the end of the combo box.
      Me.cboFilter.SetFocus
      Me.cboFilter.SelStart = Len(Me.cboFilter.Text)
    End Sub
    


    https://blogs.office.com/2012/05/03/using-a-combo-box-to-search-as-you-type/

    But when I paste it into access, it gives me a load of errors... I have a feeling its because of the " and ' symbols, but I don't know how to change them so the code works. Any advice?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Yes, it is the quote and double quote.
    I replaced them in the following. It's the same code , only the quotes/dbl quotes have been changed.
    Code:
    Private Sub cboFilter_Change()
      ' If the combo box is cleared, clear the form filter.
      If Nz(Me.cboFilter.Text) = "" Then
        Me.Form.Filter = ""
        Me.FilterOn = False
        
      ' If a combo box item is selected, filter for an exact match.
      ' Use the ListIndex property to check if the value is an item in the list.
      ElseIf Me.cboFilter.ListIndex <> -1 Then
        Me.Form.Filter = "[Company] = '" & _
                         Replace(Me.cboFilter.Text, "'", """) & "'"
        Me.FilterOn = True
        
      ' If a partial value is typed, filter for a partial company name match.
      Else
        Me.Form.Filter = "[Company] Like '*" & _
                         Replace(Me.cboFilter.Text, "'", """) & "*'"
        Me.FilterOn = True
      End If
      
      ' Move the cursor to the end of the combo box.
      Me.cboFilter.SetFocus
      Me.cboFilter.SelStart = Len(Me.cboFilter.Text)
    End Sub

  3. #3
    Luchino is offline Novice
    Windows 10 Access 2013
    Join Date
    Dec 2015
    Posts
    11
    Great, thank you!
    It gives me this error though:

    Click image for larger version. 

Name:	accesssc.PNG 
Views:	34 
Size:	17.7 KB 
ID:	23026

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    If the idea of the Replace function is to replace the single quote with a double quote, use the escape character for double quotes ...
    Replace(Me.cboFilter.Text, "'", """")

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The idea was to replace a single ' with 2 single quotes ''


    After seeing ItsMe response, it does appear that a single quote ' was replaced with a double quote ".

    Whenever, I escape characters (single quote, I always replace with 2 single quotes).

    It looks to me there was some mistyping in the original.

  6. #6
    Luchino is offline Novice
    Windows 10 Access 2013
    Join Date
    Dec 2015
    Posts
    11
    I've done this and it doesn't give me that error now, but I have this instead:
    Click image for larger version. 

Name:	accesssc2.PNG 
Views:	32 
Size:	8.6 KB 
ID:	23028

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you copy and post the code? Your graphic is only showing part of what you have.

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by orange View Post
    ...
    After seeing ItsMe response, it does appear that a single quote ' was replaced with a double quote ".
    ...
    Sometimes I will declare some constants to represent special characters and make my code easier to read. For example ...

    Code:
    Const strDoubleQuote As String = """"
    Const strSingleQuote As String = "'"
     = "[Company] Like '*" & Replace(Me.cboFilter.Text, strSingleQuote, strSingleQuote & strSingleQuote) & "*'"

  9. #9
    Luchino is offline Novice
    Windows 10 Access 2013
    Join Date
    Dec 2015
    Posts
    11
    It realised that there were two lines where I needed to change the quotes. Thank you both; your suggestions worked.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  11. #11
    Dkaiman is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Sep 2016
    Posts
    1
    Hello, I have taken your advice on the changes to the code but My problem seems to be the Me.Filter = True Line. I have copied the text below. Could you point me in the right direction to fix this.
    Any Help would be greatly appreciated.

    Private Sub cboFilter_Change()
    ' If the combo box is cleared, clear the form filter.
    If Nz(Me.CboFilter.Text) = "" Then
    Me.Form.Filter = ""
    Me.FilterOn = False

    ' If a combo box item is selected, filter for an exact match.
    ' Use the ListIndex property to check if the value is an item in the list.
    ElseIf Me.CboFilter.ListIndex <> -1 Then
    Me.Form.Filter = "[Course Title] = '" & _
    Replace(Me.CboFilter.Text, "'", """") & "'"
    Me.FilterOn = True

    ' If a partial value is typed, filter for a partial company name match.
    Else
    Me.Form.Filter = "[Course Title] Like '*" & _
    Replace(Me.CboFilter.Text, "'", """") & "*'"
    Me.FilterOn = True
    End If

    ' Move the cursor to the end of the combo box.
    Me.CboFilter.SetFocus
    Me.CboFilter.SelStart = Len(Me.CboFilter.Text)
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Combo Box Autocomplete / Filter as you type
    By Pimped in forum Programming
    Replies: 28
    Last Post: 01-14-2015, 08:37 AM
  2. Replies: 3
    Last Post: 05-31-2013, 04:32 PM
  3. Replies: 5
    Last Post: 06-13-2012, 05:08 AM
  4. Combo Box Filter Not working in View mode.
    By Richie27 in forum Access
    Replies: 5
    Last Post: 05-17-2012, 07:02 AM
  5. Replies: 2
    Last Post: 08-18-2011, 10:20 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums