I'm using ADO to populate a "SELECT" form, which pulls a unique list of people from an Access DB. Once the user selects a name from the list I produce an HTML table from all the tblJobs records that have that name (see code below). This works very well, but now I want the user to be able to click on one of the table rows (select a specific job) and produce a new table of all the reports produced as a result of that job, posted to a new HTML page floating on top of the original. I assume this task hinges on setting an identifying variable based on the specific job, and then passing that variable to the new page which uses it to requery the DB to build the new table - but that's where I run up against my own ignorance. Can anyone help? Thanks in advance...!
Code:
<%
dim ConnString
dim Conn
dim rs
ConnString = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("GAS.mdb")
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open ConnString
Set rs = SERVER.CreateObject("ADODB.Recordset")
' first have to get the category records to fill the drop down....
' Set SQL statement
strSQL = "SELECT DISTINCT tblJobs.Owner FROM tblJobs ORDER BY tblJobs.Owner;"
' Open Recordset Object
rs.Open strSQL,conn, adOpenStatic
%>
<html>
<body bgcolor="black" text="gold" link="gold" vlink="gold">
<FORM name="formCategory">
<SELECT NAME="qryCategory" onChange="getFilter(this.form.qryCategory)">
<OPTION selected>Select A Job Owner:
<% Do While Not rs.EOF
Response.Write ("<OPTION value='" & rs("Owner") & "'>" & rs("Owner"))
rs.MoveNext
Loop
rs.Close
Set rs=Nothing
%>
</OPTION>
</SELECT><BR>
</FORM>
<SCRIPT language="JavaScript">
function getFilter(listitem){
var object = "";
var listValue = getListValue(listitem);
document.formCategory.submit(listValue);
}
function getListValue(list){
var listValue="";
if (list.selectedIndex != -1) {
listValue = list.options[list.selectedIndex].value;
}
return (listValue);
}
</SCRIPT>
<%
dim arrArray()
dim i
i=0
If Request.QueryString("qryCategory") = "" Then
Response.Write("Please select a Job Owner from the list.")
Else
' Build query
Set rs = SERVER.CreateObject("ADODB.Recordset")
' first have to get the category records to fill the drop down....
' Set SQL statement
'strSQL = "SELECT Uploads.Employee_Name, Uploads.File_Type, Uploads.Upload_File_Name, Uploads.Date_Submitted, "
'strSQL = strSQL & "Uploads.Comments, Uploads.Employee_Email, Uploads.Subject_Line, Upload_Category.Category_Name "
'strSQL = strSQL & "FROM Upload_Category INNER JOIN Uploads ON Upload_Category.Category_Id = Uploads.Category_Id "
'strSQL = strSQL & "WHERE (((Upload_Category.Category_Name) = '" & Request.QueryString("qryCategory") & "'))"
strSQL = "SELECT * FROM tblJobs WHERE (((tblJobs.Owner) = '" & Request.QueryString("qryCategory") & "')) ORDER BY tblJobs.job_id DESC"
' Open Recordset Object
rs.Open strSQL,conn,adOpenStatic
If rs.RecordCount = 0 Then
Response.Write("No records found for Job Owner: " & Request.QueryString("qryCategory"))
Else
Response.Write("<H3>Items Found for Job Owner: " & Request.QueryString("qryCategory") & "</H3>")
' Build a table here
Response.Write("<TABLE CELLSPACING=2 CELLPADDING=0 COLS=3 WIDTH=500 BGCOLOR=Black>")
Response.Write("<TR>")
Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=30% BGCOLOR=Blue><B>Name</B></TD>")
Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=40% BGCOLOR=Blue><B>Project</B></TD>")
Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=15% BGCOLOR=Blue><B>WBS</B></TD>")
Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=15% BGCOLOR=Blue><B>Job #</B></TD>")
Response.Write("</TR>")
Do While Not rs.EOF
'the anchor javascript opens a new window with various controllable parameters - very handy!
Response.Write("<TR></tr><a href=""javascript:void(0);"" onclick=""window.open('[URL unfurl="true"]http://scotts-pc/test.htm',[/URL] 'Window1', 'location=no, menubar=no, height=300, width=400, status=no, resizable=no, scrollbar ')"">")
Response.Write("<TD><u>" & rs("Owner") & "</u></TD>")
Response.Write("<TD><u>" & rs("Project") & "</u></TD>")
Response.Write("<TD><u>" & rs("WBS") & "</u></TD>")
Response.Write("<TD><u>" & rs("job_id") & "</u></TD>")
Response.Write("</a></TR>")
rs.MoveNext
Loop
rs.Close
Response.Write("</TABLE>")
End If
End If
Set rs=Nothing
conn.Close
Set conn=Nothing
%>
</body>
</html>![[ponytails2]](/data/assets/smilies/ponytails2.gif "[ponytails2] [ponytails2]")