Results 1 to 12 of 12
  1. #1
    cowboy is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Posts
    291

    Search kicking error when filter does not return any records

    I have a form with a text box which filters the records for the form after each character is typed in the search text box. The form is setup as a split form and the search box is located in the form header. The code works great until the search box filters for something that returns zero records I get an error "Run-time error '2185': You can't reference a property or method for a control unless the control has the focus." and this shows up even if on the previous line I set the focus.

    Here is the code I am using:
    Code:
    Private Sub txtTitleSearch_Change()    
        Dim vSearch As String
        vSearch = Me.txtTitleSearch.Text
        
        If Len(vSearch) > 0 Then
            Me.Filter = vbNullString
            Me.OrderBy = vbNullString
            Me.Filter = "[Title] Like '*" & vSearch & "*'"
            Me.FilterOn = True
        Else
            Me.Filter = vbNullString
        End If
        Me.txtTitleSearch.SetFocus
        Me.txtTitleSearch.SelStart = Len(Me.txtTitleSearch.Text)
    End Sub
    I was able to use "If Me.RecordsetClone.RecordCount = 0 Then" to trigger after filtering to zero records, but an exit sub doesnt help because on the next time the sub is called it throws the same error.

    I would like to allow the user to be able to type whatever they want even if it returns zero records, any ideas?

    Thank you.
    -Dan


    search error.zip
    I have attached a simplified version of the database if that helps.
    Last edited by cowboy; 04-18-2019 at 09:52 AM.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you cant calculate Len() of null, change line:
    If Len(vSearch) > 0 Then
    to
    Code:
    If IsNull(txtTitleSearch) then
       me.filterOn = false
    else
        me.filter…..etc....
    endif

  3. #3
    cowboy is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Posts
    291
    Sorry I should have specified what lines were triggering errors. Using the numbers below line 14 triggers the error. I figured that if I put in a catch after line 12 to exit the sub if Me.RecordsetClone.RecordCount = 0 then line 3 triggers the error the next time a character is entered. What I think is happening is when there is no record it is not recognizing the form header as it is linked to the detail area. I think I could fix this using a continuous form, but then I wouldnt have all the sorting and filtering options that the datasheet type format allows.

    Code:
    1   Private Sub txtTitleSearch_Change()    
    2       Dim vSearch As String
    3       vSearch = Me.txtTitleSearch.Text
    4    
    5         If Len(vSearch) > 0 Then
    6             Me.Filter = vbNullString
    7             Me.OrderBy = vbNullString
    8             Me.Filter = "[Title] Like '*" & vSearch & "*'"
    9             Me.FilterOn = True
    10        Else
    11            Me.Filter = vbNullString
    12        End If
    13        Me.txtTitleSearch.SetFocus
    14        Me.txtTitleSearch.SelStart = Len(Me.txtTitleSearch.Text) 
    15   End Sub
    
    @ranman256, the Len(vSearch) > 0 is working, when I pull the .text it shows "" and not Null.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your problem is basically that you have Allow Addtions set to No, thus when the form has no records, no valid controls can receive the focus. Sure, you can set the focus to the remove filter button, but then you can't get the textbox text property. Probably the best thing you can do is trap for the error, or do something like the following:
    Code:
     With Me
       .Recalc
       .FilterOn = False
       .Filter = "[Title] Like '*" & .txtTitleSearch & "*'"
       .FilterOn = True
       If .Recordset.RecordCount = 0 Then Exit Sub
       .txtTitleSearch.SetFocus
       .txtTitleSearch.SelStart = Len(.txtTitleSearch.Text)
      End With
    Gotta say I'm not a big fan of the flickering and if there's a LOT of records, the recordset is 'queried' on each keystroke. That can be quite the performance hit. Can't recall one of these that wouldn't allow you do enter a space between words, but your's doesn't for some reason.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This should take care of the inability to include spaces
    Code:
    Dim vSearch As String
    
    Me.txtTitleSearch.SetFocus
    vSearch = Me.txtTitleSearch.Text
    Me.FilterOn = False
    Me.Filter = "[Title] Like '*" & vSearch & "*'"
    Me.FilterOn = True
    Me.txtTitleSearch.SetFocus
    Me.txtTitleSearch = vSearch
    Me.txtTitleSearch.SelStart = Me.txtTitleSearch.SelLength

  6. #6
    cowboy is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Posts
    291
    Quote Originally Posted by Micron View Post
    This should take care of the inability to include spaces
    Thank you. That did work for the spaces, I had not realized that was an issue yet so thanks for catching that.

    Now I just need to figure out how to handle when someone types in something that returns zero records.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    ??
    I experience no issues when there are no records.

  8. #8
    cowboy is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Posts
    291
    When I type in a title that brings up no results I get the following error:
    Click image for larger version. 

Name:	error 2185.png 
Views:	7 
Size:	9.4 KB 
ID:	38154

    and then if I hit debug it takes me to this line:

    Click image for larger version. 

Name:	error line.png 
Views:	7 
Size:	11.3 KB 
ID:	38155

    Are you not getting the same results?

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    No, sorry. I had set Allow Additions to yes while experimenting. That would solve the issue.
    If you didn't want to permit additions but set the property to yes, then the controls can be locked or disabled. Not sure what the purpose of the form is, other than to find records that I can edit yet cannot add to, so that may not be viable for you.

    What does "figure out how to handle when someone types in something that returns zero records." mean? How to implement my suggestion on trapping the record if you stick with the no additions? Does that mean you need help to handle the error, or that you missed that point? It would prevent the error but will change how the form behaves a bit.

  10. #10
    cowboy is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Posts
    291
    Sorry I missed that somehow, let me try that out and I will let you know if it works, probably wont be until Monday.

  11. #11
    cowboy is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Posts
    291
    It looks like adding the allow additions works on the simplified version I made for this thread, but the one in the actual program steps from several queries and does not allow for additions. Would your suggestion be to simplify the search to them typing in the full search they want and hitting a button? Currently I have put the following code in and it is working well enough for the time being:

    Code:
    Private Sub txtTitleSearch_Change()On Error GoTo Err_Handle
        Dim vSearch As String
        Me.txtTitleSearch.SetFocus
        vSearch = Me.txtTitleSearch.Text
    
        If Len(vSearch) > 0 Then
            Me.txtTrackingSearch = ""
            Me.FilterOn = False
            Me.OrderBy = vbNullString
            Me.Filter = "[Title] Like '*" & vSearch & "*'"
            Me.FilterOn = True
        Else
            Me.FilterOn = False
        End If
    
        If Me.RecordsetClone.RecordCount = 0 Then
            MsgBox ("No Search Results Found"), vbOKOnly
            Me.FilterOn = False
            Me.txtTitleSearch = ""
            Exit Sub
        End If
    
        Me.txtTitleSearch.SetFocus
        Me.txtTitleSearch = vSearch
        Me.txtTitleSearch.SelStart = Me.txtTitleSearch.SelLength
    
    Exit_Handle:
        Exit Sub
    Err_Handle:
        Me.FilterOn = False
        Me.txtTitleSearch = ""
        Resume Exit_Handle
    End Sub

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    the answer to that question is probably dependent on whether or not you like the performance of the form and the traffic on the network, assuming there is any. I was once told by a MS engineer that applying a filter re-runs the underlying query, which was kind of opposite to what I expected to read. In that case, you're dinging the network for every letter typed.

    Filtering via a button is the way I would tend to go, but that's largely a personal opinion. What you have possibly works better than what I came up with (due to what seems like additional settings in your code) but I had
    Code:
    On Error GoTo errHandler
    With Me
      .txtTitleSearch.SetFocus
       vSearch = .txtTitleSearch.Text
      .FilterOn = False
      .Filter = "[Title] Like '*" & vSearch & "*'"
      .FilterOn = True
      .txtTitleSearch.SetFocus
      .txtTitleSearch = vSearch
      .txtTitleSearch.SelStart = .txtTitleSearch.SelLength
    End With
    
    exitHere:
    Exit Sub
    
    errHandler:
    If Err.Number = 2185 Then
      Exit Sub
    Else
      MsgBox "Error " & Err.Number & ": " & Err.Description
      Resume exitHere

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

Similar Threads

  1. Replies: 9
    Last Post: 08-19-2014, 12:41 PM
  2. error when search for multiple records.. WHY?
    By KingOf206 in forum Forms
    Replies: 11
    Last Post: 07-10-2013, 12:21 PM
  3. Return the result of a search
    By Loc in forum Programming
    Replies: 11
    Last Post: 06-12-2013, 06:23 PM
  4. Replies: 1
    Last Post: 03-06-2013, 05:51 AM
  5. Search records and return message.
    By sYn in forum Programming
    Replies: 4
    Last Post: 01-07-2011, 11:21 PM

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