Creating a Combo Box that only includes info driven from the selection from another Combo box

I'm enhancing our employee search list. Right now I have part of it working. You can enter a first and or last name and get that person's info. I'm now trying to add a feature where you have a company dropdown where you can select a company and then only the departments that apply within that company appear in another dropdown, you can then select the department and all of the employees within the specified company and department show up. I have it set up where I have an employee table, department table, and company table in access - but linked to SQL Server, So I know it'll be a SQL statement and possibly some VBScript. Below, I've attached the code that I've started with to get the company dropdown I need help with the query to make it dynamically go to the departments that apply to that company and then pull the employee info:

<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN">
<html>

<head>
<meta name="GENERATOR" content="Microsoft FrontPage 3.0">
<title>Webphonelist</title>
<meta name="GENERATOR" content="Microsoft FrontPage 3.0">
<meta name="Microsoft Theme" content="aagrnd 010, default">
<meta name="Microsoft Border" content="tlb, default">
</head>

<body>

<p><!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN"> </p>

<p><!--webbot bot="HTMLMarkup" startspan -->
<!-- #INCLUDE FILE="_private/ADOConnect.inc" -->
<%
SQLQuery = "SELECT AAGIS.dbo.Employee_Master_Active.First_Name First_Name, AAGIS.dbo.Employee_Master_Active.Last_Name Last_Name, AAGIS.dbo.Employee_Master_Active.Work_Telephone Work_Telephone,AAGIS.dbo.Employee_Master_Active.Dept_Desc Dept_Desc,AAGIS.dbo.Employee_Master_Active.Location_Description Location_Description,AAGIS.dbo.Employee_Master_Active.Mail_Stop Mail_Stop, AAGIS.dbo.Employee_Master_Active.Company_ID Company_Name FROM AAGIS.dbo.Employee_Master_Active where Company_ID = AAGIS.dbo.Employee_Master_Active.Company_Name and Company_Name =  + Request.Form ("Company_Name")"
Set RS = Conn.Execute(SQLQuery)
%>
<p>
<table border="0" width="600">
          
            <tr>
        <td colspan="3"><font color="#000080" size="3"
        face="Verdana, Arial, Helvetica"><em><strong>
      <img src="images/64x_book.gif" width="56" height="48"
alt="64x_book.gif (3614 bytes)">
AAG Phonelist:
      </strong></em></font><font color="#000080"
        size="3" face="Verdana, Arial, Helvetica"><em><strong> </strong></em></font><hr
        size="4">
        </td>

<tr>
          <tr>
            <table border="0" cellpadding="2" colspan="5">
                <font size="2" face="Verdana, Arial, Helvetica"><!-- BEGIN column header row  --></font>
                <tr>
                               

                          <td align="center" bgcolor="#000080"><font
                          color="#FFFFFF" size="2"
face="Verdana, Arial, Helvetica"><font STYLE="Verdana, Arial,  Helvetica"><strong>First Name</font></strong></font><font
                          size="2" face="Verdana, Arial, Helvetica"> </font></td>

                          <td align="center" bgcolor="#000080"><font
                          color="#FFFFFF" size="2"
face="Verdana, Arial, Helvetica"><font STYLE="Verdana, Arial,  Helvetica"><strong>Last Name</font></strong></font><font
                          size="2" face="Verdana, Arial, Helvetica"> </font></td>

                          <td align="center" bgcolor="#000080"><font
                          color="#FFFFFF" size="2"
face="Verdana, Arial, Helvetica"><font STYLE="Verdana, Arial,  Helvetica"><strong>Business Phone Number</font></strong></font><font
                          size="4" face="Verdana, Arial, Helvetica"> </font></td>

                                          <td align="center"  bgcolor="#000080"><font
                          color="#FFFFFF" size="2"
face="Verdana, Arial, Helvetica"><font STYLE="Verdana, Arial,  Helvetica"><strong>Department</font></strong></font><font
                          size="2" face="Verdana, Arial, Helvetica"> </font></td>
                                          
                                          <td align="center"  bgcolor="#000080"><font
                          color="#FFFFFF" size="2"
face="Verdana, Arial, Helvetica"><font STYLE="Verdana, Arial,  Helvetica"><strong>Location</font></strong></font><font
                          size="4" face="Verdana, Arial, Helvetica"> </font></td>

                                          <td align="center"  bgcolor="#000080"><font
                          color="#FFFFFF" size="2"
face="Verdana, Arial, Helvetica"><font STYLE="Verdana, Arial,  Helvetica"><strong>Mail Stop</font></strong></font><font
                          size="4" face="Verdana, Arial, Helvetica"> </font></td>


                                          <td align="center"  bgcolor="#000080"><font
                          color="#FFFFFF" size="2"
face="Verdana, Arial, Helvetica"><font STYLE="Verdana, Arial,  Helvetica"><strong>Company</font></strong></font><font
                          size="2" face="Verdana, Arial, Helvetica"> </font></td>

                 

</tr>

<font size="2" face="Verdana, Arial, Helvetica"><!-- BEGIN first row of inserted phonelist data  --></font><font
size="2" face="Verdana, Arial, Helvetica"><% Do While Not RS.EOF %></font>
               
                                           <tr>

<td align="left" bgcolor="#FDFFEA"><font
size="2" face="Verdana, Arial, Helvetica"><font STYLE="Verdana, Arial, Helvetica"></font><font
size="2"><%=RS("First_Name")%></font><font size="2"
face="Verdana, Arial, Helvetica"></font></font></td>

<td align="left" bgcolor="#FDFFEA"><font
size="2" face="Verdana, Arial, Helvetica"><font STYLE="Verdana, Arial,  Helvetica"></font><font
size="2"><%=RS("Last_Name")%></font><font size="2"
face="Verdana, Arial, Helvetica"></font></font></td>
             
<td align="left" bgcolor="#FDFFEA"><font
size="2" face="Verdana, Arial, Helvetica"><font STYLE="Verdana, Arial, Helvetica"></font><font
size="2"><%=RS("Work_Telephone")%></font><font size="2"
face="Verdana, Arial, Helvetica"></font></font></td>      
        
                     
<td align="left" bgcolor="#FDFFEA"><font
size="2" face="Verdana, Arial, Helvetica"><font STYLE="Verdana, Arial, Helvetica"></font><font
size="2"><%=RS("Dept_Desc")%></font><font size="2"
face="Verdana, Arial, Helvetica"></font></font></td>
             
<td align="left" bgcolor="#FDFFEA"><font
size="2" face="Verdana, Arial, Helvetica"><font STYLE="Verdana,      Arial,  Helvetica"></font><font
size="2"><%=RS("Location_Description")%></font><font size="2"
face="Verdana, Arial, Helvetica"></font></font></td>
             
<td align="left" bgcolor="#FDFFEA"><font
size="2" face="Verdana, Arial, Helvetica"><font STYLE="Verdana,      Arial,  Helvetica"></font><font
size="2"><%=RS("Mail_Stop")%></font><font size="2"
face="Verdana, Arial, Helvetica"></font></font></td>

<td align="left" bgcolor="#FDFFEA"><font
size="2" face="Verdana, Arial, Helvetica"><font STYLE="Verdana,  Arial, Helvetica"></font><font
size="2"><%=RS("Company_Name")%></font><font size="2"
face="Verdana, Arial, Helvetica"></font></font></td>              





                              </tr><font size="2" face="Verdana, Arial, Helvetica"><%
      RS.MoveNext
Loop
Conn.Close
Set Conn = Nothing
%></font>
            </table>
        </form>
        </td>
    </tr>
</table>
<p></p>
<p> </p><hr>
<!--webbot bot="HTMLMarkup" endspan --> </p>
 </body>
</html>
 
Kerri10Asked:
Who is Participating?
 
PepsterConnect With a Mentor Commented:
Right, I have the two comboboxes working by doing this:

Assume your form is called frmTest
and the filename is Test.asp


1) Create two comboboxes and populate the first one from the database.

2) Make sure the form submits to itself by default eg:
<form method="post" action="Test.asp" name="frmTest">

3) in the select tags of the 1st combobox put this:

onchange=frmTest.submit()

4) Do the second combo like this:
<select name=cbo2 size=1><option>
            <%if trim(Request.Form("cbo1")) <> "" then FillSecondCombo()%>
</select>

and in the FillSecondCombo routine you can do a WHERE clause on the recordset according to the value of then first combo box.

5) At the top of you ASP put a test to check to see whether there is a value in the second combobox and if so response.redirect to your table page.

eg:
<%
if trim(request.form("cb2")) <> "" then response.redirect("yourtablepage.asp")
%>

Hope this helps with the two dynamic combo boxes, now you just need to include the values in your query on your table page.



0
 
PepsterCommented:
is it just me is is there no combo box here.

From what I can see it populates a table.
0
 
Kerri10Author Commented:
I apologize that was the results page which is a table, here's the combo box portion:

<html>

<head>
<meta name="GENERATOR" content="Microsoft FrontPage 3.0">
<title>Phonelist Selection</title>
<meta name="Microsoft Theme" content="aagrnd 010, default">
<meta name="Microsoft Border" content="b">
</head>

<body>
<div align="left">

<table border="2" width="100%" style="border: medium none rgb(255,255,255)"
cellpadding="8">
  <tr>
    <td width="154" height="61" valign="top" bordercolor="#FFFFFF" bordercolorlight="#FFFFFF"
    bordercolordark="#FFFFFF"></td>
    <td height="61" bordercolor="#FFFFFF" bordercolorlight="#FFFFFF" bordercolordark="#FFFFFF"
    width="100%"><!--webbot bot="Navigation" S-Type="banner" S-Rendering S-Orientation
    B-Include-Home B-Include-Up --></td>
  </tr>
  <tr>
    <td width="154" valign="top" bordercolor="#FFFFFF" bordercolorlight="#FFFFFF"
    bordercolordark="#FFFFFF"><!--webbot bot="ImageMap"
    text="(20,4) (74, 26) {Back} {Times New Roman} 10 B #000000 CT 0  http://aagweb"
    src="images/back1.gif" alt="back1.gif (826 bytes)" border="0" --></td>
    <td valign="top" bordercolor="#FFFFFF" bordercolorlight="#FFFFFF"
    bordercolordark="#FFFFFF" width="100%"><img src="images/64x_book.gif" width="56"
    height="48" alt="64x_book.gif (3614 bytes)"><font color="#000040" face="Matisse ITC"><em><big><strong><big><big>To
    view the list....</big></big></strong></big></em></font><div align="left"><table
    border="1" width="43%">
      <tr>
        <td width="100%" bgcolor="#000040"><p align="center"><strong><em><font color="#FFFFFF"><font
        size="3">Select a Company from the list box, <br>
        then Click</font><font size="4"> </font></font><font size="3"><font color="#80FFFF"><font
        face="Viner Hand ITC">Find by Company</font></font><font color="#FFFFFF">.</font></font></em></strong></td>
      </tr>
      <tr>
        <td width="100%" valign="middle" align="center" bgcolor="#FBFDC8"><br>
        <br>
        <!--webbot bot="HTMLMarkup" startspan -->
<!-- #INCLUDE FILE="_private/ADOConnect.inc" -->
<%
SQLQuery = "Select Company_ID, Company_Name from AAGIS.dbo.Company_Master order by Company_Name Asc"

Set RS = Conn.Execute(SQLQuery)
%>
<form action="Webcompany.asp" method="POST">
    <p><select name="Company_Name" size="1">
<% Do While Not RS.EOF %>
      <option value="<%=RS("Company_ID")%>"><%=RS("Company_Name")%></option>
 

 

      <%RS.MoveNext
Loop
Conn.Close
set Conn = Nothing
%>
    </select></p>
    <p><input type="submit" value="Find by Company..."></p>
</form></td>
      </tr>
    </table>
    </div><p>&nbsp;</p>
   
<!--webbot bot="HTMLMarkup" endspan --> <p>&nbsp;</p>
        <p align="right">&nbsp;</td>
      </tr>
    </table>
    </div><p>&nbsp; </td>
  </tr>
</table>
</div>

<p>&nbsp;
</body>
</html>
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
PepsterCommented:
So if I have this right, when you select a company you want another ComboBox to appear with a filtered list of departments, and when a department is selected then the table comes up with all the details?
0
 
Kerri10Author Commented:
that's correct
0
 
PepsterCommented:
is it just me is is there no combo box here.

From what I can see it populates a table.
0
 
PepsterCommented:
oops, didn`t want to resubmit that!

0
 
Kerri10Author Commented:
ok - cause I got a little confused :)
0
 
TTomCommented:
Kerri:

You probably have a couple of different options here.

On the one hand, you can simply use this form to submit to another form, which would create the second dropdown, using the selected value from the first form.

Another option is to use some sort of "remote scripting" solution to populate a second dropdown list "dynamically" from a selection in the first list.  This would allow the second list to be populated without necessitating the entire page to be reloaded, and could probably be extended to populate the final table when all selections have been made.

(I offer these as suggestions; I am not a real remote scripting guru, so I don't know how it would be done.  I have just seen it mentioned a number of times recently.)

Best luck,

Tom
0
 
PepsterCommented:
I am currently trying to let a second combo populate from the first on the same form so please bear with me as this will look much better than two combo boxes on two different pages.
0
 
RBertoraCommented:

<% 'THIS DEMO I JUST KNOCKED UP TO SHOW YOU HOW TO
   'HAVE 2 DYNAMIC DROP DOWN BOXES, YOU CAN EXTEND THIS
   'TO INCLUDE MORE THAN 2 BOXES OR EVEN TABLES EVENTUALLY
   'BY SIMPLY ADDING ELSE IF STATEMENTS IN THE IF THEN STATEMENT

   'THE IDEA IS TO POPULATE THE FIRST DROP DOWN BOX (CUSTOMERS num and Name)
   'WHEN THE USER CLICKS ON THIS BOX, THE JAVA SCRIPT FUNCTION IS INVOKED
   'AND SUMBITS TO THE SAME PAGE

   'THE PAGE IF THEN STATEMENT DETECTS THAT IT IS THE SECOND TIME IT IS BEING
   'LOADED BECAUSE THE QUERYSTRING PARAMETER IS NOT "" AND THEREFORE
   'POPULATES BOTH DROP DOWN BOXES (THE SECOND ONE TOO BASED ON THE VALUE
   'CLICKED ON THE FIRST)

   'Rob;-)

   'P.S.
   'Sorry if this is not in your data field terms but you should be able
   'to translate it to what you need
%>

<% 'THIS FUNCTION WILL SUBMIT TO THE SAME FORM PASSING THE FIRST DROP DOWN
   'AS A PARAMETER IN THE QUERYSTRING %>
<script language="Javascript">
function nextPage(){
  tempValue = document.NewCustForm.CustomerDropDown.options[document.NewCustForm.CustomerDropDown.selectedIndex].value;
  NewUrl = "/Robwork/demo/Multilistbox.asp"+"?CN=" + tempValue
  location.href = NewUrl
}
</script>



<HTML>
<HEAD>
<TITLE>Rob Demo Login</TITLE>
</HEAD>
<BODY  LEFTMARGIN=0 TOPMARGIN=0 bgcolor = #eeeeee >


<%' connect to db
      Session("dbConn") = "dsn=Tess41SQL;UID=TessDemo;Password=orc"
  strConn = Session("dbConn")
  set xConn = Server.CreateObject("ADODB.Connection")
  xConn.Open StrConn
%>



<% 'check that this is the first time the page is being loaded
   If Request.QueryString("CN") = "" then
   ' if it is then just load the fist drop down box with customer names else load both dropdowns
%>
   <FORM name = "NewCustForm" ACTION="/Robwork/demo/Multilistbox.asp" METHOD=POST>

    <%
      MySql="Select distinct cust_num,cust_name from sccust order by cust_name "
      set rs = Server.CreateObject("ADODB.Recordset")
      rs.Open MySQL, xConn, 1, 2
    %>

    <SELECT Name = "CustomerDropDown" onchange = "javascript: nextPage();">
      <%do%>
       <OPTION value = "<%=Rs(0)%>"> <% Response.write(Rs(0)& ":" & Rs(1)) %>
      <% Rs.MoveNext
      loop while Rs.Eof = 0 %>
    </SELECT>

    <SELECT Name = "CallNumbersDropDown">
    </SELECT>

<% Else
  ' if it is the second time then populate the fist drop down,
  'but also the second drop down too
%>

     <FORM name = "NewCustForm" ACTION="/Robwork/demo/Multilistbox.asp" METHOD=POST>

    <%' second time round populate both drop downs :first one
      MySql="Select distinct cust_num,cust_name from sccust order by cust_name "
      set rs = Server.CreateObject("ADODB.Recordset")
      rs.Open MySQL, xConn, 1, 2
      response.write("dfgd")
    %>

        <SELECT Name = "CustomerDropDown" onchange = "javascript: nextPage();">
          <%do%>
           <OPTION value = "<%=Rs(0)%>"> <% Response.write(Rs(0)& ":" & Rs(1)) %>
          <% Rs.MoveNext
          loop while Rs.Eof = 0 %>
        </SELECT>

    <%' second time round populate both drop downs :second one
      MySql="Select call_num  from sccall where call_cust_num = '" & Request.QueryString("CN") &"'"
      set rs = Server.CreateObject("ADODB.Recordset")
      rs.Open MySQL, xConn, 1, 2
    %>

        <SELECT Name = "CallNumbersDropDown" >
          <%do%>
           <OPTION value = "<%=Rs(0)%>"> <% Response.write(Rs(0)) %>
          <% Rs.MoveNext
          loop while Rs.Eof = 0 %>
        </SELECT>



<% End If %>

  </FORM>

</BODY></HTML>
0
 
mgfranzCommented:
I have a script that does this within the same page if interested...  It populates a dropdown list with dB entries, then depending on which is selected, populates a second list without a button select.  And here is the fun part, this is all done with one script file. Take a look at this; (you will have to change the dB and field names to suit your needs.  I use this to select wines, you select a country, then depending on which colors are associated with the country, it populates the second list.  It goes on, but you get the idea...)

Mark

<%
Dim objDC, objRS

Set objDC = Server.CreateObject("ADODB.Connection")
objDC.ConnectionTimeout = 30
objDC.CommandTimeout = 60
objDC.Open "DBQ=" & Server.MapPath("test.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"
Set objRS = Server.CreateObject("ADODB.Recordset")
%>

<HTML>
<HEAD>

</HEAD>
<BODY BGCOLOR="FFFFFF" TEXT="000000" LINK="0000FF" VLINK="800080">
<%
PSQL = "SELECT * FROM Test99 Order by Country;"
objRS.Open PSQL, objDC, 3, 3
%>

<%
Action = Request("Action")
If Action = "" then Action = "Get"
%>

<% If Action = "Get" then %>
<!-- ACTION="form.asp" METHOD="get"-->
<FORM method="get">
<SELECT NAME="ID" onChange="top.location.href=this.form.ID.options[this.form.ID.selectedIndex].value">
<OPTION> Select a Name&nbsp;</OPTION>
<%
Do While Not objRS.EOF
%>

<OPTION VALUE="?Country=<% =objRS.Fields("Country") %>&Action=View"><% =objRS.Fields("Country") %></OPTION>
<%
objRS.MoveNext
Loop
%>

</SELECT>
</form>
<%
objRS.Close
'Set objRS =  Nothing
objDC.Close
'Set objDC = Nothing
%>

<% ElseIf Action="View" then %>
<%
'Dim objDC, objRS

Set objDC = Server.CreateObject("ADODB.Connection")
objDC.ConnectionTimeout = 30
objDC.CommandTimeout = 60
objDC.Open "DBQ=" & Server.MapPath("test.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"
Set objRS = Server.CreateObject("ADODB.Recordset")
%>
<%
PSQL = "SELECT Distinct Color FROM Test99 WHERE Country='" & Request("Country") & "'"
objRS.Open PSQL, objDC, 3, 3
%>
<html>
<body>
<SELECT NAME="Name">
<OPTION> <%=Request("Country") %></OPTION>
</select>
<% 'objRS.MoveFirst
%>
<FORM method="get">
<SELECT NAME="ID" onChange="top.location.href=this.form.ID.options[this.form.ID.selectedIndex].value">
<OPTION> Select a Name&nbsp;</OPTION>
<%
Do While Not objRS.EOF
%>

<OPTION VALUE="?Color=<% =objRS.Fields("Color") %>&Action=View"><% =objRS.Fields("Color") %></OPTION>
<%
objRS.MoveNext
Loop
%>

</SELECT>
</form>
<%
objRS.Close
Set objRS =  Nothing
objDC.Close
Set objDC = Nothing
%>
<% End If %>
</body>
</html>
0
 
Kerri10Author Commented:
Would like to grade Pepster
0
 
Kerri10Author Commented:
Thank you
0
 
RBertoraCommented:
Well done Pepster.

Rob;-)
0
 
PepsterCommented:
Cheers :)

and thanks Kerri
0
All Courses

From novice to tech pro — start learning today.