Linked Drop Drop Menus

jcpelejo · Mar 13, 2002

Hello. I am having problems linking my drop down menus so that if you pick one item from the first drop down menu, it filters the next drop down menu. When you select the second item from the second drop down menu it filters the third drop down menu. Once all 3 items are chosen and the person clicks on Submit, the values are saved in a table called [Update Claim Detail Details]. Please advise. I have included the code that I have been working on.

<%@ Language=VBScript %>
<% OPTION EXPLICIT %>

<html>
<head>
<title>Vehicle Description</title>

</head>

<body bgcolor="#FFFFFF" text="#000000">

<%
'------------------------
'Declare all variables
'------------------------
Dim adoCon
Dim strCon
Dim strAccessDB
Dim sqlVehicleYear, sqlVehicleMake, sqlVehicleModel
Dim rsVehicleYear, rsVehicleMake, rsVehicleModel
Dim strYear, strMake, strModel

'--------------------------------
'Create connection to database
'--------------------------------
Set adoCon = Server.CreateObject("ADODB.Connection&quot

strAccessDB = "\fpdb\claim.mdb"
strCon = "DRIVER={Microsoft Access Driver (*.mdb)};uid=;pwd=patrick; DBQ=" & Server.MapPath(strAccessDB)
adoCon.Open strCon

'-------------------
'Create recordset
'-------------------
Set rsVehicleYear = Server.CreateObject("ADODB.Recordset&quot

'---------------------------------
'Initialize combo box variables
'---------------------------------
strYear = Request("cboVehicleYear&quot

strMake = Request("cboVehicleMake&quot

strModel = Request("cboVehicleModel&quot

'--------------------------
'Initialize SQL statements
'--------------------------
sqlVehicleYear = "SELECT DISTINCT VehicleYear FROM [Vehicle Description]"
sqlVehicleMake = "SELECT DISTINCT VehicleMake FROM [Vehicle Description]"
sqlVehicleModel = "SELECT DISTINCT VehicleModel FROM [Vehicle Description]"

%>
<form name="frmVehicleDescription" method="POST" action="claim.asp">
<SELECT name=cboVehicleYear LANGUAGE=JavaScript>
<%
'--------------------
'Create a recordset
'--------------------
Set rsVehicleYear = Server.CreateObject("ADODB.Recordset&quot

'----------------------------------
'Open connection to the recordset
'----------------------------------
rsVehicleYear.Open sqlVehicleYear, strCon

'----------------------------------------------------------------
'Verify information is valid and post as a list (1st combo box)
'----------------------------------------------------------------
Do While Not rsVehicleYear.EOF
Response.Write "<OPTION VALUE = '" & rsVehicleYear ("VehicleYear&quot

& "'>"
Response.Write rsVehicleYear("VehicleYear&quot

& "</OPTION>"
rsVehicleYear.MoveNext
Loop

rsVehicleYear.Close
Set rsVehicleYear = Nothing
%>
</SELECT>
<SELECT name=cboVehicleMake LANGUAGE=JavaScript>
<%

'--------------------
'Create a recordset
'--------------------
Set rsVehicleMake = adoCon.Execute(sqlVehicleMake)

'----------------------------------------------------------------
'Verify information is valid and post as a list (2nd combo box)
'----------------------------------------------------------------
Do While Not rsVehicleMake.EOF
Response.Write "<OPTION VALUE = '" & rsVehicleMake ("VehicleMake&quot

& "'>"
Response.Write rsVehicleMake("VehicleMake&quot

& "</OPTION>"
rsVehicleMake.MoveNext
Loop

rsVehicleMake.Close
Set rsVehicleMake = Nothing
%>
</SELECT>

<select name=cboVehicleModel language=JavaScript>
<%
'--------------------
'Create a recordset
'--------------------
Set rsVehicleModel = Server.CreateObject("ADODB.Recordset&quot

'------------------------------------
'Open connection to the recordset
'------------------------------------
rsVehicleModel.Open sqlVehicleModel, strCon

'-----------------------------------------------------------------
'Verify information is valid and post as a list (3rd combo box)
'-----------------------------------------------------------------
Do While Not rsVehicleModel.EOF
Response.Write "<OPTION VALUE = '" & rsVehicleModel ("VehicleModel&quot

& "'>"
Response.Write rsVehicleModel("VehicleModel&quot

& "</OPTION>"
rsVehicleModel.MoveNext
Loop

rsVehicleModel.Close
Set rsVehicleModel = Nothing

%>
</select>
</form>

</body>
</html>
Life is too short to waste...
Julius Pelejo
jcpelejo@hotmail.com

jcpelejo · Mar 14, 2002

Hello. I was able to link the combo boxes but the only thing that I have a problem with is removing all redundant records from the listings. Please advise. I have attached the code that I have.

<%@ Language=VBScript %>
<% Option Explicit %>
<%
Call Main()

Sub Main()
' If the form is submitted, just display the selected country and state
If Request.Form("cmdSubmit&quot

<> "" Then
Response.Write "<B> VehicleYear = " & Request.Form("cboVehicleYear&quot

& _
" VehicleMake = " & Request.Form("cboVehicleMake&quot

& " VehicleModel = " & Request.Form("cboVehicleModel&quot

& "</B>"
Exit Sub
End If

Dim objConnection ' ADO Connection object
Dim strAccessDB ' Database name
Dim strCon ' Microsoft Access Driver
Dim strSQL ' SQL query to be executed
Dim strSQL1 ' SQL query to be executed
Dim strSQL2 ' SQL query to be executed
Dim strSQL3 ' SQL query to be executed

Set objConnection = Server.CreateObject("ADODB.Connection&quot

strAccessDB = "\fpdb\claim.mdb"
strCon = "DRIVER={Microsoft Access Driver (*.mdb)};uid=;pwd=patrick; DBQ=" & Server.MapPath(strAccessDB)
objConnection.Open strCon

Dim rsVehicleYear ' recordset that holds the Vehicle Year Information
Dim rsVehicleMake ' recordset that holds the Country Information
Dim rsVehicleModel ' recordset that holds the State Information

Dim strVehicleYear ' holds the Vehicle Year
Dim strVehicleMake ' holds the Country ID
Dim strVehicleModel ' holds the State ID

Set rsVehicleYear = Server.CreateObject("ADODB.Recordset&quot

Set rsVehicleMake = Server.CreateObject("ADODB.Recordset&quot

Set rsVehicleModel = Server.CreateObject("ADODB.Recordset&quot

strSQL = "SELECT DISTINCT * FROM [Vehicle Description]"

Set rsVehicleYear = objConnection.Execute(strSQL)

' Check if there is a Vehicle Year selected by the user
' If not, just take the first Vehicle Year as the
' filter for the Vehicle Make List
strVehicleYear = Request.Form("cboVehicleYear&quot

If strVehicleYear = "" Then
If Not rsVehicleYear.EOF Then
strVehicleYear = rsVehicleYear("VehicleYear&quot

End If
End If

If strVehicleYear <> "" Then
strSQL = "SELECT DISTINCT * FROM [Vehicle Description] WHERE VehicleYear = '" & strVehicleYear & "'"
Set rsVehicleMake = objConnection.Execute(strSQL)
strVehicleMake = Request.Form("cboVehicleMake&quot

If strVehicleMake = "" Or Request.Form("hid_VehicleYear_Changed&quot

= "True" Then
If Not rsVehicleMake.EOF Then
strVehicleMake = rsVehicleMake("VehicleMake&quot

End If
End If
strSQL = "SELECT DISTINCT * FROM [Vehicle Description] WHERE VehicleMake='" & strVehicleMake & "'" & _
" AND VehicleYear = '" & strVehicleYear & "'"
Set rsVehicleModel = objConnection.Execute(strSQL)
End If
%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<CENTER>
<FORM NAME=frmHierarchy METHOD=post ACTION="Hierarchy.asp">
<INPUT TYPE=HIDDEN NAME=hid_VehicleYear_Changed>
<INPUT TYPE=HIDDEN NAME=hid_VehicleMake_Changed>
<P><H2>Hierarchial Selection of Items</H2></P>
<TABLE CELLSPACING=5 CELLPADDING=5 BORDER=0 ALIGN="CENTER">
<TR>
<TD>Vehicle Year: </TD>
<TD><SELECT id=cboVehicleYear name=cboVehicleYear onchange="ChangeVehicleYear()">
<%
' Add the Vehicle Year to the list
If Not rsVehicleYear.EOF Then
Do While Not rsVehicleYear.EOF
strVehicleYear = rsVehicleYear("VehicleYear&quot

If rsVehicleYear("VehicleYear&quot

= Request.Form("cboVehicleYear&quot

Then %>
<OPTION VALUE="<%=rsVehicleYear("VehicleYear&quot

%>" SELECTED> <%=strVehicleYear%></OPTION>
<%
Else
%>
<OPTION VALUE="<%=rsVehicleYear("VehicleYear&quot

%>" > <%=strVehicleYear%></OPTION>
<%
End If
rsVehicleYear.MoveNext
Loop
End If
'Reset the record pointer to the first record
rsVehicleYear.MoveFirst
%>
</SELECT></TD>
</TR>
<TR>
<TD>Vehicle Make: </TD>
<TD><SELECT id=cboVehicleMake name=cboVehicleMake onchange="ChangeVehicleMake()" >
<%
' Add the Vehicle Make to the list
If Not rsVehicleMake.EOF Then
Do While Not rsVehicleMake.EOF
strVehicleMake = rsVehicleMake("VehicleMake&quot

If rsVehicleMake("VehicleMake&quot

= Request.Form("cboVehicleMake&quot

Then
%>
<OPTION Value="<%=rsVehicleMake("VehicleMake&quot

%>" SELECTED> <%=strVehicleMake%></OPTION>
<%
Else
%>
<OPTION Value="<%=rsVehicleMake("VehicleMake&quot

%>" > <%=strVehicleMake%></OPTION>
<%
End If
rsVehicleMake.MoveNext
Loop
End If

'Reset the record pointer to the first record
rsVehicleMake.MoveFirst
%>
</SELECT></TD>
</TR>
<TR>
<TD>Vehicle Model: </TD>
<TD><SELECT id=cboVehicleModel name=cboVehicleModel>
<%
' Add the Vehicle Model to the list
If Not rsVehicleModel.EOF Then
Do While Not rsVehicleModel.EOF
strVehicleModel = rsVehicleModel("VehicleModel&quot

%>
<OPTION Value="<%=rsVehicleModel("VehicleModel&quot

%>" > <%=strVehicleModel%></OPTION>
<%
rsVehicleModel.MoveNext
Loop
End If
%>
</SELECT></TD>
</TR>
</TABLE>
<P><INPUT id=cmdSubmit name=cmdSubmit type=submit value=Submit></P>
<P> </P></FORM>
</FORM>
</CENTER>
</BODY>
<SCRIPT LANGUAGE="JavaScript">
function ChangeVehicleYear()
{
document.frmHierarchy.hid_VehicleYear_Changed.value = "True";
document.frmHierarchy.submit();
}

function ChangeVehicleMake()
{
document.frmHierarchy.hid_VehicleMake_Changed.value = "True";
document.frmHierarchy.submit();
}
</SCRIPT>
</HTML>
<%
' Release the connection and recordsets
Set objConnection = Nothing
Set rsVehicleYear = Nothing
Set rsVehicleMake = Nothing
Set rsVehicleModel = Nothing
End Sub
%>
Life is too short to waste...
Julius Pelejo
jcpelejo@hotmail.com

jcpelejo · Mar 14, 2002

Hello. I was able to link the combo boxes but the only thing that I have a problem with is removing all redundant records from the listings. Please advise. I have attached the code that I have.

<%@ Language=VBScript %>
<% Option Explicit %>
<%
Call Main()

Sub Main()
' If the form is submitted, just display the selected Vehicle Make and Vehicle Model
If Request.Form("cmdSubmit&quot