Dynamically search multiple fields - Help

[blankbandit]

Hello - John Big Booty posted an awesome thread in these forums. I'm unable to post a link due to not having posted at least 10 posts, but you can find the link by tacking on the following to this site's primary URL: forums/showthread.php?t=188663

It was, essentially, a type-and-show-as-you-go search functionality for an Access Form. It works great, except that I'm experiencing intermittent slowness when using the form. As I type, the characters appear very slowly and experience a significant lag time.

I only have about 70 records so far, so I'm pretty sure the slowness is not being caused by an overabundance of data. At other times, it goes super fast without any lag.

Does anyone know how to optimize the code or otherwise address this slowness?

Thanks in advance.

David

 

[jdraw]

You can post, I believe, if you zip your database and attach the zipfile.
Do a compact and repair before zipping.

Tell us more about your set up --split database? Number of users...

 

[blankbandit]

You can post, I believe, if you zip your database and attach the zipfile.
Do a compact and repair before zipping.

Tell us more about your set up --split database? Number of users...

The database is full of proprietary, highly confidential data, so, unfortunately, I'd be unable to do that. Apologies.

The setup is done just like the post suggested. I have a form, and, within the detail section, I have two unbound text boxes and then a list box. In the Search For text box, I have the following code for the On Change event:

Private Sub SearchFor_Change()
'Create a string (text) variable
    Dim vSearchString As String

'Populate the string variable with the text entered in the Text Box SearchFor
    vSearchString = SearchFor.Text

'Pass the value contained in the string variable to the hidden text box SrchText,
'that is used as the sear4ch criteria for the Query QRY_SearchAll
    SrchText.Value = vSearchString

'Requery the List Box to show the latest results for the text entered in Text Box SearchFor
    Me.SearchResults.Requery


'Tests for a trailing space and exits the sub routine at this point
'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box SearchFor
    If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
        Exit Sub
    End If

'Set the focus on the first item in the list box
    Me.SearchResults = Me.SearchResults.ItemData(1)
    Me.[SearchResults].SetFocus

'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of  the List Box
    DoCmd.Requery

'Returns the cursor to the the end of the text in Text Box SearchFor
    Me.SearchFor.SetFocus

    If Not IsNull(Len(Me.SearchFor)) Then
        Me.SearchFor.SelStart = Len(Me.SearchFor)
    End If

End Sub

 

[Frothingslosh]

I use something similar that I found, but I use this to limit the calls to only occur after a certain minimum number of characters have been entered in order to avoid that very problem:

If Len(Nz(Me.cboLastName.Text)) > 3 Then Call ReloadLName(Nz(Me.cboLastName.Text, ""))

The actual code I use for the search-as-you-type functionality is this:

Private Sub ReloadLName(ByRef lName As String)
 
Dim NewStub             As String
Dim SQL                 As String
 
On Error GoTo ReloadLName_Err
 
    'First characters of LName.
    NewStub = Nz(Left(lName, 4), "")
 
    'Only check if the first 4 characters are different than previously.
    If NewStub <> LNameStub Then
 
        If Len(NewStub) < 4 Then
 
            'Clear the RowSource.
            Me.cboLastName.RowSource = ""
            Me.cboLastName.LimitToList = False
            LNameStub = ""
        Else
 
            'Add a RowSource.
            SQL = "SELECT UCase(tblPreAuditFile.LASTNAME) as LName " & _
                  "FROM tblPreAuditFile " & _
                  "GROUP BY tblPreAuditFile.LASTNAME " & _
                  "HAVING tblPreAuditFile.LASTNAME Like " & Chr(34) & NewStub & "*" & Chr(34) & " " & _
                  "ORDER BY tblPreAuditFile.LASTNAME;"
            Me.cboLastName.RowSource = SQL
            Me.cboLastName.LimitToList = True
            LNameStub = NewStub
        End If
    End If
 
ReloadLName_Exit:
    Exit Sub
 
ReloadLName_Err:
    MsgBox "Error occurred" & vbCrLf & vbCrLf & _
           "In Function:" & vbTab & "frmMainMenu.ReloadLName" & vbCrLf & _
           "Err Number: " & vbTab & Err.Number & vbCrLf & _
           "Description: " & vbTab & Err.Description, vbCritical, AppTitle
 
    Resume ReloadLName_Exit
End Sub

Obviously this particular code is customized for the search box where I used it - it's used to do search-as-you-type in a Last Name combo box. You should be able to modify it to do what you need, or even to work as a generic function, but it's a starting point.

Oh, this function is in the form's module, and LNameStub is defined at the module level so it'll stick around as long as the form is open.

 

[jdraw]

I don't see any obvious reason for slow performance. Can you mock up some of your data and "desensitive it" ---mickey mouse, captain ahab sort of thing.
We really don't want confidential data or private info --but we need a sample of your set up to some degree if we are to review and try things.

I am aware of John Big Booty's post, and it always seems to perform well.

Perhaps there are other things going on in your usage. Optimizing code typically involves working with the code or a portion of it.