1

I have a "Search" form in my database that takes in a string, explodes it into an array and builds an SQL string based on a selected join type ("Or", "And", or "Exact Phrase" radio buttons). My code works perfectly 95% of the time, but every once and while the database hangs when I switch between the different join types and requery. I have confirmed that the SQL is being created properly and I think that the problem stems from trying to change the subform's recordsource while it is still loading.

The exact way that my search form works is as follows:

  1. The user puts a search term/phrase in a text box
  2. On the "After Update" event of the textbox, VBA creates an SQL string and stores it in a hidden text field (dubbed "ModifiedSearchValue")
  3. If the user changes the join type (radiobuttons with options "Or", "And", Or "Exact Phrase") the "After Update" event on the group evokes the VBA sub (as in #2) and VBA creates an SQL string which it stores in the hidden text field (dubbed "ModifiedSearchValue")

  4. When the user hits the "Search" button, VBA sets the RecordSource of the subform to the value of "ModifiedSearchValue" by:

    Me!Results.Form.RecordSource = Me.ModifiedSearchValue

Again, this works perfectly most of the time, but if you enter the search term, click "Search", then change the join type and hit "Search" again, it causes the database to hang approximately 5% of the time.

My main VBA code is as follows

    Private Sub SearchString()
Dim SearchString, SearchStringTitle, SearchStringName, SearchStringDescription, SearchStringInvestigator, JoinValue, j, SQLString As String, SearchArray, varValue As Variant

    SearchString = Trim(Me.SearchValue)

    If Not IsNull(SearchString) Then
        SearchArray = Split(SearchString, " ")

        SQLString = "SELECT tbl_Studies.StudyID, tbl_Studies.Study_Short_Title, tbl_Studies.Study_Name, tbl_Studies.Study_Description, [qry_General:FullName_FMLD].FullName AS Investigator, tbl_Studies.Project_Type, IIf([Project_Type]=1,[tbl_Studies:Status]![Status],[tbl_Studies:NR_Status]![NR_Status]) AS Overall_Status, tbl_Studies.Date_Submitted, tbl_Studies.Date_Updated, tbl_Studies.Results_Summary, tbl_Studies.Inactive " & _
                    "FROM ([tbl_Studies:NR_Status] RIGHT JOIN ([tbl_Studies:Status] RIGHT JOIN tbl_Studies ON [tbl_Studies:Status].StatusID = tbl_Studies.Status) ON [tbl_Studies:NR_Status].NR_StatusID = tbl_Studies.NR_Status) LEFT JOIN [qry_General:FullName_FMLD] ON tbl_Studies.Investigator = [qry_General:FullName_FMLD].PersonID " & _
                    "WHERE "

        If Me.Join_Type <> 3 Then

            If Me.Join_Type = 1 Then
                JoinValue = "OR"
            ElseIf Me.Join_Type = 2 Then
                JoinValue = "AND"
            Else
                JoinValue = " "
            End If

    '--
            SearchStringTitle = "(("
            For Each varValue In SearchArray
                j = Trim(varValue)
                SearchStringTitle = SearchStringTitle & "(tbl_Studies.Study_Short_Title) Like ""*" & j & "*"""

                If varValue <> SearchArray(UBound(SearchArray)) Then
                    SearchStringTitle = SearchStringTitle & " " & JoinValue & " "
                End If
            Next varValue
            SearchStringTitle = SearchStringTitle & "))"

    '--
            SearchStringName = "(("
            For Each varValue In SearchArray
                j = Trim(varValue)
                SearchStringName = SearchStringName & "(tbl_Studies.Study_Name) Like ""*" & j & "*"""

                If varValue <> SearchArray(UBound(SearchArray)) Then
                    SearchStringName = SearchStringName & " " & JoinValue & " "
                End If
            Next varValue
            SearchStringName = SearchStringName & "))"

    '--
            SearchStringDescription = "(("
            For Each varValue In SearchArray
                j = Trim(varValue)
                SearchStringDescription = SearchStringDescription & "(tbl_Studies.Study_Description) Like ""*" & j & "*"""

                If varValue <> SearchArray(UBound(SearchArray)) Then
                    SearchStringDescription = SearchStringDescription & " " & JoinValue & " "
                End If
            Next varValue
            SearchStringDescription = SearchStringDescription & "))"

    '--
            SearchStringInvestigator = "(("
            For Each varValue In SearchArray
                j = Trim(varValue)
                SearchStringInvestigator = SearchStringInvestigator & "([qry_General:FullName_FMLD].FullName) Like ""*" & j & "*"""

                If varValue <> SearchArray(UBound(SearchArray)) Then
                    SearchStringInvestigator = SearchStringInvestigator & " " & JoinValue & " "
                End If
            Next varValue
            SearchStringInvestigator = SearchStringInvestigator & "))"

            SearchString = SearchStringTitle & " OR " & SearchStringName & " OR " & SearchStringDescription & " OR " & SearchStringInvestigator
        Else
            SearchStringTitle = "(((tbl_Studies.Study_Short_Title) Like ""*" & SearchString & "*""))"
            SearchStringName = "(((tbl_Studies.Study_Name) Like ""*" & SearchString & "*""))"
            SearchStringInvestigator = "((([qry_General:FullName_FMLD].FullName) Like ""*" & SearchString & "*""))"
            SearchStringDescription = "(((tbl_Studies.Study_Description) Like ""*" & SearchString & "*""))"

            SearchString = SearchStringTitle & " OR " & SearchStringName & " OR " & SearchStringDescription & " OR " & SearchStringInvestigator
        End If

        SearchString = SQLString & SearchString & ";"

        Me.ModifiedSearchValue.Value = SearchString
    End If
End Sub

Again, my theory is that the hanging is caused by changing the RecordSource of the subform before it has finished loading from the previous search, but I can't seem to determine any workaround.

Thanks in advance for any and all insight/help!

coffejor
  • 11
  • 3
  • 1
    What is `[tbl_Studies:Status]![Status]`? Is "tbl_Studies:Status" a table name? If, yes, why do you use the bang (!) instead of the dot (.)? Put the constructed query (`SQLString`) into a empty query and execute it in order to see if it is working. Put a breakpoint on the last line then enter "?SQLString" in the immediate window to get the SQL. – Olivier Jacot-Descombes Oct 26 '12 at 20:30
  • Hello, Thanks for your response! The use of the bang in place of the dot is proof of my laziness: I quickly put together the core of the SQL string in the query builder and copied it into VBA. I have gone back and cleaned up the code. To address your suggestion, I have verified that the SQL strings that are being produced are valid in much the way that you suggested. The problem does not seem to be invalid SQL. Thanks again for your suggestions; any other thoughts? – coffejor Oct 27 '12 at 19:30
  • I just realized that I didn't answer your initial question: yes "tbl_Studies:Status" is a table name. Thanks again! – coffejor Oct 27 '12 at 20:08
  • Why do you store the search string in a textbox? Store it in a class level variable (a variable defined before the first `Sub` or `Function` in the form. Or even better, construct the serach string only when the user hits "Search". Then it will no longer be necessary to store it permanently. – Olivier Jacot-Descombes Oct 29 '12 at 13:11
  • I really like the idea of only constructing the string when the user hits the "Search" button. The method I currently use again shows my laziness: while I was developing the code to construct the string I used the textbox to visualize and test the output. Today I will spend some time rewriting my code and will let you know if it solves the problem. Thanks for the suggestions! – coffejor Oct 29 '12 at 15:55
  • Ok, I have rewritten my code based on your suggestions: the string is now constructed and set to the RecordSource of the subform when the user hits search, without storing it in a separate field. Unfortunately, the problem still persists. I did note that I can get the database to hang without changing the search term or join type, e.g. if I submit the search, wait for the results, resubmit the search, wait for the results... (*n) Sometimes the database will hang after the third resubmissions, sometimes it takes more, but it seems to always hang eventually. Thanks again for the help thus far! – coffejor Oct 29 '12 at 16:21
  • Is it possible that, different event handler procedures are calling each other? Sometimes the code in one event handler can trigger another event, which in turn executes a code that triggers the first event again... – Olivier Jacot-Descombes Oct 29 '12 at 16:33
  • That is a great suggestion, and one that I definitely hadn't thought of. To test this, I created a completely new search form and results subform free of any event handlers other than the OnClick event of the "Search" button. I tested the result and had the same problem... :(. Thank you for sticking with me on this problem and for your suggestions! – coffejor Oct 29 '12 at 17:08
  • Yet another suggestion: Define a record source without where clause and don't change it. Then change the filtering by setting the `Filter` and `FilterOn` clauses. Where `Filter` will corresond to your Where-clause without the word `Where` and `FilterOn` will be either `True` or `False`. (That way you can even store the `SELECT FROM` part as Access query, what makes it easier to edit and understand it.) – Olivier Jacot-Descombes Oct 30 '12 at 13:54
  • Hello. Thanks again for all of your suggestions. I have employed the filter method of searching as you suggested. This method method works great, but I get a similar problem as with the RecordSource method: after hitting search numerous times, the database crashes (instead of hanging like it did with the RecordSource). When I open the database after the crash and zoom the SQL in the subform's source, the database crashes again. When I restore from a backup (created prior to the search crash), I can verify that the subform's source zoomed fine. Any insight? :S Thanks again for everything! – coffejor Oct 30 '12 at 22:19
  • Is the data stored in an access mdb? If yes compact and repair the mdb. You can do the same with the frontend (the application). – Olivier Jacot-Descombes Oct 30 '12 at 22:34
  • The data are stored in an Access MDB, and I have run a compact and repair. I have also run MS's JetComp utility to rebuild the database if the backup is inherently corrupted. I will attempt to rebuild the database tomorrow by copying everything to a new database file and will reapply your suggestions. Thanks again for everything, Olivier, you have been most helpful. – coffejor Oct 31 '12 at 02:01
  • I have completely rebuilt the database and re-compacted/repaired. Unfortunately, it has the same problem as before: when I click "Search" multiple times (usually any greater than three times), giving the search form enough time to load between clicks, the database crashes. Could this be a bug in Access? If it helps, I have the database save in Access 2000 mode. – coffejor Oct 31 '12 at 14:37
  • Just a shot in the dark: Do all your tables have primary keys? Did you replace all the bangs (!) by dots (.) for filed accesses (even in `qry_General:FullName_FMLD`)? Are you calling some VBA functions within `qry_General:FullName_FMLD`? – Olivier Jacot-Descombes Oct 31 '12 at 14:46
  • Olivier: You were 100% correct! I was calling some VBA in `qry_General:FullName_FMLD` so that I could concatenate the person's name with their respective degrees. As soon as I switched to the query `qry_General:FullName_FML` (which doesn't include a degree nor, consequently, any VBA) the problem was solved. I can now click "Search" as many times as I'd like without crashing the database. I am guessing that the root of the problem was that the form was attempting to apply the filter before the query had returned a result thereby creating a corrupt filter string. Thanks again! – coffejor Oct 31 '12 at 20:56

1 Answers1

0

As per Olivier's suggestions, the true cause of the problem was VBA being called as part of the query [qry_General:FullName_FMLD]; switching to [qry_General:FullName_FML] (which doesn't call any VBA) eliminated all problems. I am guessing that the root of the problem was that the form was attempting to apply the filter before the query had returned a result thereby creating a corrupt filter string.

