Monday, July 12, 2010

Access Search Tool - Chapter 1 - ListView

   Searching for data in an Access database is a drudgingly common problem.  I found myself writing the same old search utilities for different systems month after month when I hit rock bottom.
   "No!" I cried, "No!" I cried, "No!" what I should do is , "Avoid repetition, avoid repetition, avoid repetition!"    And then it came to me:  Rather than reinvent a search tool for every project, what I wanted was a single solution.  I wanted to be able to throw SQL at it so it was fast and easy to use.  I wanted it in a library database so I could set a reference and be done.  I wanted to be able to type in text and watch the list of matching records get shorter and shorter and shorter.  Was I dreaming?
   No!
   But at the core of this set of criteria is a list that can be very rapidly rebuilt for every keystroke.  In the coming weeks I'll show you a class I use to accomplish exactly this.  The class is contained in a library database as a wrapper around an Access.Form that contains an MSComCtlLib.ListView.  You can throw SQL at it and it works.  You can sort on any field by clicking the headers.  It raises an event for every line added to the list so consumers can customize formats and text colours and so on and so on.
   And what about the search tool?  Once the list is working the search tool will be simple.
   More later,