Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Jun 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Creating dropdown from database

    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:
    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:&nbsp;
    		</td>
    		<td colspan="2"> 
    			<input type="text" name="Observer" id="req1" value="<%= recordset("Observer")%>" size='30'>
    		</td>
    		<td> 
    		Date:&nbsp;
    		</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>&nbsp;</td>
    		<td>&nbsp;</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>&nbsp;</td>
    		<td>&nbsp;</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: 
    &nbsp;&nbsp;&nbsp; 
    </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">&nbsp;</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>

  • #2
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,072
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Just loop through whichever database has the values

    I am only showing the <select> tag here you can get the rest

    Code:
    <select name="RCAT_code" id="req1">
    <% Do Until recordset.EOF %>
    	<option value="<%=recordset("")%>"><%=recordset("")%></option>
    <%recordset.MoveNext
    Loop
    %>
    </select>

  • #3
    New to the CF scene
    Join Date
    Jun 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Miranda - Thank you for your response!!

    The code your provided has helped me generate a drop down list based on data from the table referenced in my SQL statement.

    What I am trying to do is generate the drop down list from another table in the database and I'm not sure how to handle the ADO connection/SQL statement. I've tried stringing SQL statements together but it seems that Access does not appreciate this format and returns the following error.

    Microsoft JET Database Engine error '80040e14'
    Characters found after end of SQL statement.

    Additionally, is there a way to create dependant list that is dependent on information entered in an html form. I'm trying to convert the following SQL into something that will work in a website.

    SELECT RCAT_CALL_REASONS.RCAT_code, RCAT_CALL_REASONS.definition FROM RCAT_CALL_REASONS WHERE (((RCAT_CALL_REASONS.end_date)>FORMS.RCAT_FORM.OBSVTN_DATE) And ((RCAT_CALL_REASONS.start_date)<FORMS.RCAT_FORM.OBSVTN_DATE) And ((RCAT_CALL_REASONS.TechSupport_Specific)=IIf(FORMS.RCAT_FORM.LOB="Technical Services",-1,0) Or (RCAT_CALL_REASONS.TechSupport_Specific)=IIf(FORMS.RCAT_FORM.LOB="Technical Services",0,0))) ORDER BY RCAT_CALL_REASONS.RCAT_code;

    Thanks again for your help!!!

  • #4
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,072
    Thanks
    4
    Thanked 8 Times in 8 Posts
    The easiest way for a beginner is to use a 2nd recordset object. If this table is in the same database you can use the same connection.
    So Do something like this


    Code:
    <select name="RCAT_code" id="req1">
    <% 
    Dim oRs
    Set oRs = Server.CreateObject("ADODB.Recordset")
    SQL = "Put The Query Used To Fill The Drop Down Here" 
    oRs.Open SQL, Connection
    Do Until oRs.EOF %>
    	<option value="<%=oRs("")%>"><%=oRs("")%></option>
    <%oRs.MoveNext
    Loop
    'Always do the Garbage Handling as ASP is poor at it
    oRs.Close 
    Set oRS = Nothing
    %>
    </select>


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •