Paging Search Results from Access:

Code:

search.asp

<%option explicit%>

<%
dim conn, rs, strconn, strsql

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>

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

<table>
<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>

</font>

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

</body>
</html>

list.asp

Highlight

<%option explicit%>

<%
dim conn, rs, strconn, strsql
dim strQuery, strPageMenuCenter, strPageMenuLeft, strPageMenuRight, strMenu, i, x

set conn = server.createobject("adodb.connection")
set rs = server.createobject("adodb.recordset")

rs.CursorLocation = 3
rs.CursorType = 3
rs.PageSize = 5

conn.open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & server.mappath("test.mdb")
strsql = "select * from test where"

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

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

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

</head>
<body>

<%
if rs.EOF Then
	response.redirect "search.asp?Results=False"
else
%>

<b>Search Results:</b>

<%
	if rs.PageCount <> 1 then

strQuery = request.querystring
		strQuery = replace(strQuery,request.querystring("pg"),"")
		strQuery = replace(strQuery,"pg=&","")

for i = 1 to rs.PageCount
			if cint(request.querystring("pg")) = i then
				strPageMenuCenter = strPageMenuCenter & ("<b>" & i & "</b>")
			else
				if request.querystring("pg") = "" and i = 1 then
					strPageMenuCenter = strPageMenuCenter & ("<b>" & i & "</b>")
				else
					strPageMenuCenter = strPageMenuCenter & ("<a href=list.asp?pg=" & i & "&" & strQuery & ">" & i & "</a>")
				end if
			end if
			if i < rs.PageCount then
				strPageMenuCenter = strPageMenuCenter & " | "
			end if
		Next

if request.querystring("pg") = 1 or request.querystring("pg") = "" then
			x = 2
			strPageMenuLeft = ""
			strPageMenuRight = " | <a href=list.asp?pg=" & x & "&" & strQuery & ">next</a> >"
		else
			x = cint(request.querystring("pg")) + 1
			if x > rs.PageCount then
				x = x - 2
				strPageMenuLeft = "< <a href=list.asp?pg=" & x & "&" & strQuery &  ">back</a> | "
				strPageMenuRight = ""
			else	
				strPageMenuLeft = "< <a href=list.asp?pg=" & x - 2 & "&" & strQuery & ">back</a> | "
				strPageMenuRight = " | <a href=list.asp?pg=" & x & "&" & strQuery & ">next</a> >"
			end if
		end if
	end if

strMenu = strPageMenuLeft & strPageMenuCenter & strPageMenuRight

response.write "<p>" & strMenu & "<p>"
	response.write "<table border=1>"

x = 1

if request.queryString("pg") = "" then
		rs.AbsolutePage = 1
	else
		rs.AbsolutePage=cint(request.queryString("pg"))
	end if

do
		response.write "<tr>"
		response.write "<td class=font>" & rs.Fields("Name") & "</td>"
		response.write "<td class=font>" & rs.Fields("Division") & "</td>"
		response.write "</tr>"

rs.MoveNext
		x = x + 1
		if rs.EOF then exit do
	Loop Until x = rs.PageSize + 1

response.write "</table>"
	response.write "<p>" & strMenu
end if
%>

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

</font>

</body>
</html>