Populate 2 Select Lists in Search Form from Access and Display Search Results, Detail and Update Page w/Delete Ability:

Code:

search.asp

<%Dim conn,rs,strconn,DBQValue

set conn = server.createobject("adodb.connection")
set rs = server.createobject("adodb.recordset")
conn.open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & server.mappath("test.mdb")

strsql = "select * from test"
rs.open strsql, conn, 2, 2
%>

</head>
<body>

<table>

<%if request.querystring("Results") = "False" then%>
	<tr>
	<td colspan=2 class=font><font color=#ff0000><b>Sorry, there are no matching records.</b></font><br><br></td>
	</tr>
<%end if%>

<%if request.querystring ("Updated") = "True" then%>
	<tr>
	<td colspan=2 class=font><font color=#ff0000><b>The record has been updated.</b></font><br><br></td>
	</tr>
<%end if%>

<%if request.querystring ("Record") = "Deleted" then%>
	<tr>
	<td colspan=2 class=font><font color=#ff0000><b>The record has been deleted.</b></font><br><br></td>
	</tr>
<%end if%>

<tr>
<td class=font valign=middle><b>Name:</b> </td>
<td valign=middle>
<select name="Name">
<option value="">--Select a Name--</option>
<%If rs.BOF and rs.EOF Then%>
<%else%>
	<%Do While Not rs.EOF%> 
		<option value="<%=rs("Name")%>"><%=rs("Name")%></option>
	<%rs.movenext%>
	<%loop%>
<%end if%>
</select>
</td>
</tr>

<td class=font valign=middle><b>Division:</b> </td>
<td valign=middle>
<select name="Division">
<option value="">--Select a Division--</option>
<%If rs.BOF and rs.EOF Then%>
<%else%>
	<%rs.movefirst
	Do While Not rs.EOF%> 
		<option value="<%=rs("Division")%>"><%=rs("Division")%></option>
	<%rs.movenext%>
	<%loop%>
<%end if%>
</td>
</tr>

</form>

</body>
</html>

<%    
rs.close
conn.close 
set rs = nothing
set conn = nothing
%>

list.asp

Highlight

<%
Dim conn,rs,strconn,DBQValue
set conn = server.createobject("adodb.connection")
set rs = server.createobject("adodb.recordset")
conn.open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & server.mappath("test.mdb")
'on error resume next
strsql = "select * from test where"

If Request.Form("Name") <> "" Then
strsql = strsql & " Name = '" 		&_ 
Request.Form("Name") & "' and "
End If

If Request.Form("Division") <> "" Then
strsql = strsql & " Division = '" 	& _
Request.Form("Division") & "' and "
End If

strsql=left(strsql,(len(strsql) - 5))
'Response.Write (strsql)
rs.open strsql,conn, 2, 2
%>

</head>
<body>

<%If rs.BOF and rs.EOF Then
	response.redirect "search.asp?Results=False"
Else%>
	<%If Not rs.BOF Then%>

<table cellspacing=5 cellpadding=2 border=0>
		<tr>
		<td colspan=5 class=font><b>Search Results:</b><br><br></td>
		</tr>

<tr>
		<td class=font><b>Name: </b></td>
		<td class=font><b>Division: </b></td>
		<td> </td>
		</tr>

<%Do While Not rs.EOF%>

<tr>
		<td class=font><%=rs("Name")%></td>
		<td class=font><%=rs("Division")%></td>
		<td class=font><a href="details.asp?ID=<%=rs("ID")%>">Details...</a></td>
		</tr>

<% 
		rs.MoveNext
		Loop
		%>

</table>
	<%End If%>
<%End If%>

</body>
</html>

<%    
rs.close
conn.close 
set rs = nothing
set conn = nothing
%>

details.asp

Highlight

If Request.Querystring("ID") <> "" Then
strsql = strsql & " ID = " 		&_ 
Request.Querystring("ID")
End If

'Response.Write (strsql)
rs.open strsql,conn, 2, 2
%>

</head>
<body>

<%If Not rs.BOF Then%>

<%Do While Not rs.EOF%>

<tr>
	<td class=font><b>Division: </b> <%=rs("Division")%></td>
	</tr>
	<tr>
	<td class=font><b>Phone: </b> <%=rs("Phone")%></td>
	</tr>
	<tr>
	<td class=font colspan=2><b>E-Mail: </b> <a href="mailto:<%=rs("Email")%>"><%=rs("Email")%></a></td>
	</tr>
	<tr>
	<td class=font colspan=2><a href="update.asp?ID=<%=rs("ID")%>">Edit...</a></td>
	</tr>
	<% 
	rs.MoveNext
	Loop
	%>

</table>
<%End If%>

</body>
</html>

<%    
rs.close
conn.close 
set rs = nothing
set conn = nothing
%>

update.asp

Highlight

<%
dim conn, rs, strsql, strconn, strID

if request.querystring("UpdateID") <> "" then

strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("test.mdb")
	set conn = server.createobject("adodb.connection")
	conn.open strconn
	strID = Request.querystring("UpdateID")
	set rs = server.createobject("adodb.recordset")

strsql = "select * from test where ID =" & strID

rs.open strsql, conn, 2, 2

rs("Name") = request.form("Name") 
	rs("Title") = request.form("Title") 
	rs("Division") = request.form("Division") 
	rs("Phone") = request.form("Phone") 
	rs("Email") = request.form("Email")

rs.update

set rs = nothing
	set conn = nothing

response.redirect ("search.asp?Updated=True")

end if

if request.querystring("DeleteID") <> "" then

strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("test.mdb")
	set conn = server.createobject("adodb.connection")
	conn.open strconn
	set rs = server.createobject("adodb.recordset")

strsql = "delete " & _
	    " from test" & _
	    " where ID=" & request.querystring("DeleteID")
	conn.execute(strsql)

set rs = nothing
	set conn = nothing

response.redirect "search.asp?Record=Deleted"

else

strsql = strsql & " ID = " 		&_ 
	Request.Querystring("ID")

rs.open strsql, conn

end if
%>

</head>
<body>

<% 
rs.movefirst
Do While NOT rs.EOF 
%>

<table>

<tr>
<td valign=middle class=font><b>Title: </b></td>
<td><input type="text" size="30" name="Title" value="<%=rs("Title")%>"></td>
</tr>

<td valign=middle class=font><b>Division: </b></td>
<td><input type="text" size="30" name="Division" value="<%=rs("Division")%>"></td>
</tr>

<td valign=middle class=font><b>Phone: </b></td>
<td><input type="text" size="30" name="Phone" value="<%=rs("Phone")%>"></td>
</tr>

</table>

</td>
</tr>

</table>

</form>

<% 
rs.movenext
Loop 
%>

</body>
</html>