Multiple Searches

[saranmc]

As a part of the database I am developing I ran into a tricky Situation. Would be great if anyone could Show me the right way to proceed.
It is regarding a search function.

I will try my best to give a good Explanation.
so I have:
3 tables completely different except one common field [pnr]
1 form.

in this form I have 2 Combo boxes and one TextBox.
The first Combo box (COMBO1) has the 3 table names,
the second Combo box (COMBO2) Shows the field names of the selected table in COMBO1
and the TextBox (TEXT1) allows the user to enter a value.
on clicking search the value has to be searched and deposited in a new table (TABLESEARCH) for the user to view.
all the values are searched, I use (*).
Till here I am able to do it. No Problems.

The Twist in the Problem starts here.
My users would like to have a Kind of multiple searches.
that means, I put in 3 more sets of (COMBO1+COMBO2+TEXT1).
each time one set is filled there is a command button and a new set will appear if the user wants.

now the search has to work like this.
the user can enter the first set of Parameters into (COMBO1 +COMBO2 +TEXT1).
it searches and Shows in TABLESEARCH.
now when a new set of Parameters are entered, the second search has to take place in the result of the first search.
this is what makes it difficult for me.

and to make it even more difficult, as i mentioned at the start I have three tables,
lets say, the user selects a new table (TABLENEW) for the second search then the search result has to get added into the first search result table (TABLESEARCH).
so the resulting [pnr] from the first search (TABLESEARCH) has to be searched in the new table (TABLENEW) and the results added to the first search result table (TABLESEARCH).
that means new fields from TABLENEW have to be added into the first search result table (TABLESEARCH).

I hope i was not too confusing. I have no idea how to do this. I am not even sure if it can be done.
Can someone please tell me my Options or maybe some modifications can be also made.

Thanks as always..

[zmbd]

Let's see if we understand this... it sounds like you are building a cascading filter along the same lines as one sees on the shopping websites
All Products>Clothing>Men's>Shoes>Company>Size13US>Blu e>Running>Minimalist

However, you have the three tables... just a little twist...

but lets verify that my logic is matching yours

+ You have TableA, TableB, and TableC

+ You have a common field [PNR] such that:
[TableA]![PNR]==[TableB]![PNR]==[TableC]![PNR]

(?) How are you ensuring that this relationship holds true?

+ If a user selects from CBO1_Tables, CBO1_Fields has the fields that are in the design of the table selected.

++ You have a TextBox1 that the user enters the search criteria.

+++ using these three controls you can create the SQL-WHERE clause such (this isn't the properly formatted string just symbolic)
WHERE ([CBO1_Tables]![CBO1_Fields]=[TextBox1]);

IE user slects:
CBO1_table=[TableA]; CBO1_Field=[Filed1]; TextBox1="Value"
[iCODEWHERE ([TableA]![Fields1="Value");[/icode]
Such that you get records so that:
record set = [TableA][PNY]={1,2,3,4}

++ You are appending the resulting query results to another table (?)

You might want to consider creating a secondary database "on the fly" at the user's pc for this to avoid bloating your main user interface or the back-end file(s)

+ The subsequent searches are to be restricted to where only the records in the prior search with the same [PNY] in all the tables are available for the subsequent searches:

Using the example return above the second search is based on:

Record set =
([TableA]![PNY]= 1,2,3,4)
   ===([TableB]![PNY]= 1,2,3,4)
      ===([TableC]![PNY]= 1,2,3,4

If the user's selections are that you get something like:

Record set =
([TableA]![PNY]= 1,2)
   ===([TableB]![PNY]= 1,2)
      ===([TableC]![PNY]= 1,2

for the return record you would base a third set of search criteria on the above records etc...

(?) You say that you want to append the results from each sub-record-set into the holding table - how do you handle situations where the user reselects the same table, field, and value as in the first search?

so is this correct?