Filter controls

[Question Boy]

I have a lookup control in my form header. I would like to know how I can,
at the click of a button, filter all the form's controls based on the
control's value (* & me.control.value & *).

Basically, filter the records for any control with the specified value
(including subform. One master filter of sorts.

Thank you for your guidance.

QB

 

[Piet Linden]

I have a lookup control in my form header.  I would like to know how I can,
at the click of a button, filter all the form's controls based on the
control's value (* & me.control.value & *).

Basically, filter the records for any control with the specified value
(including subform.  One master filter of sorts.

Thank you for your guidance.

QB

Whether it's possible or not, such a question makes the design of your
database seriously suspect. Why would you have the same information
in multiple fields? That to me just doesn't make sense.

 

[Question Boy]

I can garantee my design is quite sound. It is not an issues of having the
same data in numerous fields but rather simplifying the search ability. I
could create multiple lookup controls, one per field, but this becomes very
very heavy. A waste of space and limits usability. By being able to simply
enter a criteria and pull up all related record I maximize usibility. It
simply make sence and I have seen it done in numerous other applications so
now I would like to emulate it as well.

So back to my original question, any ideas on how I can achieve this?

QB

 

[Allen Browne]

If you are asking how you can use a combo to choose which field to filter,
plus a text box to enter the value you want to filter on, see:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html

If you are asking how you can apply the same filter value to multiple fields
across the record at the same time, then you do have a non-normalized design
as Piet Linden said. You would not be filtering the same value across
multiple fields unless you have repeating fields, so the design is fatally
flawed.

 

[Question Boy]

I simply want the user not to have to specify which control to search
through. by doing so I eliminate 1 step every time the user need to perform
a search.

My db is normalized, there is no issue as far as that goes. This is not my
first db by far.

I am surprised at the resistance to this question though. I find it hard to
believe that I am the first to wish to create a simplified search capability
with a form and minimize user interaction. Hence a single textbox for the
criteria and a button to execute the search/filter. Then it simply returns
the records in which the criteria is found no matter where it was found.

I have seen this in numerous applications and websites. When you perform a
search on a website (for instance), you don't specify if you wish to search
the h1 tag, h2 tag, content, title,... no you simply search and it returns
thos records where the criteria was found regardless of in which field it was
found. This is obviously all control by the underlying query but never the
less the user never has to specify where to search but simply what to search
for. I wish to do the same, but in Access.

I had hoped someone could provide some guidance. As I said, I cannot be the
first to do this. I guess I will plug away at it myself and find a way.

Thank you both anyways.

QB

 

[Allen Browne]

Try FindRecord in a macro (or DoCmd.FindRecord in code.)
The 6th argument can be set to all fields.

(I personally don't find this useful as you don't know if the find succeeds
or where you've ended up.)

 

[Question Boy]

Allen,

I have been looking at your 'Find as you type' example (by the way very
nice!) and trying to see if it would work in my particular case, but have run
into one issue. I have a tab control with 3 pages, but I only get the
controls from the 1st page in the list, would you happen to have any idea as
too why?

Thank you,

QB

 

[Allen Browne]

I'm guessing that those are the controls on this form, and the other pages
have subforms?

You might find the AccessVandal's answer helpful too. You can match several
(specific) fields with that technique, and still know the results.