asked on

Search Macro help in Access

Having trouble in editing an existing macro within the Assets Tracking template in Access. I need the search macro to be able to search through a table and bring it back to the form. However, I have never worked with macros before and I am unsure how to do this. Attaching a picture of the area of the macro that needs attention, although to be honest I am unsure what needs to be updated. I've been replacing the old fields with the new ones.

Daniel Pineault

This is my personal opinion of course, and I know I'm not answering your immediate question, but I'd convert any and all macros to VBA events. VBA is much, much easier to work with!!!

Mark Edwards

Macros are Microsoft's pre-programmed attempt at no-coding app building. But a common shortcoming of no-coding pre-programmed macros (or any no-programming app building software from anybody) is that you are stuck with whatever capabilities Microsoft programmed into them. If they can't do what you want (and there is a lot that they can't do), then you go to VBA code. Once you start using code, you'll never go back to doing it the hard way with macros.

For what it is worth, macros have a "RunCode" action which will run a public VBA function built in a module.

Trust me, I've been there...…...

PatHartman

There is an icon on the ribbon that says "convert macro to vba". Unfortunately, the code generated will be convoluted and we may still have trouble with it but start there and then we need more details on what change you need to make. Or, maybe someone who works with macros will show up but as Dan and Mark have said, most experts just don't use macros because once you get beyond very simple tasks, they are actually more complicated than code.

Bitsqueezer

Hi,

the real funny thing is that the macro code you've shown above already looks like VBA code so if you understand what's happening here you would also understand the same code in VBA as there is not much difference...

So forget macros as all the others already said and write the code always in VBA.

You can access everything which is possible in macros in VBA using the "DoCmd" object but in macros you can only use these, in VBA you can do a lot more.

Your macro code clears the "txtSearchBox" field, checks if the Access file is trusted and sets the "Order" and "OrderBy" properties of the form from a TempVars variable (which is a collection in Access containing freely definable variables, the only possible kind of variables in macros).

So there is no code to search anything, I think as the displayed scrollbar is longer the following code will filter a form due to an input of the user in the "txtSearchBox" textbox. That's not a search, it's a filter. A search would display all records and go to the (next) found record, still displaying all records where a filtering would only display (all) matching records of a filter string and not displaying the rest.

I think you should first define for yourself what functionality do you expect: Search or filter (and in which fields, only one or more than one), with or without ordering the records and if, ordering which fields in which direction.

Cheers,

Christian

Natalie Hamilton

ASKER

Hi all,

Thank you for your comments. It is a listed as a txtSearchBox but the event is to be a Filters.QuickSearch. So really what I would like to do is to be able to type anything into that box and have it filter for it and also save the filter (something that comes with the template).

ASKER CERTIFIED SOLUTION

Daniel Pineault

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Natalie Hamilton

ASKER

This is the VBA code. From what I can gather it's for all the filters. I don't have a retired date, or contacts. I've also tried replacing the 'IDs' with "Case#' since that's what my data is based on.

Option Compare Database

'------------------------------------------------------------
' Filters1
'
'------------------------------------------------------------
Function Filters1()
On Error GoTo Filters1_Err

    Beep
    ' Macro can't be run from the navigation pane.
    Exit Function


Filters1_Exit:
    Exit Function

Filters1_Err:
    MsgBox Error$
    Resume Filters1_Exit

End Function


'------------------------------------------------------------
' Filters_ApplyFilterFavorite1
'
'------------------------------------------------------------
Function Filters_ApplyFilterFavorite1()
On Error GoTo Filters_ApplyFilterFavorite1_Err

    With CodeContextObject
        If (IsNull(Screen.ActiveControl) Or Screen.ActiveControl = 0) Then
            ' Clear Filters.
            DoCmd.RunMacro "Filters.ClearFilter", , ""
            Exit Function
        End If
        If (Screen.ActiveControl = -1) Then
            ' Manage Filters.
            DoCmd.RunMacro "Filters.Manage", , ""
            Exit Function
        End If
        ' Apply Filters
        TempVars.Add "FilterString", DLookup("[Filter String]", "Filters", "ID = " & Screen.ActiveControl)
        TempVars.Add "SortString", DLookup("[Sort String]", "Filters", "ID = " & Screen.ActiveControl)
        If (Not IsNull(TempVars!FilterString)) Then
            DoCmd.ApplyFilter "", Eval("[TempVars]![FilterString] & IIf(Not [Form]![chkShowRetired],""and ([Retired Date ] is null or [Retired Date ] > date())"","""")"), ""
        End If
        If (Not IsNull(TempVars!FilterString)) Then
            .txtSearchBox = ""
        End If
        If (CurrentProject.IsTrusted And Not IsNull(TempVars!SortString)) Then
            .OrderBy = Nz(TempVars!SortString)
        End If
        If (CurrentProject.IsTrusted) Then
            .OrderByOn = Not IsNull(TempVars!SortString)
        End If
        TempVars.Remove "FilterString"
        TempVars.Remove "SortString"
    End With


Filters_ApplyFilterFavorite1_Exit:
    Exit Function

Filters_ApplyFilterFavorite1_Err:
    MsgBox Error$
    Resume Filters_ApplyFilterFavorite1_Exit

End Function


'------------------------------------------------------------
' Filters_New1
'
'------------------------------------------------------------
Function Filters_New1()
On Error GoTo Filters_New1_Err

    With CodeContextObject
        DoCmd.RunMacro "Filters.CheckFilter", , ""
        DoCmd.RunMacro "Filters.SetupTempVars", , ""
        DoCmd.OpenForm "Filter Details", acNormal, "", "", acAdd, acDialog
        DoCmd.Requery "cboFilterFavorites"
        If (CurrentProject.IsTrusted) Then
            .Form!cboFilterFavorites = TempVars!LastFilterCreated
        End If
        DoCmd.RunMacro "Filters.RemoveTempVars", , ""
    End With


Filters_New1_Exit:
    Exit Function

Filters_New1_Err:
    MsgBox Error$
    Resume Filters_New1_Exit

End Function


'------------------------------------------------------------
' Filters_SetLastFilterID1
'
'------------------------------------------------------------
Function Filters_SetLastFilterID1()
On Error GoTo Filters_SetLastFilterID1_Err

    With CodeContextObject
        ' Used in conjunction with NEW to set the value of the combo box when trusted.
        TempVars.Add "LastFilterCreated", .ID
    End With


Filters_SetLastFilterID1_Exit:
    Exit Function

Filters_SetLastFilterID1_Err:
    MsgBox Error$
    Resume Filters_SetLastFilterID1_Exit

End Function


'------------------------------------------------------------
' Filters_Manage1
'
'------------------------------------------------------------
Function Filters_Manage1()
On Error GoTo Filters_Manage1_Err

    TempVars.Add "ObjectType", Application.CurrentObjectType
    TempVars.Add "ObjectName", Application.CurrentObjectName
    DoCmd.OpenForm "Filter Details", acNormal, "", "[Object Name]=[Application].[CurrentObjectName]", , acDialog
    DoCmd.RunCommand acCmdRefresh
    TempVars.Remove "ObjectType"
    TempVars.Remove "ObjectName"


Filters_Manage1_Exit:
    Exit Function

Filters_Manage1_Err:
    MsgBox Error$
    Resume Filters_Manage1_Exit

End Function


'------------------------------------------------------------
' Filters_SetupTempVars1
'
'------------------------------------------------------------
Function Filters_SetupTempVars1()
On Error GoTo Filters_SetupTempVars1_Err

    With CodeContextObject
        TempVars.Add "ObjectType", Application.CurrentObjectType
        TempVars.Add "ObjectName", Application.CurrentObjectName
        TempVars.Add "FilterString", .Filter
        TempVars.Add "SortString", .OrderBy
    End With


Filters_SetupTempVars1_Exit:
    Exit Function

Filters_SetupTempVars1_Err:
    MsgBox Error$
    Resume Filters_SetupTempVars1_Exit

End Function


'------------------------------------------------------------
' Filters_RemoveTempVars1
'
'------------------------------------------------------------
Function Filters_RemoveTempVars1()
On Error GoTo Filters_RemoveTempVars1_Err

    TempVars.Remove "ObjectType"
    TempVars.Remove "ObjectName"
    TempVars.Remove "FilterString"
    TempVars.Remove "SortString"
    TempVars.Remove "Order"
    TempVars.Remove "LastFilterCreated"


Filters_RemoveTempVars1_Exit:
    Exit Function

Filters_RemoveTempVars1_Err:
    MsgBox Error$
    Resume Filters_RemoveTempVars1_Exit

End Function


'------------------------------------------------------------
' Filters_ClearFilter1
'
'------------------------------------------------------------
Function Filters_ClearFilter1()
On Error GoTo Filters_ClearFilter1_Err

    With CodeContextObject
        If (CurrentProject.IsTrusted And .Form.Name = "Asset List") Then
            .Form!cboFilterFavorites = ""
        End If
        ' Clear Filter
        DoCmd.ApplyFilter "", """""", ""
        If (.Form.Name = "Asset List" And Not (.Form!chkShowRetired)) Then
            ' Filter out Retired assets if checkbox is cleared
            DoCmd.ApplyFilter "", Eval("""[Retired Date ] is null or [Retired Date ] > date()"""), ""
        End If
        DoCmd.GoToControl "txtSearchBox"
        DoCmd.SetProperty "cmdSearchClear", acPropertyVisible, "0"
        DoCmd.SetProperty "iconSearchClear", acPropertyVisible, "0"
        DoCmd.SetProperty "cmdSearchGo", acPropertyVisible, "-1"
        If (CurrentProject.IsTrusted) Then
            .txtSearchBox = ""
        End If
        Exit Function
    End With


Filters_ClearFilter1_Exit:
    Exit Function

Filters_ClearFilter1_Err:
    MsgBox Error$
    Resume Filters_ClearFilter1_Exit

End Function


'------------------------------------------------------------
' Filters_CheckFilter1
'
'------------------------------------------------------------
Function Filters_CheckFilter1()
On Error GoTo Filters_CheckFilter1_Err

    With CodeContextObject
        If (Not (.Form.FilterOn Or .Form.OrderByOn)) Then
            Beep
            MsgBox "You don't have a filter or sort to save.", vbOKOnly, "Save Filter"
            End
                End If
    End With


Filters_CheckFilter1_Exit:
    Exit Function

Filters_CheckFilter1_Err:
    MsgBox Error$
    Resume Filters_CheckFilter1_Exit

End Function


'------------------------------------------------------------
' Filters_QuickSearch1
'
'------------------------------------------------------------
Function Filters_QuickSearch1()
On Error GoTo Filters_QuickSearch1_Err

    With CodeContextObject
        If (.txtSearchBox = "") Then
            ' Clear Filter when search box empty
            DoCmd.RunMacro "Filters.ClearFilter", , ""
            End
        End If
        If (VarType(.txtSearchBox) <> 8) Then
            End
        End If
        DoCmd.SetProperty "cmdSearchGo", acPropertyVisible, "-1"
        If (.txtSearchBox = "" And .cmdSearchClear.Visible <> 0) Then
            DoCmd.SetProperty "cmdSearchClear", acPropertyVisible, "0"
            End
            DoCmd.SetProperty "iconSearchClear", acPropertyVisible, "0"
        End If
        ' Handle "'s in search
        TempVars.Add "strSearch", Replace(.txtSearchBox, """", """""")
        ' Build the Filter for the Asset list
        If (.Form.Name = "Asset List") Then
            TempVars.Add "strFilter", "([Item] Like "" * " & [TempVars]![strSearch] & " * "" )"
        End If
        If (.Form.Name = "Asset List") Then
            TempVars.Add "strFilter", TempVars!strFilter & " OR ([Description] Like "" * " & [TempVars]![strSearch] & " * "" )"
        End If
        If (.Form.Name = "Asset List") Then
            TempVars.Add "strFilter", TempVars!strFilter & " OR ([Comments] Like "" * " & [TempVars]![strSearch] & " * "" )"
        End If
        If (.Form.Name = "Asset List") Then
            TempVars.Add "strFilter", TempVars!strFilter & " OR ([Manufacturer] Like "" * " & [TempVars]![strSearch] & " * "" )"
        End If
        If (.Form.Name = "Asset List") Then
            TempVars.Add "strFilter", TempVars!strFilter & " OR ([Model] Like "" * " & [TempVars]![strSearch] & " * "" )"
        End If
        If (.Form.Name = "Asset List") Then
            TempVars.Add "strFilter", Eval("IIf(Not [Form]![chkShowRetired],""([Retired Date ] is null or [Retired Date ] > date()) AND ("" & [TempVars]![strFilter] & "")"",[TempVars]![strFilter])")
        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 "txtSearchBox"
        DoCmd.SetProperty "cmdSearchGo", acPropertyVisible, "-1"
    End With


Filters_QuickSearch1_Exit:
    Exit Function

Filters_QuickSearch1_Err:
    MsgBox Error$
    Resume Filters_QuickSearch1_Exit

End Function

Open in new window

Natalie Hamilton

ASKER

Thank you everyone, really appreciate your help on this issue.