Hi,

It' s time to rewrite a form I wrote years ago. One client wants new
criteria added to the form I wrote and the form is just unmanageable
now. Too many patches to it, etc...

I need to filter out the client table depending on what the contents
of various controls are on a form, and write the results to a table
that I will use for reports, labels, display, etc...

Not all the fields on the form can be found in the client's table.
Most are from various related tables.

Most are text fields, but some are numerics and some are date ranges.
Supporting tables include reservations made, multiple types of
interests, etc.. all located in different tables.

And the results need to be sorted based upon various radio buttons
that the user picks. There are currently two button groups.

Any combination of the fields on the form and sort orders can exist.
One, two, fifteen, etc..

I was thinking about starting out by writing all client records to the
work table and deleting those that don't match the criteris the client
picked on the form. But there are a couple dozen textboxes and
comboboxes on the form that can be selected in any combination. And
the client table can have 100,000 records in it.

Any ideas/suggestions?

I'll tell you what I did with the original form but I've hard-coded it
a few times over time so it is darn hard to decipher now.

-paulw

It might be worth having a look at SysFilterByForm from a firm called
www.AccessToGo.org.UK. It costs, and is a bit fussy requiring saved queries
as RecourdSources and RowSources. I have incorporated it my Dbs, but in all
honesty, rarely use it. Phil

What I do is name each control on the form to the name of the field that it
should be applied to.
In the tag property of each control I put the field type (text, integer,
etc.)

Then loop through all of the controls on the form and for each control that
is not empty, build my where clause utilizing Application.BuildCriteria and
passing it the correct parameters based on the control name and tag
property.

Some special conditions, like a listbox or range of values need to be
handled separately, so leave the tag empty as a flag to not process that
control.

This saves a lot of code for most controls.

In some cases, I need to build a where clause, based on a separate group of
controls, to be used within an IN clause as part of the main where clause.
I place each group of controls on a separate tab and use the same code to
loop through the controls on each tab.

If you later add a field, you don't even need to touch the code.

--

AG
Email: npATadhdataDOTcom


"PW" <emailaddyinsig (AT) ifIremember (DOT) com> wrote

On Sun, 12 Jun 2011 09:39:28 -0400, "agiamb"
<NOSPAMagiamb (AT) newsgroup (DOT) nospam> wrote:

Clever!

Okay, that is what I have been doing.

Very good.

I need to digest that for a while. It's not computing yet.

That's what I want! I can't stand hard-coding things if I can get
around it. I just feel dirty after I do. :-)

Thanks!

-paulw

IN clauses come in handy for utilizing criteria that is not in the
recordsource that you are trying to filter.
For instance, if you want to filter the 'one' table in a one to many
relationship by values in the 'many' table.
Select * from OneTable WHERE RelationField IN (Select RelationField from
ManyTable where SomeOtherField = SomeValue).

However, you need to be careful using them. Often performance is fine, but
sometimes it is very poor. So, be sure to test.
--

AG
Email: npATadhdataDOTcom


"PW" <emailaddyinsig (AT) ifIremember (DOT) com> wrote

On Fri, 10 Jun 2011 14:17:20 -0600, PW
<emailaddyinsig (AT) ifIremember (DOT) com> wrote:


Hmmm... the usual critics didn't chime in on my post. I thought I
left myself open ;

-paulw