Solved General search box for a table (1 Viewer)

Sarameier

Sarameier

New member
Local time
Today, 19:05
Joined
Sep 3, 2021
Messages
26
Hi all,

I have been trying to find out how this great search box which MS has in its templates works.
Did not manage to understand its logic.

Does anybody knows how to implement this?


1631301816347.png
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 10:05
Joined
Oct 29, 2018
Messages
21,474
Hi. That box is actually a group of one textbox, two buttons, two images for the buttons, and a rectangle as the background. There is a macro attached to the buttons for clearing the box and performing the actual search.
 
Sarameier

Sarameier

New member
Local time
Today, 19:05
Joined
Sep 3, 2021
Messages
26
I guess, this link could be a simple solution. right?
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 10:05
Joined
Oct 29, 2018
Messages
21,474
Sarameier said:
I guess, this link could be a simple solution. right?
Click to expand...
That's certainly one way of doing it, but it all depends on the actual effect you want to achieve. I also prefer to use VBA over macros.

That solution you found will limit the records contained in your form, so it will end up not having all the records you may want to review.

So, maybe if you tell us what your "search" button is supposed to do, we can tell you how to create something to do it. One popular solution is to use the Bookmark property.
 
Sarameier

Sarameier

New member
Local time
Today, 19:05
Joined
Sep 3, 2021
Messages
26
So, maybe if you tell us what your "search" button is supposed to do
Click to expand...
The search button should perform a search in which all fields (of one table A) are included.
1. After a string is entered in the search box,
2. and the search function is executed,
3. all records of the table A which include the string entered in the search box before should be displayed

I think that is pretty much the same what the search function in the MS template above does
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 10:05
Joined
Oct 29, 2018
Messages
21,474
Sarameier said:
The search button should perform a search in which all fields (of one table A) are included.
1. After a string is entered in the search box,
2. and the search function is executed,
3. all records of the table A which include the string entered in the search box before should be displayed

I think that is pretty much the same what the search function in the MS template above does
Click to expand...
Okay, let's clarify a little bit the intent here. Are you using a single view or a continuous form?

In the solution you found, the result will limit the records available to the form with the matching records from the search box. If you were using a single view form, you will have to go to the next record to see the next match. If you're using a continuous form, then only matching records will be displayed all at once (or a page at a time). In this case, the bookmark property will not apply, because you want to eliminate non-matching records.

Now, as I said, that solution will eliminate non-matching records from being included in the form. If you want to reset the form to include all records again, you would have to also reset the record source that was modified by that code solution.

The approach used by the template you referred to is different in that it applies a Filter to the recordset of the form, rather than modifying it's record source, to limit the data to display. However, all original records are still available to the form. If you want to view all the records again, you simply remove the filter. Incidentally, this is the common approach when implementing search features on a bound form.
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 18:05
Joined
Jul 9, 2003
Messages
16,282
I have a product which maybe of Interest. It would mean adding your contacts to a subform in the Nifty Search Form. There are several videos showing you how to do this. If you got stuck I would be able to help.

www.niftyaccess.com

Nifty Search Form - Nifty Access

Nifty Search Form This Advanced but simple to use Search Form is unbelievably easy to set-up. Just create a query and a subform. Following the comprehensive Video Instructions, which demonstrate how to add this incredible utility to your database. Establish yourself as the "Go To" person in your...
www.niftyaccess.com www.niftyaccess.com

I can provide you with a free copy, just contact me in a private message and I will explain how you can get a free copy.
 
Sarameier

Sarameier

New member
Local time
Today, 19:05
Joined
Sep 3, 2021
Messages
26
Hi all...:coffee:

The approach used by the template you referred to is different in that it applies a Filter to the recordset of the form, rather than modifying it's record source, to limit the data to display. However, all original records are still available to the form. If you want to view all the records again, you simply remove the filter. Incidentally, this is the common approach when implementing search features on a bound form.
Click to expand...
@ DBguy: I am planning to use a (continuous) datasheet (table) view (i.e. no single or continuous form)

Yes. I actually would need such a "filter function" as in the MS template, that is:
- only filter applying to record sets
- no modification of records
- having the option to view all records again by simply removing the filter

If VBA is preferred over macros, I will be happy to try this out with VBA (although I have never done this before)

@ Uncle Gizmo: Thanks for your offer with Nifty Access. I am checking this and trying to understand.

Just for my understanding: Using a subform although there is only one source table used in my case. Would this method still be applicable?
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:05
Joined
May 7, 2009
Messages
19,245
you can use Split form instead of datasheet.
 

Attachments

  • CountriesDb(combobox filter-as-you-type).accdb
    976 KB · Views: 268
Sarameier

Sarameier

