asked on

cfselect onchange or onselect to populate a field via Access dbase query

I'm creating a form that has a dropdown of items to order and I need the price associated with this item to populate the price field. The dropdown is a cfselect populated with options from an Access database (the prices are here too). I want the onchange or onselect to query the same database and populate the price field with the price associated with the hardware selected. There are four dropdowns on the same page that need the same functionality.

Here a portion of the code that I have so far; I removed my poor attempts at adding in the onchange or onselect. I think that these events need to hit a second page with the query, passing through the hardware selected as the condition in the query and pass that price back through to the main page (or I'm just babbling).

<html>
<head>
<cfquery name="hardware" datasource="mydatabase">
SELECT *
FROM options
WHERE type IN ('hardware', 'none')
ORDER BY description
</cfquery>
</head>

<body>
<cfform action="form.cfm" method="post" name="proposal" id="proposal">
<table>
<tr>
<td><cfselect name="hardware" size="1" query="hardware" display="description" value="part_number" selected="none"></cfselect></td>
<td><input type="text" name="hardware_qty" size="3" value="1"></td>
<td><input type="hidden" name="hardware_price" value="#hardware_price#"><font face="arial" size="2"><b> $#hardware_price#</b></font></td>
</tr>
</table>
</cfform>
</body>
</html>

Thank you in advance for any help provided,
Brian

SOLUTION

mrichmon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

trailblazzyr55

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

trailblazzyr55

you can also use the custom tag on macromedia as a cf_tag or cfmodule.. depends on the use :o)

chignoli

ASKER

mrichmon - I think that I almost have your background lookup working, but it is getting caught on passing the text portion of the variable back through to the function on the main page. My code is below:

I used your iFrameRPC.js code...

------Main Page------
<html>
<head>
<script type="text/javascript" language="JavaScript" src="iframeRPC.js"></script>
<script type="text/javascript" language="JavaScript">
function LookupHardware()
{
var hardware = eval("document.proposal.hardware").value;

if (hardware != "none")
{ // If a Hardware was entered then look it up in our DB
callToServer("HardwareLookup.cfm?hardware=" + escape(hardware))
}
}
function HardwareLookupComplete(hardware, Found, hardware_price)
{
if(Found)
{ // Hardware in DB so fill in data
eval("document.proposal.hardware_price").value = hardware_price
}
return;
}
</script>
</head>

<body>
<cfquery name="hardware" datasource="MyDatabase">
SELECT *
FROM options
WHERE type IN ('hardware', 'none')
ORDER BY description
</cfquery>

<cfform action="verify.cfm" method="post" name="proposal" id="proposal">
<table>
<tr>
<td><cfselect name="hardware" size="1" query="hardware" display="description" value="part_number" selected="none" onchange="LookupHardware()"></cfselect></td>
<td><input type="text" name="hardware_qty" size="3" value="1"></td>
<td><input type="text" name="hardware_price" id="hardware_price" size="10" disabled></td>
</tr>
</table>
</body>
</html>

------Lookup Page------
<cfquery datasource="MyDatabase" name="LookupHardware">
SELECT *
FROM options
WHERE part_number = <cfqueryparam cfsqltype="cf_sql_varchar" value="#hardware#">
</cfquery>

<cfif LookupHardware.RecordCount EQ 0>
<cfset Found = false>
<cfset hardware_price = "">
<cfelse>
<cfset Found = true>
<cfset hardware_price = "LookupHardware.price">
</cfif>

<html>
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<cfoutput>
<body onLoad="window.parent.HardwareLookupComplete(#URL.hardware#, #Found#, '#hardware_price#');">
</body>
</cfoutput>
</html>

The script error is:
Line: 16
Char: 1
Error: 'M197' is undefined
Code: 0
URL: http://www.xxx.com/form/HardwareLookup.cfm?hardware=M197-570-14-US1

trail - Thanks for the TwoSelectsRelated.cfm link, that will help me on a different site :)

Thank you for your help,
Brian

ASKER CERTIFIED SOLUTION

mrichmon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chignoli

ASKER

Thank you for your help and the code!