Convert form's Macros to Visual Basic

InFlight

InFlight

User
Local time
Today, 16:41
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
 
Remove all Evals. Vb dont need it.
Also, use IsNull(txtBox) .
 
Hi. Just curious, what does the error message say?
 
Is that code actually in the form?
 
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?
 
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.
 
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.
 
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
 
Its hard to believe all that code was stuffed into a macro. Usually the conversion adds a lot recognizable trash to the code.
 
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

K
How can I add this code to the storage location of my file before scanning and then convert it to a PDF file?
2
Replies
25
Views
1,010
The_Doc_Man
The_Doc_Man
T
Solved Filter form Only
Replies
9
Views
884
arnelgp
arnelgp
N
Solved How to force MS Access display a primary key on the forms after entering data in the first field
Replies
6
Views
546
Pat Hartman
P
Uncle Gizmo
Seeking Elegant Solution for Traversing Control Hierarchy to Find Parent Form
Replies
8
Views
851
MarkK
MarkK
oxicottin
How to stop form from closing
Replies
7
Views
1,238
Pat Hartman
P

Users who are viewing this thread

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