Creating A Form Search

[requiemzz]

I need to create a simple search on a form with a list. Basically, all I need it to do is search the list for any given string, a user enters a string into a sear box and it queries the form. Currently, I am using a split form with a split database. I am not sure if I need to use a macro or setup up a quick filter to do this, Any I am happy to post more info if you need. Thanks.

[ranman256]

but what field?
is this string going to search in 1 field in 1 table?
or all fields or all tables?

[June7]

Review http://allenbrowne.com/ser-62.html

[requiemzz]

It should be able to search all fields within the datasheet associated with the form.

[June7]

Did you review the referenced link?

[requiemzz]

I am using the attached template. Basically, I have imported my data into the form and added some additional fields. In the "Asset" table. The data shows correctly in the "Asset List" Form but I cant use the search. When I try to use it returns nothing. from what I gather its using a filter based off some embedded Macros?

[June7]

This is a MS template. Yes, many of them do use macros. It also uses lookups in table which I never do. You probably need to change the ValueList items. Or build some lookup tables and modify the comboboxes on form then remove lookups from table.

There are no records in the uploaded db. You provided the accdt template, not your modified accdb.

[requiemzz]

well, that's just it. if I use that template as is, and enter a new field in the asset list table and to the asset list form. I cant search it the only thing that seems to be searchable is the "item" field.

[June7]

When you open the accdt it should prompt you to save as an accdb and you work with the accdb as your db. Is that what you did? Then you modified the accdb? That is the db you need to provide for analysis.

[requiemzz]

I have attached it. You will notice if you add a new field it will not be searchable in the search field.

https://imgur.com/a/iILDKom

[June7]

Yes, AssetList form only searches Item field. You already added field(s) to this db? Which one(s). Did this work differently before adding field(s)?

However, the search on ContactList does work for all fields.

Personally, I would convert all macros to VBA. I don't use macros and find debugging them frustrating.

Review https://sourcedaddy.com/ms-access/te...ing-macro.html

[requiemzz]

yes I added the "Asset Tag" field. Where is the search is defined in the assetlist form?

[requiemzz]

Look like you can convert a macro to VB right from within access. I found this snippet of code that seems useful:

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

[June7]

There are embedded macros associated with button cmdSearchGo.

I have never used TempVars nor Screen.ActiveControl. Suggest you review the Allen Browne link in post 3.

Only 1 time have I ever set up db to store user defined filter criteria into table for retrieval and reuse. Don't think it actually got used much.