One Pick List Based off of Another

[Stang02GT]

Hey guys I'm having some trouble's with the following code.

Here is what I am trying to do. I have two pick list options (drop downs) and the first one pulls from a table with department names in it, these department names are also found in the PhoneBook table which the second pick list is TRYING to pull from.

Here is my issue, the first option works fine the departments display no problem. How can I change the following code to allow the Requestor field to be updated based on what the user selects in the Department field?

Basically if you pick Finance in the department field then the Requestor field should then update to show only people who work in the Finance department.

<tr>
<td valign="center">Department</td>
<td>
<select name="REQUESTOR_DEPT" onChange="">
<option value="">Choose One
<%sql = "SELECT APP_LIST_VALUE_DESC from  TICKER_APP_LIST_VALUE WHERE APP_LIST_ID = 5 ORDER BY APP_LIST_VALUE ASC "                
set rs = server.CreateObject("adodb.recordset")
rs.Open sql, conn
while not rs.eof 
Response.Write("<option value='"&rs(0)&"'")
if cstr(REQUESTOR_DEPT)=cstr(rs(0)) then
Response.Write(" selected ")
end if
Response.Write(">"&rs(0))
rs.movenext
wend
rs.close
set rs = nothing
%>  
</select></td>
<td></td>
<td></td>
</tr>
<tr>
<td valign="center">Requestor</td>
<td><select name="PROF_ANAYLST_ID" onChange="">
<option value="">Choose One
<%sql = "SELECT LNAME+' '+FNAME FROM dbo_phonebook WHERE Department LIKE ' " & REQUESTOR_DEPT  " '"
set rs = server.CreateObject("adodb.recordset")
rs.Open sql, conn2
while not rs.eof 
Response.Write("<option value='"&rs(0)&"'")
if cstr(PROF_ANAYLST_ID)=cstr(rs(0)) then
Response.Write(" selected ")
end if
Response.Write(">"&rs(0))
rs.movenext
wend
rs.close
set rs = nothing
%>  
</select></td>
</tr>

[jhardman]

I'm afraid asp is not set up to do this, remember ASP runs on the server and the code is dead and gone by the time the user even sees the form.

You will have to use some combination of javascript or ajax along with the ASP code to change the second select on the fly.

Jared

[Stang02GT]

Ok so maybe some onclick javascript event that will update the second picklist value. Or something along those lines.


Thanks for the advice!

[jhardman]

exactly. I've seen several solutions for this type of problem, but they all depend on Javascript or Ajax. In the minimum you could submit the form and use asp to re-build the form, but notice it still uses javascript to submit the form when the first select changes, and I think that approach is a little less-favored. I think it's a pretty common javascript problem, you should be able to google and get a hundred solutions.

Jared