Remove From My Forums

Converted Macro

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

1

Sign in to vote

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
1

Sign in to vote

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
2

Sign in to vote
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
1

Sign in to vote

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
0

Sign in to vote

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
1

Sign in to vote

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
0

Sign in to vote

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
0

Sign in to vote

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

1

Sign in to vote
@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
1

Sign in to vote
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
1

Sign in to vote

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
1

Sign in to vote

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
0

Sign in to vote

Hi 1775

What if you delete row 10 and 680 ?
Cheers // Peter Forss Stockholm

Sunday, January 3, 2021 8:18 AM

Asked by:

Converted Macro

Question

All replies