Here is the updated code using a filter method and replacing all bangs with dots:

Private Sub Search_Click()
    On Error GoTo Err_Search_Click

    Dim SearchString, SearchStringTitle, SearchStringName, SearchStringDescription, SearchStringInvestigator, JoinValue, j, SQLString As String, SearchArray, varValue As Variant

        Me.Results.Form.FilterOn = True
        SearchString = Trim(Me.SearchValue)

        If Not IsNull(SearchString) Then
            SearchArray = Split(SearchString, " ")

            If Me.Join_Type <> 3 Then

                If Me.Join_Type = 1 Then
                    JoinValue = "OR"
                ElseIf Me.Join_Type = 2 Then
                    JoinValue = "AND"
                Else
                    JoinValue = " "
                End If

        '--
                SearchStringTitle = "(("
                For Each varValue In SearchArray
                    j = Trim(varValue)
                    SearchStringTitle = SearchStringTitle & "(tbl_Studies.Study_Short_Title) Like ""*" & j & "*"""

                    If varValue <> SearchArray(UBound(SearchArray)) Then
                        SearchStringTitle = SearchStringTitle & " " & JoinValue & " "
                    End If
                Next varValue
                SearchStringTitle = SearchStringTitle & "))"

        '--
                SearchStringName = "(("
                For Each varValue In SearchArray
                    j = Trim(varValue)
                    SearchStringName = SearchStringName & "(tbl_Studies.Study_Name) Like ""*" & j & "*"""

                    If varValue <> SearchArray(UBound(SearchArray)) Then
                        SearchStringName = SearchStringName & " " & JoinValue & " "
                    End If
                Next varValue
                SearchStringName = SearchStringName & "))"

        '--
                SearchStringDescription = "(("
                For Each varValue In SearchArray
                    j = Trim(varValue)
                    SearchStringDescription = SearchStringDescription & "(tbl_Studies.Study_Description) Like ""*" & j & "*"""

                    If varValue <> SearchArray(UBound(SearchArray)) Then
                        SearchStringDescription = SearchStringDescription & " " & JoinValue & " "
                    End If
                Next varValue
                SearchStringDescription = SearchStringDescription & "))"

        '--
                SearchStringInvestigator = "(("
                For Each varValue In SearchArray
                    j = Trim(varValue)
                    SearchStringInvestigator = SearchStringInvestigator & "([qry_General:FullName_FML].FullName) Like ""*" & j & "*"""

                    If varValue <> SearchArray(UBound(SearchArray)) Then
                        SearchStringInvestigator = SearchStringInvestigator & " " & JoinValue & " "
                    End If
                Next varValue
                SearchStringInvestigator = SearchStringInvestigator & "))"

                SearchString = SearchStringTitle & " OR " & SearchStringName & " OR " & SearchStringDescription & " OR " & SearchStringInvestigator
            Else
                SearchStringTitle = "(((tbl_Studies.Study_Short_Title) Like ""*" & SearchString & "*""))"
                SearchStringName = "(((tbl_Studies.Study_Name) Like ""*" & SearchString & "*""))"
                SearchStringInvestigator = "((([qry_General:FullName_FML].FullName) Like ""*" & SearchString & "*""))"
                SearchStringDescription = "(((tbl_Studies.Study_Description) Like ""*" & SearchString & "*""))"

                SearchString = SearchStringTitle & " OR " & SearchStringName & " OR " & SearchStringDescription & " OR " & SearchStringInvestigator
            End If

            Me.Results.Form.Filter = SearchString
        End If

    Exit_Search_Click:
        Exit Sub

    Err_Search_Click:
        MsgBox ("There are no active records to review.")
        Resume Exit_Search_Click
End Sub

Again, the credit for this solution belongs to Olivier Jacot-Descombes - thanks for all your help and suggestions!

coffejor
  • 11
  • 3