New member
Local time
Today, 19:05
Joined
Sep 3, 2021
Messages
26
and a follow up to Gizmo and all others: I am happy to pay you for your product and support. Since I am still not (yet) able to contribute to this forum by posting technical comments, it should be just fine for me to contribute moneywise. no free lunch intended on my part :cool:
 
Last edited:
Sarameier

Sarameier

New member
Local time
Today, 19:05
Joined
Sep 3, 2021
Messages
26
theDBguy said:
Hi. That box is actually a group of one textbox, two buttons, two images for the buttons, and a rectangle as the background. There is a macro attached to the buttons for clearing the box and performing the actual search.
Click to expand...
...I now found it to, converted it to VBA. It is the following:
Code: 
'------------------------------------------------------------
' 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 "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
 
Sarameier

Sarameier

New member
Local time
Today, 19:05
Joined
Sep 3, 2021
Messages
26
Hi all,

updating you on this.

I have just tried it: Using the macro above, and just amending the field/table names works pretty well.

So thanks again!!

I think we can mark this thread as "solved".
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 10:05
Joined
Oct 29, 2018
Messages
21,474
Sarameier said:
Hi all,

updating you on this.

I have just tried it: Using the macro above, and just amending the field/table names works pretty well.

So thanks again!!

I think we can mark this thread as "solved".
Click to expand...
Hi. Glad to hear you got it sorted out. If you would like us to mark the thread as solved for you, we can do that as well.
 
Sarameier

Sarameier

New member
Local time
Today, 19:05
Joined
Sep 3, 2021
Messages
26
hello again...

I got one last little question for you on the source code above.

Shall I open another thread or reactivate this one?
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 10:05
Joined
Oct 29, 2018
Messages
21,474
Sarameier said:
hello again...

I got one last little question for you on the source code above.

Shall I open another thread or reactivate this one?
Click to expand...
Is it related? If so, fire away. We can always move it if necessary.
 
Sarameier

Sarameier

New member
Local time
Today, 19:05
Joined
Sep 3, 2021
Messages
26
ok..

As said, this code works

Code: 
'------------------------------------------------------------
' 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 "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

Except for the fields which are related ("lookup fields"). These will apparently not be considered by the search function although
- I added them to my form
- I mentioned them in the source code.

For example,

Code: 
TempVars.Add "strFilter", TempVars!strFilter & " OR ([Company] Like "" * " & [TempVars]![strSearch] & " * "" )"

The source for attribute company is another table. It would not be shown after a search command although it is displayed initially in the (search) form.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 18:05
Joined
Sep 21, 2011
Messages
14,309
Sarameier said:
ok..

As said, this code works

Code: 
'------------------------------------------------------------
' 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 "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

Except for the fields which are related ("lookup fields"). These will apparently not be considered by the search function although
- I added them to my form
- I mentioned them in the source code.

For example,

Code: 
TempVars.Add "strFilter", TempVars!strFilter & " OR ([Company] Like "" * " & [TempVars]![strSearch] & " * "" )"

The source for attribute company is another table. It would not be shown after a search command although it is displayed initially in the (search) form.
Click to expand...
Table lookup fields hide the real value in the field? :-(
So whilst you might see xxxx, actually the value is 2 or whatever.:(

Unless you understand this structure, best to get rid of them, show exactly what is in the field and lookup the data yourself with a join.

Very common mistake made with people new to Access. :(

I only have one DB with lookups, as I knew no better at the time, but equally knew what the lookup was doing.
I have left that DB as is, merely because there is no point converting it just for the conversion sake. However, I would not do it again. :)
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:05
Joined
May 21, 2018
Messages
8,529
The source for attribute company is another table. It would not be shown after a search command although it is displayed initially in the (search) form.
Click to expand...
As @Gasman mentioned that is not a problem with your code but a problem with the underlying query. Another reason you should NEVER use lookups in tables.

Likely in this table "Company" is a Company ID and your are storing a value like 1,2,3. Even though your lookup might show "Microsoft" that is only a display and does not exist in the underlying data. So when you type Microsoft it cannot match it to the value 1.
In your underlying query you will need to pull in the Company table as well. Then you pull in the real "Company Name" field from the Company table.
 
You must log in or register to reply here.

Similar threads

Gasman
Add templates to QA toolbar or ribbon in 2019
Replies
0
Views
192
Gasman
Gasman
M
Solved Multi combo box and textbox search
Replies
14
Views
851
mounty76
M
GPGeorge
Implement a Global Error Handler to build a rock-solid application.
Replies
6
Views
824
Josef P.
J
M
Solved Search and find records on a continuous subform
2
Replies
33
Views
1,220
Gasman
Gasman
T
Newbie alert - TheUncannyScot
Replies
7
Views
260
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

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