Populate dropdown conditionally

[pjm]

Hi,

I have two access tables acting like a product catalog (one table for the categories and other for the products) Something like this:

Table1:

CatID, CatName
============
1, Cat1
2, Cat2
3, Cat3

Table2:

ProdID, CatID2, ProdCat
==================
1, 2, 1
2, 3
3, 3

In a form (to add a new product to Table2) I need to populate a dropdown with the CatName. So far so good. Now, I will going to have special categories with just one product. When that happens the ProdCat value is '1' (that means that the category is not going to have any more products). So when populating the dropdown with CatName in those cases where ProdCat is equal to '1' I don't want the corresponding category being showed in the dropdown. How to do this? I have the following code but I know it's not correct and maybe I'm completely out of track:


<select name="CatID2">

<% While (NOT rsCat.EOF)

While (NOT rsProd.EOF)
if rsProd("ProdCat") = 1 then %>

<option value="<%=(rsCat("CatID"))%>"><%=(rsCat("CatName"))%></option>
<% else
end if
rsProd.MoveNext()
Wend

rsCat.MoveNext()
Wend %>
</select>

[phpnovice]

...when populating the dropdown with CatName in those cases where ProdCat is equal to '1' I don't want the corresponding category being showed in the dropdown. How to do this?
To not display it in the second dropdown, change this part:

if rsProd("ProdCat") = 1 then

to this:

if rsProd("ProdCat") <> 1 then

[pjm]

Sorry, that was my mistake. But it still doesn't work. By the way, it's just one dropdown, not two. Some how I need to filter the showing categories, not displaying those where ProdCat in Table2 is equal to 1.

[phpnovice]

If there is only one dropdown, then why do you have a loop within a loop arranged?

[pjm]

Well, it is what makes more sense to me, not counting with the fact that is not working at all :) My idea was to use the 'internal' loop to find on Table2 all instances of ProdCat <> 1 and then based on the results to loop the content of Table1 and only use those CatName whose ID was not found on Table2. Any tips?

[phpnovice]

Seems you could actually make this elimination in your SQL statement.

[pjm]

Thanks for the tip phpnovice. The answer to the problem which was given to me in another forum is to populate a dropdown box using the following SQL statement:

SELECT * from table1 where catID NOT IN(SELECT catid2 from table2 WHERE prodcat = 1);

[phpnovice]

Thanks for the tip phpnovice.
Cheers.

[bokeh]

Mr PHPNOVICE, why don't you post in the PHP forum any more?

[phpnovice]

Because I found I didn't know enough to be able to answer questions before somebody else answered them already. ;) By the time I looked up the answers in my PHP reference manual and tried them out for myself, somebody else had already posted the answer I was researching. It did help to increase my knowledge though. :D

Why do you ask?

[bokeh]

Your answers were interesting and you always seemed determined to get to the root of things even when most of the others had thrown in the towel.

[phpnovice]

I couldn't have made a 26-year career out of programming if I were the type to give up easily.
Thus, yes, it is true that I don't like to give up -- I prefer solutions. ;)
Thanks for the encouragement.