?
Solved

Use First Drop Down to Filter Second Drop Down

Posted on 2003-03-12
19
Medium Priority
?
208 Views
Last Modified: 2013-12-24
I'm trying to use a first drop down list on a form to filter the long possible list on the second drop down.  I'm using database results wizard on a form with many variables.  

I want to use the onChange command on the first drop down list and then filter the second list without submitting the form or opening a new form.  Is there a way to requery the second list?
0
Comment
Question by:pmalone773
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 5
19 Comments
 
LVL 14

Expert Comment

by:hhammash
ID: 8123311
0
 
LVL 14

Expert Comment

by:hhammash
ID: 8123459
Hi,

Another way without opening a new page:
I will use Customers Table to Select a country and filder the customers in it.

Create a new page and save it as Filter.asp, then:

1- Insert a table with two columns one row with no borders
2- In the First cell insert a form then remove the submit and reset buttons.
3- In this form insert Database results with one field (country) and in step 5/5 select Drop Down list - one record per item.
4- Next / Finish
5- Right Click the form and select properties
6- Name th form as FrmCountry
7- Go now to the next cell
8- Insert form
9- Remove the buttons (submit and reset)
10- Click inside the form and insert database/results
11- Select the databse then the table
12- Select only the ContactName field in step 3/5
13- In step 3/5 click More Options
14- Click Criteria
15- Select Country=Country, then OK then OK
16- Remove the check from checkbox limit records to 256
17- Click OK
18- Click Next
19- Select Drop Down list - One record per item
20- Click Next, then select display all records
21- Uncheck the Add search form
22- Click finish

Now we has a page with a table of two columns and one row,  the first cell has a form with DRW in it, and the second cell has another form with another DRW in it.  The DRWs are actually the drop down boxes.

Now:
----
1- Go back and click inside the first form FrmCountry
2- Right click and select Properties
3- Select send to other
4- Click Options
5- In the action field type Filter.asp (the name of the page which has the two forms and the table.
6- Ok then OK
7- Save the page

Now test to make sure that the Filtering Is OK.

I will stop here because I don't know what do you want to do later.  So my question is:  After you select the Country then the city what do you want to do.  Because what I have in mind is that you either want to search or submit information. In each case I have a solution for you.

Please let me know.

hhammash
0
 
LVL 14

Expert Comment

by:hhammash
ID: 8123543
Hi,

I forgot to mention.

You have the first form which has the country.  Click the country Drop Down and then click HTML view,  then after the size type:

onChange=FrmCountry.submit()


This will submit the first form to the page which triggers the second DRW to display related contact names.

As I said before.  Let me know the next stage, so I will give you the proper reply.  The replies are ready but let me know.

hhammash
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:pmalone773
ID: 8129300
EXCELLENT... BUT...

There are several other database results that the user will select and then submit the whole form.  For the sake of argument, lets say there are ten database results on the form.

The user makes selections on the first three lists.  On the fourth list, when the "Country" is selected, the fifth list "City" is filtered.  Then the user goes on to make selections on the last five lists.  When the submit button is clicked, all ten selections will post to a database table.

Now for the problem: when the fourth list "Country" is selected, the form is refreshed and the fifth list "City" is filtered, but the first four lists previously selected are reset to default values.

All ten selections on the form, including the selected "Country", need to be submitted to the database.

Just to make sure I followed your instructions, here is a different summary of the steps you gave:

1- Create a new page and save it as “Filter.asp”
2- Insert a table with two columns, one row, no borders.

FIRST CELL
3- Insert a form then remove the submit and reset buttons
4- Insert database results, in step 4/5 choose “Drop Down List – one record per item”, choose field “Country”
5- Select the drop down, then go to HTML view, after SIZE= “1” type onChange=frmCountry.submit()
6- Right click form, select form properties, name the form “FrmCountry”, select “Send to other”, click “Options”, and type Filter.asp in the Action: box.

SECOND CELL
7- Insert a form then remove the submit and reset buttons
8- Insert database results, in step 3/5 choose “More Options”, then choose “Criteria”, then choose “Add”, then select Field Name: “Country”, Comparison: “Equals”, Value: “Country”, check box for “Use this search form field”, click OK twice, remove check from “Limit number of returned...”, and finally click OK.
9- In step 4/5 choose “Drop Down List – one record per item”, choose field “City”
10- In step 5/5 uncheck “Add search form”
11- Go back to the Normal view, right click the form, select form properties, select “Send to”, and type Filter.asp in the File Name: box.

12- Save the page and test.

Thanks!!!

0
 
LVL 14

Expert Comment

by:hhammash
ID: 8129474
Ok,

So what you want is:

Select First Drop Down
Select Second Drop Down
Select Third Drop Down  (after this is selected)
Dynamically filter the fourth Drop down.  Here the form refreshes the first 3 lists and they lose what has been selected,  right?,  I have a solution for that, I will give it now.

First you have to put your fourth Drop down on diet.


I will close this post and start new ones.  One for maintaining the drop down selections. The other for gathering all selection in the form and sending them to the database.

hhammash

0
 

Author Comment

by:pmalone773
ID: 8129669
LOL...thanks!
0
 
LVL 14

Expert Comment

by:hhammash
ID: 8129720
Hi,

-------------  Maintain the Selected Value -------------

1- Go to the first drop down on the form
2- Click on it
3- Modify the code as shown, this code is for the normal drop down, not the city drop down,  the city drop down will have a different one.

<form method="POST" action="Levels.asp">
<select size="1" name="Level">
<option <%If Request.Form("Level") = "Level1" Then %>selected<%End If%>>Level1</option>

4- Do the same for all the drop downs except the City  and (maybe the country) which are DRWs.

Save the form.  

Now when you select the first, second and third, the selecion will remain.  Please note that Lelvels in the code above is the name of the drop down.  Level1 is the value selected.

The code above will work on any non DRW drop down.

----- How to maintain the value of a DRW Drop down? ----
1- Each DRW drop down has to be put on diet.
2- Modify the code as follows.

<select NAME="CusomerID" SIZE="1">
<option<%IF request("CustomerID") = FP_Field(fp_rs,"CustomerID") THEN response.write(" selected") END IF%>><%=FP_Field(fp_rs,"CustomerID")%></option>
</select>

Note that my above code has CustomerID as the name of the drop as in Name="CustomerID" and the database field is CustomerID too as in (fp_rs,"CustomerID")

Now whatever you select will remain.

hhammash
0
 
LVL 14

Accepted Solution

by:
hhammash earned 480 total points
ID: 8129867
Hi again,

-------- Building the whole thing -------

1- Insert Table with three columns and 3 rows
2- In the first Cell Insert form and remove the Buttons
3- In the second cell Insert form and remove the buttons
4- In the third cell Insert form and remove the buttons
5- In the second row first cell Insert form then remove the button
6- In the second row second cell insert form then remove the buttons.

Now you have 5 Forms,  each form in a cell.
7- Put the cursor in the third row, then select Table/Select Row,  then Table/Merge Cells.
8- Keep the buttons

Now in each of the forms above insert the DRW and leave the form in the third row blank.  Click Each non DRW drop down and put the code I gave you above to maintain the selected value.  Then click each DRW Drop and put it on diet and save the form.  You can use the DRW which I gave you in my first post to filter the cities.

Now you have three drop downs to select values with the code to maintain the value,  then you have the country DRW drop dieted and with the code to maintain the selection, and the city DRW dieted with the code to maintain the selection.

Now you should have 6 forms. 1 form for each Drop and the blank form in the third row.  (Note: The drop downs that are not database based can be inserted all in one form)

IMPORTANT:  The page that has all the forms should be saved as something like SubmitInfo.asp and all the forms should post to it except form number 6 which is in the third row.
---------  How to submit all that to the database? ----

Right click the empty form,  then click Form properties, the click advanced.

Now we will add hidden fields.

Click add
In the field name box type the name of the first drop (the name of the first drop should have an identical field in the database)
in the value put
<%=request.form("FirstDropName")%>

Then OK
Click add again and do the same for the second drop down

do it each time for all the drop downs.  Now you should have 5 hiddden fields.

Click OK and go back to the form.

Right click the form which is in the third row and select form properties then send to database
select the database, select the table and click saved fields and match the fields on the form with the table fields.

Not that the database will detect your hidden fields.

Finish and save teh SubmitInfo.asp page.

What will happen when you work:

Drop one is selected and will maintain the value
Drop two is selected and will maintain the value
Drop three is selected and will maintain the value
Drop Four is selected and will maintain the value and filter drop down five (the city).
Drop down 5 (the city) is selected and will maintain the value.  Note that each drop down has the onChange=FormName.submit() and in the Action property of all these drop down you should put SubmitInfo.asp.

The form in row three will collect all the values from the above 5 forms and put the values in hidden fields and sends them to the database.

Regards
hhammash
0
 
LVL 14

Expert Comment

by:hhammash
ID: 8129901
Hi,

IF YOU HAVE A PROBLEM IN FOLLOWING ALL THESE STEPS YOU CAN SEND ME THE  TABLE BY EMAIL,  I WILL MAKE IT FOR YOU AND EMAIL IT BACK.

MY EAMIL IS HMAF@YAHOO.COM

HHAMMASH
0
 

Author Comment

by:pmalone773
ID: 8129953
LOL...thanks!
0
 
LVL 14

Expert Comment

by:hhammash
ID: 8129972
You are welcome,

hhammash
0
 
LVL 14

Expert Comment

by:hhammash
ID: 8151623
Hi,

I will make a sample for you but with less fields OK.  Your form is huge.

OK?

hhammash
0
 

Author Comment

by:pmalone773
ID: 8151981
Any help is GREATLY appreciated.  You are too good.  Thank you!!!
0
 
LVL 14

Expert Comment

by:hhammash
ID: 8152427
Hi,

You are welcome.

I will work on it now.

hhammash
0
 
LVL 14

Expert Comment

by:hhammash
ID: 8158380
Hi pmalone773,

Copy the code under the dotted line and see how I handled the Drop Down Boxes that are just Drop Downs not populated by the Database.

---------------------------------------------

<html>

<head>
<meta http-equiv="Content-Language" content="en-us">
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>

<body>

<form method="POST" name="MyForm" action="DateRange2.asp">
  <table border="0" cellspacing="1" width="100%" id="AutoNumber1">
    <tr>
      <td width="21%"><select size="1" name="MonthName" onChange=MyForm.submit()>
      <option selected>Select Month</option>
      <option <%If Request.form("MonthName")="January" Then %>Selected<% End if %>>January</option>
      <option <%If Request.form("MonthName")="February" Then %>Selected<% End if %>>February      </option>
      <option <%If Request.form("MonthName")="March" Then %>Selected<% End if %>>March</option>
      </select></td>
      <td width="79%"><nobr>
<!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
      </SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM DayDrop WHERE (MonthName =  '::MonthName::')"
fp_sDefault="MonthName="
fp_sNoRecords="No records returned."
fp_sDataConn="DateRange"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="DayNo"
fp_sMenuValue="DayNo"
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<select NAME="DayNo" SIZE="1">
<option selected>Select a Day</option>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<option <%IF request.form("DayNo")=FP_FieldHTML(fp_rs,"DayNo") Then %>Selected<% End if%>><%=FP_FieldHTML(fp_rs,"DayNo")%></option>
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
      </select>&nbsp; <select size="1" name="Year">
      <option <%if request.form("Year")="1999" Then %>Selected<% End if%>>1999</option>
      <option <%if request.form("Year")="2000" Then %>Selected<% End if%>>2000</option>
      <option <%if request.form("Year")="2001" Then %>Selected<% End if%>>2001</option>
      <option <%if request.form("Year")="2002" Then %>Selected<% End if%>>2002</option>
      <option <%if request.form("Year")="2003" Then %>Selected<% End if%>>2003</option>
      <option <%if request.form("Year")="2004" Then %>Selected<% End if%>>2004</option>
      </select></nobr></td>
    </tr>
    <tr>
      <td width="21%">
      <select size="1" name="MonthName2" onChange=MyForm.submit()>
      <option selected>Select Month</option>
      <option <%If Request.form("MonthName2")="January" Then %>Selected<% End if %>>January</option>
      <option <%If Request.form("MonthName2")="February" Then %>Selected<% End if %>>February      </option>
      <option <%If Request.form("MonthName2")="March" Then %>Selected<% End if %>>March</option>
      </select></td>
      <td width="79%"><nobr>
<!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
      </SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM DayDrop WHERE (MonthName =  '::MonthName2::')"
fp_sDefault="MonthName2="
fp_sNoRecords="No records returned."
fp_sDataConn="DateRange"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="DayNo"
fp_sMenuValue="DayNo"
fp_iDisplayCols=1
fp_fCustomQuery=True
BOTID=1
fp_iRegion=BOTID
%>
<select NAME="DayNo2" SIZE="1">
 <option selected>Select A Day</option>
 <!--#include file="../_fpclass/fpdbrgn1.inc"-->
<option <%IF request.form("DayNo2")=FP_FieldHTML(fp_rs,"DayNo") Then %>Selected<% End if%>><%=FP_FieldHTML(fp_rs,"DayNo")%></option>
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
      </select>&nbsp; <select size="1" name="Year2">
      <option <%if request.form("Year2")="1999" Then %>Selected<% End if%>>1999</option>
      <option <%if request.form("Year2")="2000" Then %>Selected<% End if%>>2000</option>
      <option <%if request.form("Year2")="2001" Then %>Selected<% End if%>>2001</option>
      <option <%if request.form("Year2")="2002" Then %>Selected<% End if%>>2002</option>
      <option <%if request.form("Year2")="2003" Then %>Selected<% End if%>>2003</option>
      <option <%if request.form("Year2")="2004" Then %>Selected<% End if%>>2004</option>

      </select></nobr></td>
    </tr>
  </table>
  <p><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></p>
</form>
<%
Dim varMonth,varMontha,varDay,varDaya,varYear,varYeara
varMonth=request.form("MonthName")
varDay=request.form("DayNo")
varYear=request.form("Year")
varMontha=request.form("MonthName2")
varDaya=request.form("DayNo2")
varYeara=request.form("Year2")

response.write varDay+"/"+varMonth+"/"+varYear+"<BR>"
response.write varDaya+"/"+varMontha+"/"+varYeara+"<BR>"
%>

</body>
</html>


Best regards
hhammash

0
 
LVL 14

Expert Comment

by:hhammash
ID: 8158415
Hi again,

Now see how I dandled the Drop Down box that is populated by the database after putting the DRW on Spooky Diet.

-------------------------------

fp_iRegion=BOTID
%>
<select NAME="CustomerID" SIZE="1">
      <!--#include file="_fpclass/fpdbrgn1.inc"-->
      <option <%IF request("CustomerID") = FP_Field(fp_rs,"CustomerID") THEN response.write(" selected")%>><%=FP_Field(fp_rs,"CustomerID")%> </option>
      <!--#include file="_fpclass/fpdbrgn2.inc"-->
      </select>
</nobr></td>


Best regards
hhammash
0
 
LVL 14

Expert Comment

by:hhammash
ID: 8158451
Hi again,

Now see how I handled the Drop Down box that is populated by the database after putting the DRW on Spooky Diet.

-------------------------------

fp_iRegion=BOTID
%>
<select NAME="CustomerID" SIZE="1">
      <!--#include file="_fpclass/fpdbrgn1.inc"-->
      <option <%IF request("CustomerID") = FP_Field(fp_rs,"CustomerID") THEN response.write(" selected")%>><%=FP_Field(fp_rs,"CustomerID")%> </option>
      <!--#include file="_fpclass/fpdbrgn2.inc"-->
      </select>
</nobr></td>
-------------------------------------------------

Now see below how I handled Drop Down box that is populated by the Database (DRW Like in your form) without putting it on diet. Don't be confused,  just concentrate on the line that starts with <Option and ends with </Option>.  Replace the original <Option>....</Option> with the one I put below.

---------------------------------------
fp_iRegion=BOTID
%>
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="50989" --><select NAME="CustomerID" SIZE="1">
  <!--webbot bot="AspInclude" clientside u-incfile="_fpclass/fpdbrgn1.inc" startspan --><!--#include file="_fpclass/fpdbrgn1.inc"--><!--webbot bot="AspInclude" endspan i-checksum="62210" -->

<option <%IF request("CustomerID") = FP_Field(fp_rs,"CustomerID") THEN response.write(" selected")%>><%=FP_Field(fp_rs,"CustomerID")%> </option>


  <!--webbot bot="AspInclude" clientside u-incfile="_fpclass/fpdbrgn2.inc" startspan --><!--#include file="_fpclass/fpdbrgn2.inc"--><!--webbot bot="AspInclude" endspan i-checksum="62218" -->
  </select><!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE" b-menuformat="TRUE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside preview=" &lt;span style=&quot;color: rgb(0,0,0); background-color: rgb(255,255,0)&quot;&gt;Results&lt;/span&gt; " startspan --><!--webbot bot="DatabaseRegionEnd" endspan --></nobr></p>
  <p><input type="submit" value="Submit" name="B1"></p>
</form>

------------------



Best regards
hhammash
0
 

Author Comment

by:pmalone773
ID: 8270955
This is an excellent way to handle the situation when there are a small number of fields.  Because my form is so large and has many of these dropdown lists, its taking forever to reload the page when you make a choice.

I am now looking into a different method where the database table is pre-loaded into an array with Java Script.  It is more complicated but I hope it will yield faster results while the form is being used.

Thanks!
0
 
LVL 14

Expert Comment

by:hhammash
ID: 8271271
Hi pmalone773,

You are most welcome.

We will see how to do it when you post the question.

Regards
hhammash
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Turn A Profile Picture Into A Cartoon Using Photoshop And Illustrator This tutorial will teach you how to make a cartoon style image out of a regular picture. I have tried to keep the tutorial as simple as possible. I used Adobe CS4 for this tuto…
Originally, this post was published on Monitis Blog, you can check it here . It goes without saying that technology has transformed society and the very nature of how we live, work, and communicate in ways that would’ve been incomprehensible 5 ye…
The purpose of this video is to demonstrate how to Import and export files in WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Click on Too…
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question