?
Solved

Search textbox on a form

Posted on 2014-10-27
4
Medium Priority
?
296 Views
Last Modified: 2014-10-28
Microsoft has a template named "IssuesDesktopDatabase.accdb" and on one of the forms is what appears to be a simple search textbox.  This is what it looks like...

Search
But I'm trying to figure out what goes on beneath the surface of the textbox.  It seem that if the user enters a string of characters the function will find records that have that string of characters in any field.  

Does anyone know how this works?  If I convert the macros to VBA code it doesn't seem to work and I'd like to duplicate this on a form.

--Steve
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 85
ID: 40407230
It would help greatly if you either (a) let us know where we can review the template or (b) zip up the database and post it here.
0
 

Author Comment

by:SteveL13
ID: 40407261
Attached.  Thanks.
IssuesDesktopDatabase.zip
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 40408103
Looks like each form is based on a query, and that query includes a Field named "Searchable". That field basically concats all the values from each row into a single Field. For example, the IssuesExtended query has a Field like this:

Searchable: [Issues].[ID] & " " & [Summary] & " " & [Status] & " " & [Priority] & " " & [Category] & " " & [Project] & " " & [Keywords] & " " & [Resolution] & " " & [ResolvedVersion] & " " & [Users].[FullName] & " " & [Users].[Email] & " " & [Users_1].[FullName] & " " & [Users_1].[Email]

The box then has an embedded macro that searches THAT field for the value the user enters. To see that macro, open the form in Design view, select the Search box, and click the build button next to the AfterUPdate event in the Properties sheet. YOu'll see several steps to that macro, but the one you're looking for is the second If - End If step. In that, you'll see a SetTemp action and a SetFilter action that performs the search.

FWIW - to me, this is a poor way to handle things, since the concat process could definitely slow things down if you put too many fields in there, or as the number of rows begin to climb. You can do the same thing by creating a valid WHERE clause and assigning that to the Filter field. To me, this would be a much cleaner, and more performant, method to use to create a search in this manner.
0
 

Author Comment

by:SteveL13
ID: 40408401
Scott,

Excellent insight.  I truly appreciate it. Thank you.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question