[AccessD] Simulate AutoFilter

Even on their worst days, the engineers at YPG did not want
to search 1.5 mil records on a single screen.

If they want to filter based on all 40 columns, then add
combo boxes for all of them. Then build the SQL for the
subform source dynamically based on the comboboxes that
have been used to select data. It will be a lot of code-behind,
but it will never break like the Excel thing did.


At 12:41 PM 4/4/2007, you wrote:

>Thanks. My sanity is questionable at best.
>
>I would never have recommended Excel for this job. The department 
>asking for help actually used Excel for this before I got here. A 
>guy put together some VBA to import the text data into Excel, 
>splitting it into several sheets, of course. He has left, and the 
>VBA now bombs (no pun intended). Since it no longer works, and since 
>it took 25 minutes to load when it did, they've asked for my help.
>
>Limiting them in any way but their own applied filters is not an option.
>
>If I can't find a way to fake an AutoFilter, I guess I'll use a 
>zillion or so comboboxes and go from there.
>
>Thomas F. Ewald
>Stryker Mass Properties
>General Dynamics Land Systems
>
>
>
>
>
>
>Thomas,
>
>First, are you nuts? Loading 1.5 million records just to filter them.
>That is extremely poor design.
>
>And to even consider Excel for the task...well, I won't say what I
>think about that.
>
>Build a search form.  Create a subform on it that will display the
>results of the things selected as part of the search. Do not load
>the source for the subform until you click on a button to 'find' the
>results of the search criteria entered. As part of the search, give
>them a field for the top X number of records. And do not let them
>exceed a certain number.
>
>Even you military contractor types should be able to use it. :-)
>
>I can say that because I developed the FRACAS system that Stewart
>& Stevenson used in the FMTV production system for the Army. So, I
>know how backward engineers can be. I also worked at YPG as a RAM-D
>engineer. As well as reporting on some of the testing on the M1A1
>systems.
>
>Robert
>
>At 12:00 PM 4/4/2007, you wrote:
> >Date: Wed, 4 Apr 2007 10:27:16 -0400
> >From: ewaldt at gdls.com
> >Subject: [AccessD] Simulate AutoFilter
> >To: accessd at databaseadvisors.com
> >Message-ID:
> >
> ><OFA16E1530.228F9136-ON852572B3.004F0FEB-852572B3.004F6677 at is002023 
> .gdls.com>
> >
> >Content-Type: text/plain; charset="US-ASCII"
> >
> >I'd like to create a form in Access which simulates the AutoFilter
> >functionality in Excel. Basically, it's the size of the files (1.5 million
> >records) that makes Excel less useful than it would be otherwise. I doubt
> >the company wants to spring for Office 2007 just now to have Excel's
> >capacity increase.
> >
> >Does anyone know of a sample database or just some instruction on how to
> >build such a puppy? I've got about 40 fields; will this require 40  combo
> >boxes, or is there another way?
> >
> >TIA.
> >
> >Thomas F. Ewald
> >Stryker Mass Properties
> >General Dynamics Land Systems
>
>
>
>This is an e-mail from General Dynamics Land Systems. It is for the 
>intended recipient only and may contain confidential and privileged 
>information. No one else may read, print, store, copy, forward or 
>act in reliance on it or its attachments. If you are not the 
>intended recipient, please return this message to the sender and 
>delete the message and any attachments from your computer. Your 
>cooperation is appreciated.