asked on

Combo Boxes working together.

I have a drop down box that pulls from a table a group of suppliers. What I am trying to do is once the supplier is chosen it then lists in a separate drop down box lists out all the projects for that supplier.

Here is what I have so far :
Combo box # 1 :
Major Customer :
<select name="sel_Major" onchange="fillInfo(document.form1.sel_Major.value);" class="cls10px">
      <option selected></option>
            <%
            strSQL = " SELECT CustomerID, Customer_Name"
            strSQL = strSQL & " FROM Customer_Index"
      strSQL = strSQL & " ORDER BY Customer_Name"

            Set objRS = objConn.Execute(strSQL)
            While Not objRS.EOF
            %>
      <option value="<%=objRS("CustomerID")%>" class="cls10px" <%If objRS("CustomerID") = strCustomerID Then%>selected<%End If%>><%=objRS("Customer_Name")%></option>

            <%
            objRS.MoveNext
            Wend
            objRS.Close
            Set objRS = Nothing
            %>
</select>

Combo Box #2 :
Project Number:
<select name="sel_project_num" onchange="fillInfo2(document.form1.sel_project_num.value);" class="cls10px">
            <option selected></option>
                  <%
                  strSQL = "SELECT Customer_Index.CustomerID, Customer_Index.Customer_Name, Project_Index.ProjectID, Project_Index.CustomerID AS CustID, Project_Index.Project_Number, Project_Index.Description "
                  strSQL = strSQL & " FROM Customer_Index INNER JOIN "
            strSQL = strSQL & " Project_Index ON Customer_Index.CustomerID ='"&strID&"' AND Customer_Index.CustomerID = '"&strCustomerID&"'"
                  strSQL = strSQL & "ORDER BY Customer_Index.Customer_Name"
                  Set objRS2 = objConn.Execute(strSQL)
                  While Not objRS2.EOF
                  %>
<option value="<%=objRS2("Project_Number")%>" class="cls10px"<%If objRS2("Project_Number") = strProjectNumber Then%>selected<%End If%>><%=objRS2("Project_Number")%></option>
                  <%
                  objRS2.MoveNext
                  Wend
                  objRS2.Close
                  Set objRS2 = Nothing
                  %>
</select>

How do I get them to work together? Any advice would be great. Thanks in advance.

Gwen

Rajmahesh

hi
r u looking for something like this

<script language=javascript>
function fillInfo(val)
{
      location.href="samepage.asp?customer=" + val;
}
</script>

<body>
            <td class=tdr width=28%>Major Customer:
                  <select name="sel_Major" onchange="fillInfo(document.form1.sel_Major.value);" class="cls10px">
                   <option selected></option>
                   <%
                   strSQL = " SELECT CustomerID, Customer_Name"
                   strSQL = strSQL & " FROM Customer_Index"
                   strSQL = strSQL & " ORDER BY Customer_Name"

                   Set objRS = objConn.Execute(strSQL)
                   While Not objRS.EOF
                   %>
                   <option value=<%=objRS("CustomerID")%> class="cls10px" <%If objRS("CustomerID") = trim(Request.Querystring("customer")) Then%>selected<%End If%>><%=objRS("Customer_Name")%></option>

                   <%
                   objRS.MoveNext
                   Wend
                   objRS.Close
                   Set objRS = Nothing
                   %>
                  </select>
            </td>

            <td class=tdr width=27%>Project Number:
                  <select name="sel_project_num" class="cls10px">
                   <option selected></option>
                   <%
                   strSQL = "SELECT Customer_Index.CustomerID, Customer_Index.Customer_Name, Project_Index.ProjectID, Project_Index.CustomerID AS CustID, Project_Index.Project_Number, Project_Index.Description "
                   strSQL = strSQL & " FROM Customer_Index INNER JOIN "
                   strSQL = strSQL & " Project_Index ON Customer_Index.CustomerID ='"&Project_Index.CustomerID&"' where Customer_Index.CustomerID = '"&Request.Querystring("customer")&"'"
                   strSQL = strSQL & "ORDER BY Customer_Index.Customer_Name"
                   Set objRS2 = objConn.Execute(strSQL)
                   While Not objRS2.EOF
                   %>
                  <option value=<%=objRS2("Project_Number")%> class="cls10px"><%=objRS2("Project_Number")%></option>
                   <%
                   objRS2.MoveNext
                   Wend
                   objRS2.Close
                   Set objRS2 = Nothing
                   %>
                  </select>
      </td>
</body>

YZlat

put this javascript in the head section of your document

<script language="javascript">

</script>

Then use the code below.
Before using it, replace "index.asp with your page

Major Customer :
<%
strSQL = " SELECT CustomerID, Customer_Name"
strSQL = strSQL & " FROM Customer_Index"
strSQL = strSQL & " ORDER BY Customer_Name"
%>
<select name="sel_Major" onchange="setFormAction("index.asp");" class="cls10px">
<option selected></option>
<%

Set objRS = objConn.Execute(strSQL)
While Not objRS.EOF
%>
<option value="<%=objRS("CustomerID")%>" class="cls10px" <%If objRS("CustomerID") = strCustomerID Then%>selected<%End If%>><%=objRS("Customer_Name")%></option>

<%
objRS.MoveNext
Wend
objRS.Close
Set objRS = Nothing
%>
</select>

Combo Box #2 :
Project Number:
<%
Dim strCustomerID
strCustomerID=request.form("sel_Major")
strSQL = "SELECT Project_Index.ProjectID, Project_Index.CustomerID, Project_Index.Project_Number, Project_Index.Description "
strSQL = strSQL & " FROM Project_Index "
strSQL = strSQL & " WHERE Project_Index.CustomerID = '"&strCustomerID&"'"
%>
<select name="sel_project_num" onchange="fillInfo2(document.form1.sel_project_num.value);" class="cls10px">
<option selected></option>
<%
Set objRS2 = objConn.Execute(strSQL)
While Not objRS2.EOF
%>
<option value="<%=objRS2("Project_Number")%>" class="cls10px"<%If objRS2("Project_Number") = strProjectNumber Then%>selected<%End If%>><%=objRS2("Project_Number")%></option>
<%
objRS2.MoveNext
Wend
objRS2.Close
Set objRS2 = Nothing
%>
</select>

Gwen21

ASKER

I think YZlat’s code is more what I am looking for. But still struggling to get the value from Combo one to the sql statement in combo two.

YZlat

make sure the form is submitted when the value of first combo box is changed, and then
use request.form("sel_Major")

Gwen21

ASKER

Ok that is where the probelm seems to be, the form is not submitting.

YZlat

ok, are your select boxes on the form or no?

ASKER CERTIFIED SOLUTION

YZlat