I am trying to produce a web form with 3 seperate drop down menu's, all 3
are populated from different fields in an
access97 db, and the results of these are are used to produce a query based
on the results i.e SELECT * from table WHERE a is LIKE %a& AND b LIKE %B%
etc
the trouble is that I can only populate the drop downs once, an any changes
I make to the tables are reflected in the dropdowns, unless I reseet the
server.
I have got the code to work with 1 drop down, and have copied the code for
the other 2 ( and here lies the problem I'm sure !).
ANY HELP would be greatly appreciated as I am completely stuck, i think the
phrase is " can't see the wood for the trees !"

I have enclosed a copy of all the asp code for the page, if you get it to
work feel free to use it !

<%@ LANGUAGE="VBSCRIPT" %>
<%
Option Explicit

' -- Declare Variables
Dim strweek_151, strouc_151, strt2_151, intPass ' our variables for the
Week, OUC, Tier2, and PASS

strWeek_151 = Request("cboweek1")
strouc_151 = Request("cboouc1")
strt2_151 = Request("cbot21")
intPass = Request("PASS")

Select Case Trim(intPass)
Case Trim("1")
' -- Repeat Visit, display database data
DisplayDatabaseData
Case Else
' -- First Time Visit, display HTML Form
DisplayHTMLForm
End Select

Response.End

Sub DisplayHTMLForm()

' -- Get the list of states from the database.
Dim strweekList_151 ' String to hold the Week list
Dim strouclist_151 ' String for OUCs
Dim strt2list_151 ' String for t2

strweekList_151 = GetWeekListForComboBox_151 ()
strouclist_151 = getouclistforcomboBox_151 ()
strt2list_151 = gett2listforcombobox_151 ()

%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
<title>test</title>
</head>

<body>

<form ACTION="test.asp" METHOD="POST">
<input TYPE="hidden" NAME="PASS" VALUE="1">
<font FACE="Tahoma, Verdana, Arial, Helvetica, Sans Serif" SIZE="2"><b>Filter
Criteria:</b><br>
</font>

<table BORDER="0" CELLSPACING="2" CELLPADDING="2" WIDTH="80%" BGCOLOR="#EEEEEE">
<tr>
<td><font FACE="Tahoma, Verdana, Arial, Helvetica, Sans Serif" SIZE="2">
Week No:
</font></td>
<td><font FACE="Tahoma, Verdana, Arial, Helvetica, Sans Serif" SIZE="2">
<select NAME="cboweek1" SIZE="1">
<%= strweekList_151 %>
</select>
<tr>
<td><font FACE="Tahoma, Verdana, Arial, Helvetica, Sans Serif" SIZE="2">
OUC:
</font></td>
<td><font FACE="Tahoma, Verdana, Arial, Helvetica, Sans Serif" SIZE="2">
<select NAME="cboouc1" SIZE="1">
<%= stroucList_151 %>
</select>
<tr>
<td><font FACE="Tahoma, Verdana, Arial, Helvetica, Sans Serif" SIZE="2">
Tier 2:
</font></td>
<td><font FACE="Tahoma, Verdana, Arial, Helvetica, Sans Serif" SIZE="2">
<select NAME="cbot21" SIZE="1">
<%= strt2List_151 %>
</select>
</font></td>
</tr>
<tr>
<td><font FACE="Tahoma, Verdana, Arial, Helvetica, Sans Serif" SIZE="2">
&nbsp;
</font></td>
<td><font FACE="Tahoma, Verdana, Arial, Helvetica, Sans Serif" SIZE="2">
<input TYPE="submit" NAME="cmd" VALUE=" OK ">
</font></td>
</tr></table>

</form>


</body>
</html>
<%
End Sub

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

Sub DisplayDatabaseData()

' -- Declare Variables
Dim objConn ' Our Connection Object
Dim objRS ' Our Recordset Object
Dim strSQL ' Our SQL String to access the database
Dim strConnection ' Our Connection string to access the database
Dim i ' a counter variable
Dim strWhereweek_151 ' Where clause for Week
Dim strWhereouc_151 ' Where clause for OUC
Dim strWheret2_151 ' Where clause for t2
Dim strFilter ' Our Filter String for Display

' -- Create objects
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")

' -- Connection String Value
strConnection = "DSN=mydsn"

' -- Open the Connection
objConn.Open strConnection

' -- Our SQL Statement now needs to incorporate the Week the user
' -- asked for.
strFilter = "FILTER CRITERIA: "
If strWeek_151 = "0" Then strWeek_151 = ""
If strouc_151 = "0" Then strouc_151 = ""
If strt2_151 = "0" Then strt2_151 = ""

If Trim(strWeek_151) = "" and Trim(strouc_151) = "" and Trim(strt2_151)
= "" Then

strSQL = "SELECT * FROM ShortCalls151"
strFilter = strFilter & " NONE "
Else
strSQL = "SELECT * FROM ShortCalls151 "
strWhereweek_151 = ""
strWhereouc_151 = ""
strWheret2_151 = ""
' -- Check the Week
if Trim(strweek_151) <> "" Then
strWhereweek_151 = " Week = '" & strweek_151 & "' "
strFilter = strFilter & " Week = " & strweek_151
End if
' -- Check the OUC
If Trim(strouc_151) <> "" Then
' -- do we already have one where clause before this?
if strWhereweek_151 <> "" Then
strWhereouc_151 = " AND "
strFilter = strFilter & " and "
end if
strWhereouc_151 = strWhereouc_151 & _
" OUC LIKE '" & strouc_151 & "' "
strFilter = strFilter & " ouc is like: " & strouc_151

End if

If Trim(strt2_151) <> "" Then
' -- do we already have one where clause before this?
if strWhereweek_151 <> "" or strWhereouc_151 <> "" Then
strWheret2_151 = " AND "
strFilter = strFilter & " and "
end if
strWheret2_151 = strWheret2_151 & _
" TIER2 LIKE '" & strt2_151 & "' "
strFilter = strFilter & " Tier2 is like: " & strt2_151

End if


strSQL = strSQL & " WHERE " & strWhereweek_151 & strWhereouc_151 & strWheret2_151

End if

set objRS = objConn.Execute (strSQL)
if (objRS.BOF and objRS.EOF) then
response.write "No records found"
response.end
End if
'----------------------------------------------------------------------
' Begin HTML output
'----------------------------------------------------------------------
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
<title>Results Page for 151 Short Calls Query</title>
</head>

<body>

<br><br><font FACE="ARIAL" SIZE="3"><b><%= strFilter %></b></font>
<table BORDER="1" CELLPADDING="2" CELLSPACING="1" WIDTH="100%">
<%
' -- Output the Field Names as the first row in the table
Response.Write "<TR BGCOLOR=""#CCCCCC"">"
For i = 0 to objRS.Fields.Count - 1
Response.Write "<TH><FONT FACE=""ARIAL"" SIZE=""2"">" & objRS.Fields(i).Name
& "</FONT></TH>"
Next
Response.write "</TR>"
' -- Now output the contents of the Recordset
objRS.MoveFirst
Do While Not objRS.EOF
' -- output the contents
Response.Write "<TR>"
For i = 0 to objRS.Fields.Count - 1
Response.Write "<TD><FONT FACE=""ARIAL"" SIZE=""1"">" & objRS.Fields(i)
& "</FONT></TD>"
Next
Response.write "</TR>"
' -- move to the next record
objRS.MoveNext
Loop

objRS.Close
set objRS = Nothing
objConn.Close
set objConn = Nothing




%>
</table><br>


</body>
</html>
<%
'----------------------------------------------------------------------
' End HTML Output
'----------------------------------------------------------------------
End Sub

'----------------------------------------------------------------------
' Function : GetWeekListForComboBox
' Gets a list of Week Numbers from the database
'----------------------------------------------------------------------
Function GetWeekListForComboBox_151()
' -- Declare Variables
Dim objConn ' Our Connection Object
Dim objRS ' Our Recordset Object
Dim strSQL ' Our SQL String to access the database
Dim strConnection ' Our Connection string to access the database
Dim i ' a counter variable
Dim strResult_151 ' Our Function Return Value


' -- Do we have the value in our Application Object already?
strResult_151 = Application("StatesList")
If strResult_151 = "" Then
' -- No, so get it from the database

' -- Create objects
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")

' -- Connection String Value
strConnection = "DSN=Shortcalls151"

' -- Open the Connection
objConn.Open strConnection
strSQL = " SELECT DISTINCT Week FROM Shortcalls151 WHERE Week <> NULL"

' -- Populate our Recordset with data
set objRS = objConn.Execute (strSQL)

if (objRS.BOF and objRS.EOF) then
response.write "No Weeks Found. Problem with Application."
response.end
End if

' -- Start building the result string
strResult_151 = "<OPTION VALUE=""0"">Please Select a Week</OPTION>"
Do While Not objRS.EOF
strResult_151 = strResult_151 & _
"<OPTION VALUE=""" & objRS("Week") & """>" & objRS("Week") & "</OPTION>"
objRS.MoveNext
Loop
' -- Close objects
objRS.Close
set objRS = Nothing
objConn.Close
set objConn = Nothing

' -- Store the value in the Application object for next visit
Application.Lock
Application("StatesList") = strResult_151
Application.UnLock
End If

' -- return the value
GetweekListForComboBox_151 = strResult_151

End Function

'----------------------------------------------------------------------
' Function : GetoucListForComboBox
' Gets a list of states from the database
'----------------------------------------------------------------------
Function GetoucListForComboBox_151 ()
' -- Declare Variables
Dim objConn ' Our Connection Object
Dim objRS ' Our Recordset Object
Dim strSQL ' Our SQL String to access the database
Dim strConnection ' Our Connection string to access the database
Dim i ' a counter variable
Dim strResult_1511 ' Our Function Return Value


' -- Do we have the value in our Application Object already?
strResult_1511 = Application("oucList")
If strResult_1511 = "" Then
' -- No, so get it from the database

' -- Create objects
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")

' -- Connection String Value
strConnection = "DSN=Shortcalls151"

' -- Open the Connection
objConn.Open strConnection
strSQL = " SELECT DISTINCT ouc FROM Shortcalls151 WHERE ouc <> NULL"

' -- Populate our Recordset with data
set objRS = objConn.Execute (strSQL)

if (objRS.BOF and objRS.EOF) then
response.write "No OUC Found. Problem with Application."
response.end
End if

' -- Start building the result string
strResult_1511 = "<OPTION VALUE=""0"">Please Select an OUC</OPTION>"
Do While Not objRS.EOF
strResult_1511 = strResult_1511 & _
"<OPTION VALUE=""" & objRS("ouc") & """>" & objRS("ouc") & "</OPTION>"
objRS.MoveNext
Loop
' -- Close objects
objRS.Close
set objRS = Nothing
objConn.Close
set objConn = Nothing

' -- Store the value in the Application object for next visit
Application.Lock
Application("oucList") = strResult_1511
Application.UnLock
End If

' -- return the value
GetoucListForComboBox_151 = strResult_1511

End Function

'----------------------------------------------------------------------
' Function : Gett2ListForComboBox
' Gets a list of states from the database
'----------------------------------------------------------------------
Function Gett2ListForComboBox_151 ()
' -- Declare Variables
Dim objConn ' Our Connection Object
Dim objRS ' Our Recordset Object
Dim strSQL ' Our SQL String to access the database
Dim strConnection ' Our Connection string to access the database
Dim i ' a counter variable
Dim strResult_1512 ' Our Function Return Value


' -- Do we have the value in our Application Object already?
strResult_1512 = Application("t2List")
If strResult_1512 = "" Then
' -- No, so get it from the database

' -- Create objects
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")

' -- Connection String Value
strConnection = "DSN=Shortcalls151"

' -- Open the Connection
objConn.Open strConnection
strSQL = " SELECT DISTINCT tier2 FROM Shortcalls151 WHERE tier2 <> NULL"

' -- Populate our Recordset with data
set objRS = objConn.Execute (strSQL)

if (objRS.BOF and objRS.EOF) then
response.write "No Tier2s Found. Problem with Application."
response.end
End if

' -- Start building the result string
strResult_1512 = "<OPTION VALUE=""0"">Please Select a Tier2</OPTION>"
Do While Not objRS.EOF
strResult_1512 = strResult_1512 & _
"<OPTION VALUE=""" & objRS("tier2") & """>" & objRS("tier2") & "</OPTION>"
objRS.MoveNext
Loop
' -- Close objects
objRS.Close
set objRS = Nothing
objConn.Close
set objConn = Nothing

' -- Store the value in the Application object for next visit
Application.Lock
Application("t2List") = strResult_1512
Application.UnLock
End If

' -- return the value
Gett2ListForComboBox_151 = strResult_1512

End Function

%>

Paul