Dynamic Drop Down List!

Ok,

I have a form I'm building in Dreamweaver. On my form I have a drop down list that is populated by a table in my Access Database. I won't to add a second drop down list but have the contents of it vary by what I select in my first drop down list. Let me explain further. Lets say I have a database of Movies. And The name of the movies are in a table and that table is used to populate my first drop down list. Now I have another table that has the actors in the movies. I have a junction table to make them relate in my database.....now how can I select a movie in the first drop down list...and the actors from that movie popluate the second drop down list???

show the first drop down list as you do.

Make the onChange event of the drop down list submit a form, with the value of the list.

Have another recordset which takes that variable and calls for the second list.

Just be sure to hide the second list when the second recordset is "EOF"

If you need an example to look at I can provide one.

I would love to see your example.

Thanks

OK, here is a quick sample of what I do, First, the two recordsets:

<%
var rsUniParts = Server.CreateObject("ADODB.Recordset");
rsUniParts.ActiveConnection = MM_connCharms_STRINGSQL;
rsUniParts.Source = "SELECT UniformItem FROM UniformParts WHERE schCode = '"+ scode + "' ORDER BY UniformItem ASC";
rsUniParts.CursorType = 0;
rsUniParts.CursorLocation = 2;
rsUniParts.LockType = 3;
rsUniParts.Open();
%>

this one brings back a list of categories for that user (scode).

<%
var rsUniitems = Server.CreateObject("ADODB.Recordset");
rsUniitems.ActiveConnection = MM_connCharms_STRINGSQL;
rsUniitems.Source = "SELECT itemno, size, condition,rrn FROM UniformDetail WHERE SchCode = '"+ scode + "' and UniformItem = '" + unitype + "' and (assignedto = 'x' or assignedto is null) ORDER BY itemno ASC";
rsUniitems.CursorType = 0;
rsUniitems.CursorLocation = 2;
rsUniitems.LockType = 3;
rsUniitems.Open();

%>

this one brings back a list of items in that category which haven't been assigned. The red code above takes the unitype variable from the first drop down list.


Now the HTML:

Here is the first drop down box:

<select name="unicat" onChange="getItems(this);" class="inputbox">
<option value="">Select Part</option>
<%while (!rsUniParts.EOF){%>
<option value="<%=(rsUniParts.Fields.Item("UniformItem").Value)%>" ><%=(rsUniParts.Fields.Item("UniformItem").Value)%></option>
<%rsUniParts.MoveNext();} %>
</select>

The second drop down box looks like this:

<% if (!rsUniitems.EOF ) { %>
<select name="recordnumber" class="inputbox" onChange="document.partform.submit(); ">
<option selected>Select Item Number</option>
<%while (!rsUniitems.EOF){%>
<option value="<%=(rsUniitems.Fields.Item("rrn").Value)%>"><%=(rsUniitems.Fields.Item("itemno").Value)%> - size <%=(rsUniitems.Fields.Item("size").Value)%></option>
<%rsUniitems.MoveNext();} %>
</select>

Of course there is more HTML around it, tables, etc. But this should get you going in the right direction. I don't actually submit a form on the first drop down, but call a javascript function which sends that value to the QueryString. If you don't want to use javascript, you could just as easily submit a form.

If you want to see a working example, I'll have to refer you to a URL which I'm not sure we can post here.