DropDown List Based on Selection from Another

Can someone provide code for the best way to populate a drop down list from a SQL databas e based on what the user selects from a dropdown list right above it?
 
onChange in one list should populate the list below it depending on the first lists selection.
 
IE
IF BOX 1 = PREP Selected then BOX 2's list should contain X, Y, Z.
IF BOX 1 = SCAN Selected then BOX 2's list should contain A, B, C. 
 
Dynamically.  
 
Any suggestions will be greatly appreciated. 

Please post the code you are currently using.

I have tried to code several versions.  Not sure how to code it.  I am grasping at straws.
 
Here is the code:
Dim mFUNCAREA Dim mSUBFUNC Dim mELEAD
if Request.form("drpFUNCAREA") <> "" and Request.form("drpFUNCAREA") <> "0" then mFUNCAREA = trim(Request.form("drpFUNCAREA")) else mFUNCAREA = "0" end if if Request.form("drpSUBFUNC") <> "" and Request.form("drpSUBFUNC") <> "0" then mSUBFUNC = trim(Request.form("drpSUBFUNC")) else mSUBFUNC = "0" end if if Request.form("drpELEAD") <> "" and Request.form("drpELEAD") <> "0" then mELEAD = trim(Request.form("drpELEAD")) else mELEAD = "0" end if
'***********************************
' Retrieve Pick List values
'*********************************** '****************************
 ' This Form '****************************
Dim mSOURCE Dim mRECCOUNT Dim cn Dim cn2 Dim rsFUNCAREA Dim meFUNCAREA Dim rsSUBFUNC Dim meSUBFUNC Dim rsELEAD Dim meELEAD Dim meELEADNAME Dim rsRESULTSET
set cn = server.createobject("ADODB.connection") set cn2 = server.createobject("ADODB.connection") set rsFUNCAREA = server.CreateObject("ADODB.recordset") set rsSUBFUNC = server.CreateObject("ADODB.recordset") set rsELEAD = server.CreateObject("ADODB.recordset") set rsRESULTSET = server.CreateObject("ADODB.recordset") cn.open strCONN_COMMON cn2.open strCONN_Data mSOURCE = "select * from fncOptions('Functional Area', '" & meFUNCAREA & "', '') order by Option_Description" rsFUNCAREA.Open mSOURCE, cn2 mSOURCE = "select * from fncOptions('Sub Function', '" & meSUBFUNC & "', '') order by Option_Description" rsSUBFUNC.Open mSOURCE, cn2 mSOURCE = "select * from fncProjLead() order by Last_Name, First_Name" rsELEAD.Open mSOURCE, cn2 '*************** ' Initialize '*************** meFUNCAREA = "0" mesubfunc = "0" meELEAD = "0" meELEADNAME = ""
 
 
if Request.form("drpeFUNCAREA") <> "" and Request.form("drpeFUNCAREA") <> "0" then meFUNCAREA = trim(Request.form("drpeFUNCAREA")) end if if Request.form("drpeSUBFUNC") <> "" and Request.form("drpeSUBFUNC") <> "0" then mesubfunc = trim(Request.form("drpeSUBFUNC")) end if if Request.form("veELEAD") <> "" and Request.form("veELEAD") <> "0" then meELEAD = trim(Request.form("veELEAD")) meELEADNAME = trim(Request.form("veELEADNAME")) end if
 
THIS DOESN'T WORK...
Sub drpeFUNCAREA_onChange If IsNull(Document.frmTIMETRACKING.drpeFUNCAREA.Value) Then Exit Sub End If If Document.frmTIMETRACKING.drpeFUNCAREA.Value = "Misc." Then mSOURCE = "select * from fncOptions('Sub Func Misc.', '" & meSUBFUNC & "', '') order by Option_Description" rsSUBFUNC.Open mSOURCE, cn2 end if End Sub
 
.................................
 
Here is the html:
 
When this field changes (drpeFUNCAREA) then I want the list in the field below it (drpeSUBFUNC) to change....
 

<select id=drpeFUNCAREA style="WIDTH: 200px" name=drpeFUNCAREA >

if not (rsFUNCAREA.BOF and rsFUNCAREA.EOF) then
rsFUNCAREA.MoveFirst
if cint(mFUNCAREA) = 0 then
Response.Write ("<OPTION value =0 selected ></OPTION>")
else
Response.Write ("<OPTION value =0></OPTION>")
end if
while not rsFUNCAREA.EOF
if rsFUNCAREA("Option_Num") = cint(meFUNCAREA) then
Response.Write ("<Option value='" & trim(rsFUNCAREA("Option_Num")) & _
"' selected>" & rsFUNCAREA("Option_Description") & "</option>" )
else
Response.Write ("<Option value='" & trim(rsFUNCAREA("Option_Num")) & _
"' >" & rsFUNCAREA("Option_Description") & "</option>" )
end if
rsFUNCAREA.MoveNext
wend
else
Response.Write ("<OPTION value =0 selected ></OPTION>")
end if
</SELECT>
 
 

<SELECT id=drpeSUBFUNC style="WIDTH: 200px" name=drpeSUBFUNC >

if not (rsSUBFUNC.BOF and rssubfunc.EOF) then
rssubfunc.MoveFirst
if cint(msubfunc) = 0 then
Response.Write ("<OPTION value =0 selected ></OPTION>")
else
Response.Write ("<OPTION value =0></OPTION>")
end if
while not rssubfunc.EOF
if rssubfunc("Option_Num") = cint(mesubfunc) then
Response.Write ("<Option value='" & trim(rssubfunc("Option_Num")) & _
"' selected>" & rssubfunc("Option_Description") & "</option>" )
else
Response.Write ("<Option value='" & trim(rssubfunc("Option_Num")) & _
"' >" & rssubfunc("Option_Description") & "</option>" )
end if
rssubfunc.MoveNext
wend
else
Response.Write ("<OPTION value =0 selected ></OPTION>")
end if

</SELECT>