locked
Converted Macro RRS feed

  • Question

  • I have converted a search macro into, but I get an error.  I believe the correction would be to set the Tools/Reference, but I don't know which one.  Please assist.

    Option Compare Database '------------------------------------------------------------ ' Search ' '------------------------------------------------------------ Function Search() On Error GoTo Search_Err With CodeContextObject If (Eval("[Form]![SearchBox] Is Null Or [Form]![SearchBox]=""""")) Then ' Clear Filter when search box empty DoCmd.ApplyFilter "", """""", "" DoCmd.GoToControl "SearchBox" DoCmd.SetProperty "cmdSearchClear", acPropertyVisible, "0" DoCmd.SetProperty "iconSearchClear", acPropertyVisible, "0" DoCmd.SetProperty "cmdSearchGo", acPropertyVisible, "-1" DoCmd.SetProperty "iconSearchGo", acPropertyVisible, "-1" End If If (Eval("[CurrentProject].[IsTrusted] And ([Form]![SearchBox] Is Null Or [Form]![SearchBox]="""")")) Then .SearchBox.Text = "" End If If (Eval("[Form]![SearchBox] Is Null Or [Form]![SearchBox]=""""")) Then End End If If (VarType(.Form!SearchBox) <> 8) Then End End If DoCmd.SetProperty "cmdSearchGo", acPropertyVisible, "-1" DoCmd.SetProperty "iconSearchGo", acPropertyVisible, "-1" If (Eval("([Form]![SearchBox] Is Null Or [Form]![SearchBox]="""") And [cmdSearchClear].[Visible]<>0")) Then DoCmd.SetProperty "cmdSearchClear", acPropertyVisible, "0" DoCmd.SetProperty "iconSearchClear", acPropertyVisible, "0" End End If ' Handle "'s in search TempVars.Add "strSearch", Replace(.Form!SearchBox, """", """""") ' Build the Filter for the Task list If (.Form.Name = "Task List") Then TempVars.Add "strFilter", "([Title] Like "" * " & [TempVars]![strSearch] & " * "" )" End If If (.Form.Name = "Task List") Then TempVars.Add "strFilter", TempVars!strFilter & " OR ([Description] Like "" * " & [TempVars]![strSearch] & " * "" )" End If If (.Form.Name = "Task List") Then TempVars.Add "strFilter", TempVars!strFilter & " OR ([Status] Like "" * " & [TempVars]![strSearch] & " * "" )" End If If (.Form.Name = "Task List") Then TempVars.Add "strFilter", TempVars!strFilter & " OR ([Priority] Like "" * " & [TempVars]![strSearch] & " * "" )" End If ' Build the Filter for the Contact list If (.Form.Name = "Contact List") Then TempVars.Add "strFilter", "([Last Name] Like "" * " & [TempVars]![strSearch] & " * "" )" End If If (.Form.Name = "Contact List") Then TempVars.Add "strFilter", TempVars!strFilter & " OR ([First Name] Like "" * " & [TempVars]![strSearch] & " * "" )" End If If (.Form.Name = "Contact List") Then TempVars.Add "strFilter", TempVars!strFilter & " OR ([E-mail Address] Like "" * " & [TempVars]![strSearch] & " * "" )" End If If (.Form.Name = "Contact List") Then TempVars.Add "strFilter", TempVars!strFilter & " OR ([Company] Like "" * " & [TempVars]![strSearch] & " * "" )" End If If (.Form.Name = "Contact List") Then TempVars.Add "strFilter", TempVars!strFilter & " OR ([Job Title] Like "" * " & [TempVars]![strSearch] & " * "" )" End If If (.Form.Name = "Contact List") Then TempVars.Add "strFilter", TempVars!strFilter & " OR ([Notes] Like "" * " & [TempVars]![strSearch] & " * "" )" End If If (.Form.Name = "Contact List") Then TempVars.Add "strFilter", TempVars!strFilter & " OR ([Zip/Postal Code] Like "" * " & [TempVars]![strSearch] & " * "" )" End If ' Apply the Filter DoCmd.ApplyFilter "", TempVars!strFilter, "" TempVars.Remove "strFilter" TempVars.Remove "strSearch" DoCmd.SetProperty "cmdSearchClear", acPropertyVisible, "-1" DoCmd.SetProperty "iconSearchClear", acPropertyVisible, "-1" DoCmd.GoToControl "SearchBox" DoCmd.SetProperty "cmdSearchGo", acPropertyVisible, "-1" DoCmd.SetProperty "iconSearchGo", acPropertyVisible, "-1" End With Search_Exit: Exit Function Search_Err: MsgBox Error$ Resume Search_Exit

    End Function


    Monday, December 14, 2020 12:06 AM

All replies

  • What error do you receive exactly?

    When you get the error and if you debug it, which line of code gets highlighted as the error?


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Monday, December 14, 2020 12:57 AM
  • I receive a message that says, "The expression you entered refers to an object that is closed or doesn't exist."  It did not go to debug it, nor highlight a section.
    Monday, December 14, 2020 5:12 PM
  • You need "Option Explicit" at the top of each module.

    Set it to be the default for new modules using Tools > Options > Require variable declaration.

    Then use Debug > Compile and fix all compile errors.

    Next, you need to understand that code like "DoCmd.GoToControl "SearchBox"" requires a form to be open and a SearchBox control to be present.

    When this code runs and you get an error, hit Ctrl+Break and you will break into the debugger and then you can tell us which line is causing the problem. And maybe fix it on your own.


    -Tom. Microsoft Access MVP

    • Proposed as answer by KHURRAM RAHIM Wednesday, December 16, 2020 7:23 PM
    Monday, December 14, 2020 5:19 PM
  • I receive a dialog box that states, "The expression you entered refers to an object that is closed or doesn't exist."
    Monday, December 14, 2020 9:24 PM
  • I have completed your recommendation and I still get the same error.  However, because of your CNTL Break trick the Resume  Search_Exit is highlight.  Any thoughts?
    Wednesday, December 16, 2020 1:02 AM
  • And then you did what I told you to do, and what happened?

    When this code runs and you get an error, hit Ctrl+Break and you will break into the debugger and then you can tell us which line is causing the problem. And maybe fix it on your own.

    -Tom. Microsoft Access MVP

    Wednesday, December 16, 2020 1:18 AM
  • I still receive:  "The expression you entered refers to an object that is closed or doesn't exist."

    Search_Exit is highlighted

    Wednesday, December 16, 2020 3:27 AM
  • I still receive a dialog box that states, "The expression you entered refers to an object that is closed or doesn't exist.   The portion that is highlighted when I hit Ctrl+Break is:

    Search_Exit

    I am at a loss at what to do. 

    Wednesday, December 16, 2020 4:54 PM
  • Hi 1775

    I added row numbers to your code and an error handler using those row numbers.

    Replace your code with mine and try again.

    Option Compare Database
    Option Explicit
    
    '------------------------------------------------------------
    ' Search
    '
    '------------------------------------------------------------
    Function Search()
    
        On Error GoTo Search_Error
    10        With CodeContextObject
    20            If (Eval("[Form]![SearchBox] Is Null Or [Form]![SearchBox]=""""")) Then
                      ' Clear Filter when search box empty
    30                DoCmd.ApplyFilter "", """""", ""
    40                DoCmd.GoToControl "SearchBox"
    50                DoCmd.SetProperty "cmdSearchClear", acPropertyVisible, "0"
    60                DoCmd.SetProperty "iconSearchClear", acPropertyVisible, "0"
    70                DoCmd.SetProperty "cmdSearchGo", acPropertyVisible, "-1"
    80                DoCmd.SetProperty "iconSearchGo", acPropertyVisible, "-1"
    90            End If
    100           If (Eval("[CurrentProject].[IsTrusted] And ([Form]![SearchBox] Is Null Or [Form]![SearchBox]="""")")) Then
    110               .SearchBox.Text = ""
    120           End If
    130           If (Eval("[Form]![SearchBox] Is Null Or [Form]![SearchBox]=""""")) Then
    140               End
    150           End If
    160           If (VarType(.Form!SearchBox) <> 8) Then
    170               End
    180           End If
    190           DoCmd.SetProperty "cmdSearchGo", acPropertyVisible, "-1"
    200           DoCmd.SetProperty "iconSearchGo", acPropertyVisible, "-1"
    210           If (Eval("([Form]![SearchBox] Is Null Or [Form]![SearchBox]="""") And [cmdSearchClear].[Visible]<>0")) Then
    220               DoCmd.SetProperty "cmdSearchClear", acPropertyVisible, "0"
    230               DoCmd.SetProperty "iconSearchClear", acPropertyVisible, "0"
    240               End
    250           End If
                  ' Handle "'s in search
    260           TempVars.Add "strSearch", Replace(.Form!SearchBox, """", """""")
                  ' Build the Filter for the Task list
    270           If (.Form.Name = "Task List") Then
    280               TempVars.Add "strFilter", "([Title] Like "" * " & [TempVars]![strSearch] & " * "" )"
    290           End If
    300           If (.Form.Name = "Task List") Then
    310               TempVars.Add "strFilter", TempVars!strFilter & " OR ([Description] Like "" * " & [TempVars]![strSearch] & " * "" )"
    320           End If
    330           If (.Form.Name = "Task List") Then
    340               TempVars.Add "strFilter", TempVars!strFilter & " OR ([Status] Like "" * " & [TempVars]![strSearch] & " * "" )"
    350           End If
    360           If (.Form.Name = "Task List") Then
    370               TempVars.Add "strFilter", TempVars!strFilter & " OR ([Priority] Like "" * " & [TempVars]![strSearch] & " * "" )"
    380           End If
                  ' Build the Filter for the Contact list
    390           If (.Form.Name = "Contact List") Then
    400               TempVars.Add "strFilter", "([Last Name] Like "" * " & [TempVars]![strSearch] & " * "" )"
    410           End If
    420           If (.Form.Name = "Contact List") Then
    430               TempVars.Add "strFilter", TempVars!strFilter & " OR ([First Name] Like "" * " & [TempVars]![strSearch] & " * "" )"
    440           End If
    450           If (.Form.Name = "Contact List") Then
    460               TempVars.Add "strFilter", TempVars!strFilter & " OR ([E-mail Address] Like "" * " & [TempVars]![strSearch] & " * "" )"
    470           End If
    480           If (.Form.Name = "Contact List") Then
    490               TempVars.Add "strFilter", TempVars!strFilter & " OR ([Company] Like "" * " & [TempVars]![strSearch] & " * "" )"
    500           End If
    510           If (.Form.Name = "Contact List") Then
    520               TempVars.Add "strFilter", TempVars!strFilter & " OR ([Job Title] Like "" * " & [TempVars]![strSearch] & " * "" )"
    530           End If
    540           If (.Form.Name = "Contact List") Then
    550               TempVars.Add "strFilter", TempVars!strFilter & " OR ([Notes] Like "" * " & [TempVars]![strSearch] & " * "" )"
    560           End If
    570           If (.Form.Name = "Contact List") Then
    580               TempVars.Add "strFilter", TempVars!strFilter & " OR ([Zip/Postal Code] Like "" * " & [TempVars]![strSearch] & " * "" )"
    590           End If
                  ' Apply the Filter
    600           DoCmd.ApplyFilter "", TempVars!strFilter, ""
    610           TempVars.Remove "strFilter"
    620           TempVars.Remove "strSearch"
    630           DoCmd.SetProperty "cmdSearchClear", acPropertyVisible, "-1"
    640           DoCmd.SetProperty "iconSearchClear", acPropertyVisible, "-1"
    650           DoCmd.GoToControl "SearchBox"
    660           DoCmd.SetProperty "cmdSearchGo", acPropertyVisible, "-1"
    670           DoCmd.SetProperty "iconSearchGo", acPropertyVisible, "-1"
    680       End With
    
    
    
        
        On Error GoTo 0
        Exit Function
    
    Search_Error:
    
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Search, line " & Erl & "."
    
    End Function
    
    



    Cheers // Peter Forss Stockholm

    Wednesday, December 16, 2020 5:43 PM
  • @Peter:

        On Error GoTo 0
        Exit Function

    What is the purpose of that first line? I don't get it.


    -Tom. Microsoft Access MVP

    Thursday, December 17, 2020 3:07 AM
  • Hi Tom

    the row numbers and error handler is generated by MZ tools (add in).

    The information I have is this one:

    Example: On Error GoTo 0

    After the first error is raised, you will receive the error as error handling has been disabled.

    Sub OnErrorGotoZeroTest()
    
        On Error GoTo 0
    
        Err.Raise Number:=9999, Description:="Forced Error"
    
    End Sub


    Cheers // Peter Forss Stockholm

    Thursday, December 17, 2020 7:00 AM
  • I am not sure what this means, but after I run it a Dialog box pops open and I receive the following:


    There was an error compiling this function.
    The Visual Basic module contains a syntax error.
    check the code, and then recompile it.

    Saturday, December 19, 2020 4:03 AM
  • UPDATE:  I realized there was an error in the code, but was able to get it to work.  I now receive the following when i run the new code you provided: 

    Error 2467 (The expression you entered refers to an object that is closed or doesn't exist.) in procedure Search, Line 10.

    What does this mean?

    Wednesday, December 23, 2020 4:27 PM
  • Hi 1775

    What if you delete row 10 and 680 ?


    Cheers // Peter Forss Stockholm

    Sunday, January 3, 2021 8:18 AM