Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2008
    Posts
    15

    Unanswered: Search Form Error

    I have a form with a button that when pressed brings up a search form. The search form searches a query from which the primary form data is extracted.

    The search is returning the following error Compile Error: Sub or Function not Defined.

    I think there is a problem because my form name has spaces in it. Unfortunately, it is referenced many times in the database and I do not want to change it.

    The code for the search form appears below:

    Code:
     Private Sub cmdSearch_Click()    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then        MsgBox "You must select a field to search."     ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then        MsgBox "You must enter search text."     Else         'Generate search criteria        GCriteria = "Replace ([" & cboSearchField.Value & "], chr(39), '')" & " LIKE '*" & Replace(txtSearchString, "'", "") & "*'"         'Filter [TEST Master Form by Ult Parent] based on search criteria        Form_("[TEST Master Form by Ult Parent].RecordSource") = "select * from [Ultimate Parent Table Query - Organizational Profile Report] where " & GCriteria        Form_("[TEST Master Form by Ult Parent].Caption") = "[Ultimate Parent Table Query - Organizational Profile Report] (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"""         'Close frmSearch        DoCmd.Close acForm, "frmSearch"         MsgBox "Results have been filtered."     End If End Sub
    Any help or advice would be much appreciated.

    Thank you

  2. #2
    Join Date
    Jul 2008
    Posts
    15
    Sorry, I did not post the code properly.

    Code:
    Private Sub cmdSearch_Click()
        If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
            MsgBox "You must select a field to search."
     
        ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
            MsgBox "You must enter search text."
     
        Else
     
            'Generate search criteria
            GCriteria = "Replace ([" & cboSearchField.Value & "], chr(39), '')" & " LIKE '*" & Replace(txtSearchString, "'", "") & "*'"
     
            'Filter [TEST Master Form by Ult Parent] based on search criteria
            Form_("[TEST Master Form by Ult Parent].RecordSource") = "select * from [Ultimate Parent Table Query - Organizational Profile Report] where " & GCriteria
            Form_("[TEST Master Form by Ult Parent].Caption") = "[Ultimate Parent Table Query - Organizational Profile Report] (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"""
     
            'Close frmSearch
            DoCmd.Close acForm, "frmSearch"
     
            MsgBox "Results have been filtered."
     
        End If
     
    End Sub

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your problem is
    Code:
    Form_(
    Try
    Code:
    Forms![TEST Master Form by Ult Parent].Recordsource
    George
    Home | Blog

  4. #4
    Join Date
    Jul 2008
    Posts
    15
    Thank you thank you thank you.

    I can't believe it was that simple.

    Thank you

  5. #5
    Join Date
    Jul 2008
    Posts
    15
    I spoke too soon.

    The search is functioning properly now, but another bump has come up. When the form with the search button is opened, it is opened in a pre-filtered condition (as if a search has already been performed). It is searching for the letter "L" in the Ultimate Parent field.

    I'm at a loss why this is. I can reset the search and all the records are there. But if I reopen the document, the records are displayed again with the filter applied.

    This is the current code for the search form:

    Code:
    Private Sub cmdSearch_Click()
    
        If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
            MsgBox "You must select a field to search."
            
        ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
            MsgBox "You must enter search text."
            
        Else
        
            'Generate search criteria
            GCriteria = "[" & cboSearchField.Value & "] LIKE '*" & Replace(txtSearchString, "'", "") & "*'"
            
            'Filter [TEST Master Form by Ult Parent] based on search criteria
            Forms![TEST Master Form by Ult Parent].RecordSource = "select * from [Ultimate Parent Table] where " & GCriteria
            Forms![TEST Master Form by Ult Parent].Caption = "[Ultimate Parent Table] (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
            
            'Close frmSearch
            DoCmd.Close acForm, "frmSearch"
            
            MsgBox "Results have been filtered."
            
        End If
        
    End Sub
    Can you spot anything in the code that would cause this to happen?

    Much thanks

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well, your code runs on the click event... So we know it's not this that is firing to cause the problem.

    What's the recordsource of the form set to when viewing the properties in design view? What other events to you have executing as the form loads?
    George
    Home | Blog

  7. #7
    Join Date
    Jul 2008
    Posts
    15
    That was it. The record source was set to:

    SELECT * FROM [Ultimate Parent Table] WHERE Replace([Ultimate Parent],chr(39),'') Like '*l*';

    There's no harm in me changing it to:
    SELECT * FROM [Ultimate Parent Table] WHERE Replace([Ultimate Parent],chr(39),'');
    ?

    The caption was also set to the same thing. I have no idea how this happened.

    In any event, that you for your help.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You can change it to anything you want... It just depends on what you want displayed!
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •