Asked by:
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
-
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.netMonday, 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