Search one table with two list boxes!

[chassid]

To all,

I am trying to search one table using two listboxes. An example of this is as follows:

In my database the field chapter has 23 members and 14 guests. The user would click on the first combo box and select the field chapter. The second combo box would automatically update with the entries members and guests. Once you would choose members the 23 names, addresses, and all other fields would appear.

Does anyone know how will that be coded? The only way I found to do this is if I had two different tables. My problem is that I am working with a very long access database and wouldn't be able to create another table. I really need both listboxes to search the same table.

Does anyone know how to code this?

I would greatly appreciate any help!

Daniel

 

[TeaAddictedGeek]

You would need to essentially have two queries--you definitely don't need two tables!!

<%

dim People, lsSQL

asp code
lsSQL = &quot;select * from people order by lastName&quot;
set People = ...

if len(People) > 0 then
run ASP query to get answer, call query &quot;Answer&quot;
end if
%>

<select name=&quot;person&quot; size=5 MULTIPLE>
<%
do while not People.EOF
%>
<option value=&quot;<%= firstname %>&nbsp;<%= lastname %>&quot;>
<%= firstname %>&nbsp;<%= lastname %>
<%
People.MoveNext
Loop
%>
</select>

<select name=&quot;answer&quot; size=5 MULTIPLE>
<%
if len(People) > 0 then
do while not Answer.EOF
%>
<option value=&quot;<%= answerstuff %>&quot;><%= answerstuff %>
<%
Answer.MoveNExt
loop
end if
%>
</select>


All it takes, really, is a little logic. Essentially the form would post to itself and the query wouldn't be called unless it had gotten at least one value to pass through the query. Otherwise it'll return with a big fat error, and you don't want that.


Hope this helps, or at the very least, gives you ideas.... [sig]<p> <br><a href=mailto:aberman@thebiz.net>aberman@thebiz.net</a><br><a href= > </a><br>Database web programmer and developer, fueled by peach snapple and mochas.[/sig]

 

[chassid]

Kyrene,

Thanks! I worked a lot with your coding above, tried to figure out the logic you were talking about and finally made it work!!! haehahea

Thanks for the help! I am new at asp, as you can see, and still need some time to see how things can be created out there. I am in a pretty steep learning curve!

Thanx again,
Daniel [sig][/sig]