How to set up a cascading combo box?

[BFP]

Hello.<br><br>I had posted a similar question, but I am still stuck, so I will make my question more exact:<br><br>I have a form called &quot;Contacts&quot; and in this form is a subform called &quot;Problems and Solutions subform.&quot;&nbsp;&nbsp;There is a combo box in my sub-form called &quot;Problem Category&quot; which is based on a table full of problem categories.&nbsp;&nbsp;Now, I have a second combo box called &quot;Sub-category&quot; which should display values from one of many tables based based on the problem category chosen.<br><br>Since I have 18 categories, I have 18 associated tables.&nbsp;&nbsp;I have had some suggestions to try a query, but I have not had success with this.&nbsp;&nbsp;About all I could do was get it to show the same table no matter what category was selected.&nbsp;&nbsp;I tried to put all the sub-category tables in the query and use the build button for each table to no avail (I get no selections in this case).&nbsp;&nbsp;It would seem as if the query is ANDing when it should be ORing.&nbsp;&nbsp;<br><br>Right now, there are no relationships except the sub-form's table has a value for the problem categories and sub-category which are look-up tables to their respective tables.&nbsp;&nbsp;The sub-categories table is blank.<br><br>Aargh, this would be a lot easier to explain if I could send some screen captures.&nbsp;&nbsp;So, if someone is willing to work with me over email, I could provide them.&nbsp;&nbsp;Otherwise, I hope this has made some sense.<br><br>Thanks and Regards,<br><br>--Dan

 

[gol4]

not seeing the structure of your database this is only a guess but!<br>the problem is you should not have 18 associated tables.you should have something like this<br><br>problemcatagory table<br>probid&nbsp;&nbsp;&nbsp;&nbsp;problemcatagory<br>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;failed<br>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;no start<br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;no part<br><br>subcatagory table<br>&nbsp;subcatid&nbsp;&nbsp;&nbsp;probid&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;value?<br>&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;unknown reason<br>&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;text here<br>&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;loose conection<br>&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;batterydead<br>&nbsp;&nbsp;4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;failed to order<br><br>1 table that you then reference back to the problem ID not 18<br>

 

[DougP]

You need to look at your 18 tables and some how make them into one. I know this sounds like a mess but...<br>Are all 18 tables having the same fields?<br>Like &quot;ProblemId&quot; &quot;Problem Description&quot; etc<br>If so then add a new field to each table called &quot;Problem category&quot;. What goes in this new field is the same name as the table it came from. Say the first problem table is called Problems1.&nbsp;&nbsp;All of the records in this table will have their new &quot;Problem category&quot; field = Problems1<br>Then add the new column to all the rest of your tables and do the same for each of them Problem table 2 will have its new &quot;Problem category&quot; field contain it's name say Problems2<br>To add the words to the new fields quickly make an update query. Simply add all of the previous field and in the new &quot;Problem category&quot; put in &quot;Problems1&quot; <br>This will quickly add Problems1 to each record in a matter of seconds.<br><br>Now after you have all of your tables with a new fields and new data. then make an append query that adds each table one at a time to a new table which has all of the original fields and the new &quot;Problem category&quot; too.<br>So when you are all done you will have a new table with all 18 tables data in it but each individual tables data is unique by its &quot;Problem category&quot;.<br>Then you can group sort or search on that your regular info plus that new &quot;Problem category&quot; field.<br><br>OK<br>I know this sounds like a lot of work but it will insure less problems later. <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.