Convert form's Macros to Visual Basic (1 Viewer)

InFlight

InFlight

User
Local time
Tomorrow, 05:48
Joined
Jun 11, 2015
Messages
130
Hi
I used "Convert form's Macros to Visual Basic" but i am getting errors in the If(eval) line.
I have a text field called SearchBox on a form called Suppliers

Code: 
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 "SearchClear", acPropertyVisible, "0"
            DoCmd.SetProperty "iconSearchClear", acPropertyVisible, "0"
            DoCmd.SetProperty "SearchGo", 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 "SearchGo", acPropertyVisible, "-1"
        DoCmd.SetProperty "iconSearchGo", acPropertyVisible, "-1"
        If (Eval("([Form]![SearchBox] Is Null Or [Form]![SearchBox]="""") And [SearchClear].[Visible]<>0")) Then
            DoCmd.SetProperty "SearchClear", acPropertyVisible, "0"
            End
        End If
        ' Handle "'s in search
        TempVars.Add "strSearch", Replace(Forms![Contact List]!SearchBox, """", """""")
        ' Build the Filter
        TempVars.Add "strFilter", "([Last Name] Like "" * " & [TempVars]![strSearch] & " * "" )"
        TempVars.Add "strFilter", TempVars!strFilter & " OR ([First Name] Like "" * " & [TempVars]![strSearch] & " * "" )"
        TempVars.Add "strFilter", TempVars!strFilter & " OR ([E-mail Address] Like "" * " & [TempVars]![strSearch] & " * "" )"
        TempVars.Add "strFilter", TempVars!strFilter & " OR ([Company] Like "" * " & [TempVars]![strSearch] & " * "" )"
        TempVars.Add "strFilter", TempVars!strFilter & " OR ([Job Title] Like "" * " & [TempVars]![strSearch] & " * "" )"
        TempVars.Add "strFilter", TempVars!strFilter & " OR ([Category] Like "" * " & [TempVars]![strSearch] & " * "" )"
        TempVars.Add "strFilter", TempVars!strFilter & " OR ([Zip/Postal Code] Like "" * " & [TempVars]![strSearch] & " * "" )"
        DoCmd.ApplyFilter "", TempVars!strFilter, ""
        TempVars.Remove "strFilter"
        TempVars.Remove "strSearch"
        DoCmd.SetProperty "SearchClear", acPropertyVisible, "-1"
        DoCmd.SetProperty "iconSearchClear", acPropertyVisible, "-1"
        DoCmd.GoToControl "SearchBox"
        DoCmd.SetProperty "SearchGo", acPropertyVisible, "-1"
        DoCmd.SetProperty "iconSearchGo", acPropertyVisible, "-1"
    End With


Search_Exit:
    Exit Function

Search_Err:
    MsgBox Error$
    Resume Search_Exit

End Function


'------------------------------------------------------------
' Search_ClearFilter
'
'------------------------------------------------------------
Function Search_ClearFilter()
On Error GoTo Search_ClearFilter_Err

    With CodeContextObject
        ' Clear Filter
        DoCmd.ApplyFilter "", """""", ""
        DoCmd.GoToControl "SearchBox"
        DoCmd.SetProperty "SearchClear", acPropertyVisible, "0"
        DoCmd.SetProperty "iconSearchClear", acPropertyVisible, "0"
        DoCmd.SetProperty "SearchGo", acPropertyVisible, "-1"
        DoCmd.SetProperty "iconSearchGo", acPropertyVisible, "-1"
        If (CurrentProject.IsTrusted) Then
            .SearchBox = ""
        End If
        Exit Function
    End With


Search_ClearFilter_Exit:
    Exit Function

Search_ClearFilter_Err:
    MsgBox Error$
    Resume Search_ClearFilter_Exit

End Function
 
Ranman256

Ranman256

Well-known member
Local time
Today, 13:48
Joined
Apr 9, 2015
Messages
4,339
Remove all Evals. Vb dont need it.
Also, use IsNull(txtBox) .
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 10:48
Joined
Oct 29, 2018
Messages
21,454
Hi. Just curious, what does the error message say?
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 18:48
Joined
Sep 21, 2011
Messages
14,235
Is that code actually in the form?
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 18:48
Joined
Sep 21, 2011
Messages
14,235
pbaldy said:
The syntax referring to the form control doesn't look correct. See:

http://www.theaccessweb.com/forms/frm0031.htm
Click to expand...

Paul,

I tried
Code: 
? [Form]![ControlName]
in one of my forms, from the immediate window and it displayed that value correctly, so I believe the syntax is correct?, which is why I asked if it was actually in the form.?

Not sure what the Eval is for?, and Ranman256 advised removing them?
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:48
Joined
Aug 30, 2003
Messages
36,124
Curious, I get an object required error, but maybe I'm doing it wrong. I'd prefer the more explicit reference anyway.

Unless something has changed the convert macro to VBA functionality creates a standard module. The code would have to be moved to a form's module for that syntax to work I suspect.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 18:48
Joined
Sep 21, 2011
Messages
14,235
pbaldy said:
Curious, I get an object required error, but maybe I'm doing it wrong. I'd prefer the more explicit reference anyway.

Unless something has changed the convert macro to VBA functionality creates a standard module. The code would have to be moved to a form's module for that syntax to work I suspect.
Click to expand...

I set a breakpoint in a form and then issued the command in the immediate window.

TBH I thought you had to refer to a form by name or Me, but it appears not to be the case.
 
InFlight

InFlight

User
Local time
Tomorrow, 05:48
Joined
Jun 11, 2015
Messages
130
Thanks all for your help. i got fixed.

Code: 
    With CodeContextObject
        If IsNull(SearchBox) Or [SearchBox] = """" Then
            ' Clear Filter when search box empty
            DoCmd.ApplyFilter "", """""", ""
            DoCmd.GoToControl "SearchBox"
            DoCmd.SetProperty "SearchClear", acPropertyVisible, "0"
            DoCmd.SetProperty "iconSearchClear", acPropertyVisible, "0"
            DoCmd.SetProperty "SearchGo", acPropertyVisible, "-1"
            DoCmd.SetProperty "iconSearchGo", acPropertyVisible, "-1"
        End If
        
        If CurrentProject.IsTrusted And IsNull(SearchBox) Or [SearchBox] = """" Then
            .SearchBox.Text = ""
        End If
        If IsNull(SearchBox) Or [SearchBox] = """" Then
            End
        End If
        If (VarType(.Form!SearchBox) <> 8) Then
            End
        End If
        DoCmd.SetProperty "SearchGo", acPropertyVisible, "-1"
        DoCmd.SetProperty "iconSearchGo", acPropertyVisible, "-1"
        If IsNull(SearchBox) Or [SearchBox] = """" And [SearchClear].[Visible] <> 0 Then
            DoCmd.SetProperty "SearchClear", acPropertyVisible, "0"
            End
        End If
        ' Handle "'s in search
        TempVars.Add "strSearch", Replace(Forms!Supplies!SearchBox, """", """""")
        ' Build the Filter
        TempVars.Add "strFilter", "([Last Name] Like "" * " & [TempVars]![strSearch] & " * "" )"
        TempVars.Add "strFilter", TempVars!strFilter & " OR ([First Name] Like "" * " & [TempVars]![strSearch] & " * "" )"
        TempVars.Add "strFilter", TempVars!strFilter & " OR ([E-mail Address] Like "" * " & [TempVars]![strSearch] & " * "" )"
        TempVars.Add "strFilter", TempVars!strFilter & " OR ([Company] Like "" * " & [TempVars]![strSearch] & " * "" )"
        TempVars.Add "strFilter", TempVars!strFilter & " OR ([Job Title] Like "" * " & [TempVars]![strSearch] & " * "" )"
        TempVars.Add "strFilter", TempVars!strFilter & " OR ([Category] Like "" * " & [TempVars]![strSearch] & " * "" )"
        TempVars.Add "strFilter", TempVars!strFilter & " OR ([Zip/Postal Code] Like "" * " & [TempVars]![strSearch] & " * "" )"
        DoCmd.ApplyFilter "", TempVars!strFilter, ""
        TempVars.Remove "strFilter"
        TempVars.Remove "strSearch"
        DoCmd.SetProperty "SearchClear", acPropertyVisible, "-1"
        DoCmd.SetProperty "iconSearchClear", acPropertyVisible, "-1"
        DoCmd.GoToControl "SearchBox"
        DoCmd.SetProperty "SearchGo", acPropertyVisible, "-1"
        DoCmd.SetProperty "iconSearchGo", acPropertyVisible, "-1"
    End With
 
A

AccessBlaster

Registered User.
Local time
Today, 10:48
Joined
May 22, 2010
Messages
5,919
Its hard to believe all that code was stuffed into a macro. Usually the conversion adds a lot recognizable trash to the code.
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:48
Joined
Aug 30, 2003
Messages
36,124
AccessBlaster said:
Usually the conversion adds a lot recognizable trash to the code.
Click to expand...

Yeah, I've always looked at the code created by the conversion as a starting point, not the finished product (not that I've used it much).
 
You must log in or register to reply here.

Similar threads

T
Solved Filter form Only
Replies
9
Views
712
arnelgp
arnelgp
oxicottin
How to stop form from closing
Replies
7
Views
720
Pat Hartman
P
Sam Summers
sending an email from a form in access 2019 to outlook 365
2
Replies
23
Views
2,117
Sam Summers
Sam Summers
S
Modifications to code in Access to modify Word doc
Replies
17
Views
1,363
Pat Hartman
P
F
MultiSelect ListBox Error
Replies
10
Views
1,142
moke123
moke123

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom