filter form when criteria in subform

[cardgunner]

I need some direction, again!

I'm almost exhausted with this project yet I got a last minute request from it's owner.

From the form he wants to be able to filter it's content by choosing the criteria in the filter by form button in the toolbar.

Some of the criteria falls in the subforms I have in the form. All forms are linked by customerid.

When I/he selects put in the criteria in the subform it filters out or leaves in what you wanted in the subform, however it does not filter the main form and show only those customers who share the same customerid as the filtered info in the subform.

Before I go and create something he is not asking for is there a way to do this with in the existing manner?

Like I said I'm at the very end of this project, I want to get paid, and I should have thought of this before. I didn't think of it and he didn't ask for it but it needs to be there.

Please any help or guidance would be great.

Because of confidentialality agreements I cannot provide the db but I can give you some screenshots.

Is there a way I/he can use the filter by form button on a form with subforms where the criteria for the subform filters the records of the subform and only shows the records from the mainform where subform and mainform are the same?

 

[cardgunner]

Here's my db after I cleaned out some stuff.

 

[ajetrumpet]

I need some direction, again!

isn't that a bit quick???

 

[cardgunner]

All do respect Adam I don't get it.

 

[ajetrumpet]

Are you going to tell me what I am looking for here??? I got your file...

 

[ajetrumpet]

Some of the criteria falls in the subforms I have in the form. All forms are linked by customerid.

When I/he selects put in the criteria in the subform it filters out or leaves in what you wanted in the subform, however it does not filter the main form and show only those customers who share the same customerid as the filtered info in the subform.

can you explain this a bit more please? Is he pushing the filter icon on the toolbar for what you're explaining here?

 

[cardgunner]

yes he wants to select the filter by form and then fill in his criteria in the fields respectively.

open up db>close reports>select customer form>select filter by form in the tool bar>go to the action subform and select 12 for the action ID> select filter in the toolbar

what he wants to see is only customers where the action id 12 shows up in there action subform.

in this case it should be Test user, hitest game, and low man.

However, this is the problem, they all show up.

 

[cardgunner]

I'll try to explain this better.

This is a db I'm building for a client.

The cstfrm is the main form he works from. At the top of the form is customer info. inset into the cstfrm is 4 subforms.

a form for comments called commentsubfrm labeled action.
a form for orders called ordersubfrm
a form for status called statusfrm
a form for instrument called instfrm

He likes the idea of hitting the the filter by form button in the toolbar. Then type in the criteria in the which ever field he wants to filter. It may be customer info or info in any of the subforms.

So say he wants to filter the the customers who have a 12 for an actionid in the commentsubfrm.

He selects the filter by form icon in the toolbar

He types 12 into the actionid field in the commentsubfrm while the form is in the filter by form state.

he selects the filter icon in the toolbar

He expects to get only customer that have 12 in the commentsubfrm.

What he gets is all the customers and the subform only shows records where 12 exists.

Who do I tell the db to only show me only the customers who have a 12 record as well?

He will also want to filter customers who have a certain record in the the commentsubfrm as well as records in the ordersubfrm who also are a certain status.

I did come up with a workaround but I have some hiccups with that. And it's not what he wants.

But because I have no idea how to make the filter by form work I will have to force feed him this qworkaround. A simple db just became a pain especially with this workaround.

Is there a way that you can have the filter by form work in the waqy he wants?

If so what do I have to do?

 

[cardgunner]

I don't know how to ask any better or more clearly then I have already.

I set the startup now so it will go directly to the the form that he will filter.

Now he is going to hit he filter by form icon (it has the funnel icon in front of the form icon)

He wants to type in his criteria in the fields respectively.

then when her finishes in putting his criteria he runs the filter by the filter button in the toolbar.

However say he enters criteria in the subform it filters the subform without effecting the the information outside of the subform. He onlys wants to see the customers that fits the criteria he has selected.

It shows all the customers and only filters the subform.

Is it possible to set the form up so he can use the filter by form icon button but show only the records in the body of the form that meet the subform's criteria?

 

[cardgunner]

Well I gave up on being able to use the filter by form button that Microsoft provides in the toolbar if I have to filter the records of the mainform and subform.

So I create a workaround. Where as a button when clicked brings up a query. The user then selects the field he wants to set criteria for, drags that into a column, sets the criteria., Saves the query and closes. He hits another button on the form which updates the recordsource and shows the recordset he wants.

Is there a way to protect the layout of the query so he can not delete or change the structure of the query? Is there a way that he can add a field and take that field he added away but he cannot change the joins or mstakenly delete a table or mistakenly delete the records there already?

If not then I'm in trouble and need to come up with a another way again to filter for certain criteria. I really hate to do it but a maybe I have to make a form with 32 boxes where he selects the criteria.

How would any one out there do it?

Can you protect a query from deletions of existing information?