Multi-form database driven search/results page

[scheppsr77]

I have a problem with a multi-form search/result page.
I have a search box that populates a combo box, which works fine. But I'm trying to get the combo box to onchange submit back to itself and display info about the selected item in the combo box.

here is the code:

<%
' Constants ripped from adovbs.inc:
Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adCmdText = &H0001

' Our own constants:
Const PAGE_SIZE = 200 ' The size of our pages.

' Declare our variables... always good practice!
Dim strURL ' The URL of this page so the form will work
' no matter what this file is named.

Dim cnnSearch ' ADO connection
Dim rstSearch ' ADO recordset
Dim strDBPath ' path to our Access database (*.mdb) file

Dim strSQL ' The SQL Query we build on the fly
Dim strSearch ' The text being looked for

Dim iPageCurrent ' The page we're currently on
Dim iPageCount ' Number of pages of records
Dim iRecordCount ' Count of the records returned
Dim I ' Standard looping variable

' Retreive the URL of this page from Server Variables
strURL = Request.ServerVariables("URL")

' Retreive the term being searched for. I'm doing it on
' the QS since that allows people to bookmark results.
' You could just as easily have used the form collection.
strSearch = Request.QueryString("search")
strSearch = Replace(strSearch, "'", "''")

' Retrieve page to show or default to the first
If Request.QueryString("page") = "" Then
iPageCurrent = 1
Else
iPageCurrent = CInt(Request.QueryString("page"))
End If

' Since I'm doing this all in one page I need to see if anyone
' has searched for something. If they have we hit the DB.
' O/W I just show the search form and quit.
%>


<script language="javascript">
<!--
function dept_onchange(frmSelect) {
frmSelect.submit();
frmSelect1.submit();
}
//-->
</script>




<form name="frmSelect1" action="<%= strURL %>" method="get">
<input name="search" value="<%= strSearch %>" size="20" />
<input type="submit" />
</form>
<p>
[Since we've got a very small sample DB, try a single letter
search like 's' or 'd' for an example that actually pages!]
</p>
<%
If strSearch <> "" Then
' MapPath of virtual database file path to a physical path.
' If you want you could hard code a physical path here.
strDBPath = Server.MapPath("../fpdb/agent.mdb")


' Create an ADO Connection to connect to the sample database.
' We're using OLE DB but you could just as easily use ODBC or a DSN.
Set cnnSearch = Server.CreateObject("ADODB.Connection")

' This line is for the Access sample database:
cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"

' Build our query based on the input.
strSQL = "SELECT * " _
& "FROM agent_info " _
& "WHERE last LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR first LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "ORDER BY last;"

' Execute our query using the connection object. It automatically
' creates and returns a recordset which we store in our variable.
Set rstSearch = Server.CreateObject("ADODB.Recordset")
rstSearch.PageSize = PAGE_SIZE
rstSearch.CacheSize = PAGE_SIZE

' Open our recordset
rstSearch.Open strSQL, cnnSearch, adOpenStatic, adLockReadOnly, adCmdText

' Get a count of the number of records and pages
' for use in building the header and footer text.
iRecordCount = rstSearch.RecordCount
iPageCount = rstSearch.PageCount

If iRecordCount = 0 Then
' Display no records error.
%>
<p>
No Agents matched your search. Please try again.
</p>
<%
Else
' Move to the page we need to show.
rstSearch.AbsolutePage = iPageCurrent

' Show a quick status line letting people know where they are:
%>
<hr />
<p>
<% ' displays number of records that match %>
<%= iRecordCount %> Records Found.
</p>
<%
' Display a combo box of the data in the recordset. We loop through the
' recordset displaying the fields from the table and using MoveNext
' to increment to the next record. We stop when we reach EOF.
%>

<% ' here is where I started editing file %>
<form name="frmSelect" method="post" action="select.asp">
<SELECT NAME=name LANGUAGE=javascript onchange="return dept_onchange(frmSelect)" SIZE="19" style="background-color: #CEE2F8">
<%
Do While Not rstSearch.EOF And rstSearch.AbsolutePage = iPageCurrent
%>

<%
if Request.Form("name") = rstSearch ("last") then
Response.Write "<OPTION VALUE = '" & rstSearch ("last") & ", " & rstSearch ("first") & "' SELECTED>"
Response.Write oRs("last") & ", " & oRs("first") & "</Option>"
oRs.MoveNext
else
Response.Write "<OPTION VALUE = '" & rstSearch ("last") & ", " & rstSearch ("first") & "'>"
Response.Write rstSearch ("last") & ", " & rstSearch ("first") & "</Option>"
rstSearch.MoveNext
end if
loop
%>
</SELECT>
<% ' here is where I stopped editing file %>

<p>

</p>
<p><p>The following was selected : <%=Request.Form ("name")%> </p>

<%
End If

' Close our recordset and connection and dispose of the objects
rstSearch.Close
Set rstSearch = Nothing
cnnSearch.Close
Set cnnSearch = Nothing
End If
%>