BabbleFish
06-19-2007, 05:00 PM
I’m attempting to create an online form that allows users to update records in a MS ACCESS 2000 database. I have no problem showing a list of records available for update and generating the html form with information from the database (i.e. date recorded entered, record type, etc.) My problem is that I can’t figure out how to create dependent drop down lists. I want the options in the drop down list to be populated by another table in the same database but the options listed are dependent upon a field in the form that is autopopulate by the record to be updated.
Example:
User selects record A to update. Portions of record A populate in Form1. The are additional (not populated from record A that need to be added to record A) fields in form1 that require user input. I want these fields to be drive by options in table B in the database. The list of options that populate are also dependent on today’s date as well as another field that populates from record A.
I can do this in an ACCESS Form but finding it difficult to converting it to a web solution.
Any help you can provide would be greatly appreciated .
Code:
<%@ Language="VBScript" %>
<% Option Explicit %>
<html>
<head><title>Record Updates</title>
</head>
<body>
<%
'variable
Dim SQL, connection, recordset
Dim sConnString, ID
'ID passed from record selection screen
ID=request.querystring("ID")
SQL = "SELECT * FROM _RCAT_DATA WHERE ID=" & ID
Set Connection=Server.CreateObject("ADODB.Connection")
Set Recordset=Server.CreateObject("ADODB.Recordset")
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("NEW R-CAT.mdb")
Connection.Open(sConnString)
recordset.Open SQL,connection
%>
<div align='center'>
<table width="500" border="0" cellspacing="0" cellpadding="0" align="center">
<tr align="left" valign="top">
<td>
<div align="center">
<p>RCAT Audit Form</p>
<!-- start the html form -->
<form method="post" action="update_record.asp" name="form">
<input type="hidden" name="id" value="<%= recordset("id")%>">
<table width="100%" border="0">
<tr>
<td width="45%">Observer:
</td>
<td colspan="2">
<input type="text" name="Observer" id="req1" value="<%= recordset("Observer")%>" size='30'>
</td>
<td>
Date:
</td>
<td>
<input type="text" name="audit_date" id="req1" value="<%= recordset("audit_date")%>">
</td>
<td width="46">
SessionID:
</td>
<td width="148">
<input type="text" name="SESSION_ID" id="req1" value="<%= recordset("SESSION_ID")%>">
</td>
</tr>
</table>
<table width="100%">
<tr>
<td colspan="5">Call Category - Type - Reason</td>
<td> </td>
<td> </td>
<td width="148">RCAT</td>
</tr>
<tr>
<td colspan="5">
<select name="RCAT_code" id="req1">
<option>Account Info-Change-Feature/Service</option>
</select>
</td>
<td> </td>
<td> </td>
<td width="148">
<input type="text" name="Ob_Type" id="req1" value="<%= recordset("Ob_Type")%>">
</td>
</tr>
</table>
<tr>
<td width="45%">Story Date:
</td>
<td colspan="2">
</td>
</tr>
<td colspan="3">
<div align="center">
<input type="submit" name="Submit" value="Submit">
</div>
</td>
</tr>
<tr>
<td colspan="3"> </td>
</tr>
</table>
<br>
<br>
</form>
<!-- end the html form -->
</div>
</td>
</tr>
</table>
</div>
<%
'close the connection and recordset objects
recordset.Close
Set recordset=Nothing
connection.Close
Set connection=Nothing
%>
</body>
</html>
Example:
User selects record A to update. Portions of record A populate in Form1. The are additional (not populated from record A that need to be added to record A) fields in form1 that require user input. I want these fields to be drive by options in table B in the database. The list of options that populate are also dependent on today’s date as well as another field that populates from record A.
I can do this in an ACCESS Form but finding it difficult to converting it to a web solution.
Any help you can provide would be greatly appreciated .
Code:
<%@ Language="VBScript" %>
<% Option Explicit %>
<html>
<head><title>Record Updates</title>
</head>
<body>
<%
'variable
Dim SQL, connection, recordset
Dim sConnString, ID
'ID passed from record selection screen
ID=request.querystring("ID")
SQL = "SELECT * FROM _RCAT_DATA WHERE ID=" & ID
Set Connection=Server.CreateObject("ADODB.Connection")
Set Recordset=Server.CreateObject("ADODB.Recordset")
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("NEW R-CAT.mdb")
Connection.Open(sConnString)
recordset.Open SQL,connection
%>
<div align='center'>
<table width="500" border="0" cellspacing="0" cellpadding="0" align="center">
<tr align="left" valign="top">
<td>
<div align="center">
<p>RCAT Audit Form</p>
<!-- start the html form -->
<form method="post" action="update_record.asp" name="form">
<input type="hidden" name="id" value="<%= recordset("id")%>">
<table width="100%" border="0">
<tr>
<td width="45%">Observer:
</td>
<td colspan="2">
<input type="text" name="Observer" id="req1" value="<%= recordset("Observer")%>" size='30'>
</td>
<td>
Date:
</td>
<td>
<input type="text" name="audit_date" id="req1" value="<%= recordset("audit_date")%>">
</td>
<td width="46">
SessionID:
</td>
<td width="148">
<input type="text" name="SESSION_ID" id="req1" value="<%= recordset("SESSION_ID")%>">
</td>
</tr>
</table>
<table width="100%">
<tr>
<td colspan="5">Call Category - Type - Reason</td>
<td> </td>
<td> </td>
<td width="148">RCAT</td>
</tr>
<tr>
<td colspan="5">
<select name="RCAT_code" id="req1">
<option>Account Info-Change-Feature/Service</option>
</select>
</td>
<td> </td>
<td> </td>
<td width="148">
<input type="text" name="Ob_Type" id="req1" value="<%= recordset("Ob_Type")%>">
</td>
</tr>
</table>
<tr>
<td width="45%">Story Date:
</td>
<td colspan="2">
</td>
</tr>
<td colspan="3">
<div align="center">
<input type="submit" name="Submit" value="Submit">
</div>
</td>
</tr>
<tr>
<td colspan="3"> </td>
</tr>
</table>
<br>
<br>
</form>
<!-- end the html form -->
</div>
</td>
</tr>
</table>
</div>
<%
'close the connection and recordset objects
recordset.Close
Set recordset=Nothing
connection.Close
Set connection=Nothing
%>
</body>
</html>