Difficult filter question on different forms

[badgateway]

Hi all,

I have been struggling with a filter issue. I have several forms based on several tables. The uniqueness of all tables is that they all have a ID. I use this ID to refer to other tables, like every ohter normal program I think
What I want now is that a user can do some filters on a form. Say he filters and has 10 records filtered, now of he clicks and other form the filter is not working because they don't contain the same fields of course. I know a way to solve this but than I have to work with a temp table and that's not my first option.
Anyone of you guys have ever struggled with something like this?
For Example:
In one form you have all kind of patients and you only want patients who are bigger than 1 m 75. From your selection you have to open another form not containing the height of patients but f.e. his address etc. I hope you people can follow me
thanks

 

[FancyPrairie]

I think the only way you are going to solve this is to make sure that RecordSource for all the forms reference all of the tables that could be included in the filter clause (I'm not saying use the same query for each of them. Rather take the query you have for form1 and join the other tables to it. Then take the query you have for form2 and join the other tables to it. And so on.). Then you will also need to change the "Recordset Type" property of all of the forms to "Dynaset (Inconsistent Updates)" so the user can still edit the data on the form.

 

[badgateway]

Hi FancyPrairie,

Changing the recordsource through code would be an option but if you do a form filter than in the filter area you can see something as (lookup_NameField.value = "your search value". Problem is that I always close my forms and that the other forms aren't going to find the Namefield.value because that form is closed. Your idea was also something I was considering but I hoped microsoft had added a more easy option If you look at Filemaker f.e. there you can query as much as you want and go to other forms without having the skills of a programmer. When will Microsoft learn that they also can learn form other people...
Anyway thanks for your reply!

 

[FancyPrairie]

First, I didn't mean to change the recordsource via code. The recordsource would be based on a query that joins the other tables. Second, instead of closing the form either make it invisible or once the second form determines the value of Namefield.value, then close the first form. Or store the value of Namefield.value in a global (public) variable prior to closing the first form.

 

[badgateway]

Hey again Fancy,

I have about 20 tables with lots of data. Don't you think this would slow down my application? That thing of the global variable is something I forget to think about It's a solution and probably in my eyes the only one

What I wanted to do is creating a temp table. You filter the form and than you set all the id's in that temp table, than I dont' have to work with many tables nor I have to work with global variables, bur still have to change my recordsource through query or code. What's your opinion about my suggestion? Do you think mine is a dumbass solution or is it workable?
thanks mate for your reaction

 

[FancyPrairie]

With that many tables, it would certainly slow things down as you open the form using the join method as I suggested. But the only way you're going to be able to filter like you want is to join the tables. Otherwise, if, for example, you've indicated that you want to filter based on Department ID and form2's recordsource does not contain Department ID, you're out of luck unless you've done the join.

I don't think there is a need to create a table to store the filter criteria entered on a form. Just keep the form open and set it's visible property to false. Then, if the user's want to change the filter criteria, just set the form's visible property to true (all of the information they entered previously is still there).

 

[badgateway]

I think there's no other option, so I'll give it a try Thanks for your advice FancyPrairie!
sincerely
Badgateway