[AccessD] Simulate AutoFilter

I found this article (http://msdn2.microsoft.com/en-us/library/aa480727.aspx)  describing how to do this in .NET 2005.  Even there it ain't simple.

Regards,

Steve Goodhall


 -------------- Original message ----------------------
From: "Robert L. Stewart" <rl_stewart at highstream.net>
> 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.
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com