How to pass CFFORM variable into another query after I selecting a values from first drop down list in the same FORM?

For more details, pls have a look into the below code:

I have 3 drop down button and the corresponding query is given below :

Once I select Cob(1) and cUprFSACode (2) from the drop down button, The value of thired (cDBFN) values
is always which are available under cUprFSACode.

Pls see the line comments for where the problem is.




<!--- QUERY 1 --->

<CFQUERY NAME="SelCob" DATASOURCE="#CSybDataSource#" USERNAME=#CSybUsername# PASSWORD=#CSybPassword#>
     SELECT distinct convert(char,dBusinessDate,111) 'Cob'
     FROM     tblPastDate
     order by dBusinessDate desc
</CFQUERY>

<!--- QUERY 2 --->

<CFQUERY NAME="SelAcct" DATASOURCE="#CSybDataSource#" USERNAME=#CSybUsername# PASSWORD=#CSybPassword#>
SELECT      distinct cUprFSACode
FROM     tblFSAAcctNameJa
ORDER BY cUprFSACode
</CFQUERY>

<!--- QUERY 3 --->

<CFQUERY NAME="SelDBFN" DATASOURCE="#CSybDataSource#" USERNAME=#CSybUsername# PASSWORD=#CSybPassword#>
SELECT      distinct cDBFN
FROM     tblDBFN
where cDBFN != ' '
and cAcct_Aum = "#----------#"      ==> I need to specify the 'cUprFSACode' variable here.
                         PS : This variable should come after user select the
                                          second drop down botton.
order by cDBFN asc
</CFQUERY>


<HTML>
<HEAD>
     <TITLE>ABCD - Menu</TITLE>
     <SCRIPT LANGUAGE="JavaScript">

</SCRIPT>
</HEAD>

<CFFORM NAME="form" ACTION="ABCDList.cfm" TARGET="main">
<TABLE BORDER="0">
          <TR>
               <TD>&nbsp;
          </TR>
          <TR>
               <TD COLSPAN="1" ><font color="white"><B>ABCD</B></font></TD>
               <TD COLSPAN="1" ><font color="white"><B>FSACode</B></font></TD>
               <TD COLSPAN="1" ><font color="white"><B>DBFN</B></font></TD>
          </TR>
          <TR>
               <TD>
               <CFSELECT NAME="CobList"
                        QUERY="SelCob"
                        VALUE="Cob"
                        DISPLAY="Cob"
                        REQUIRED="yes"
                        SIZE="1">
               </CFSELECT>
               </TD>
               <TD>
               <CFSELECT NAME="AcctList"
                        QUERY="SelAcct"
                        VALUE="cUprFSACode"
                        DISPLAY="cUprFSACode"
                        REQUIRED="yes"
                        SIZE="1">
               </CFSELECT>
               </TD>
               <TD>
               <CFSELECT NAME="DBFNList"
                        QUERY="SelDBFN"
                        VALUE="cDBFN"
                        DISPLAY="cDBFN"
                        REQUIRED="yes"
                        SIZE="1">
               </CFSELECT>
               </TD>
               <TD>
                    <INPUT TYPE="submit" NAME="Go" Value="Go" ><font color="red"></font>
                    <INPUT TYPE="radio" NAME="ViewType" Value="Browser" CHECKED><font color="white">In Browser</font>
                    <INPUT TYPE="radio" NAME="ViewType" Value="Excel"><font color="white">Excel Download</font>
               </TD>
          </TR>
</TABLE>
</CFFORM>

</BODY>

</HTML>
LVL 1
cmurugavelAsked:
Who is Participating?
 
Cyril_HConnect With a Mentor Commented:
sorry i miss some code ;)

<!--- QUERY 1 --->

<CFQUERY NAME="SelCob" DATASOURCE="#CSybDataSource#" USERNAME=#CSybUsername# PASSWORD=#CSybPassword#>
   SELECT distinct convert(char,dBusinessDate,111) 'Cob'
   FROM     tblPastDate
   order by dBusinessDate desc
</CFQUERY>

<!--- QUERY 2 --->

<CFQUERY NAME="SelAcct" DATASOURCE="#CSybDataSource#" USERNAME=#CSybUsername# PASSWORD=#CSybPassword#>
SELECT      distinct cUprFSACode
FROM     tblFSAAcctNameJa
ORDER BY cUprFSACode
</CFQUERY>

<CFIF (IsDefined("Form.AcctList")) AND (Form.AcctList IS NOT "")>
<!--- QUERY 3 --->

<CFQUERY NAME="SelDBFN" DATASOURCE="#CSybDataSource#" USERNAME=#CSybUsername# PASSWORD=#CSybPassword#>
SELECT      distinct cDBFN
FROM     tblDBFN
where cDBFN != ' '
and cAcct_Aum = "#Form.AcctList#"    
order by cDBFN asc
</CFQUERY>

</CFIF>

<HTML>
<HEAD>
   <TITLE>ABCD - Menu</TITLE>
   <SCRIPT LANGUAGE="JavaScript">
   function ReloadForm() {
   form.action = "MyForm.cfm";
   form.submit();
   }
   </SCRIPT>
</HEAD>

<CFPARAM NAME="Form.CobList" DEFAULT="">
<CFPARAM NAME="Form.AcctList" DEFAULT="">
<CFPARAM NAME="Form.DBFNList" DEFAULT="">
<CFPARAM NAME="Form.ViewType" DEFAULT="">

<CFFORM NAME="form" ACTION="ABCDList.cfm" TARGET="main">
<TABLE BORDER="0">
        <TR>
             <TD>&nbsp;
        </TR>
        <TR>
             <TD COLSPAN="1" ><font color="white"><B>ABCD</B></font></TD>
             <TD COLSPAN="1" ><font color="white"><B>FSACode</B></font></TD>
             <TD COLSPAN="1" ><font color="white"><B>DBFN</B></font></TD>
        </TR>
        <TR>
             <TD>
             <CFSELECT NAME="CobList"
                      QUERY="SelCob"
                      VALUE="Cob"
                      DISPLAY="Cob"
                      REQUIRED="yes"
                      SIZE="1"
                      SELECTED="Form.CobList">
             </CFSELECT>
             </TD>
             <TD>
             <CFSELECT NAME="AcctList"
                      QUERY="SelAcct"
                      VALUE="cUprFSACode"
                      DISPLAY="cUprFSACode"
                      REQUIRED="yes"
                      SIZE="1"
                      SELECTED="Form.AcctList"
                           PASSTHROUGH="OnChange='ReloadForm();'">
             </CFSELECT>
             </TD>
             <TD>
             <CFIF (IsDefined("Form.AcctList")) AND (Form.AcctList IS NOT "")>
             <CFSELECT NAME="DBFNList"
                      QUERY="SelDBFN"
                      VALUE="cDBFN"
                      DISPLAY="cDBFN"
                      REQUIRED="yes"
                      SIZE="1"
                      SELECTED="Form.DBFNList">
             </CFSELECT>
             </CFIF>
             </TD>
             <TD>
                  <INPUT TYPE="submit" NAME="Go" Value="Go" ><font color="red"></font>
                  <INPUT TYPE="radio" NAME="ViewType" Value="Browser" <CFIF (Not IsDefined("Form.ViewType"))
OR (Form.ViewType IS "Browser")>CHECKED</CFIF>><font color="white">In
Browser</font>
                  <INPUT TYPE="radio" NAME="ViewType" Value="Excel" <CFIF (IsDefined("Form.ViewType"))
AND (Form.ViewType IS "Excel")>CHECKED</CFIF>><font color="white">Excel Download</font>
             </TD>
        </TR>
</TABLE>
</CFFORM>

</BODY>

</HTML>
0
 
Cyril_HCommented:
You have to reload the entire form (easiest way) or Create Javascript Arrays (if your query doesn't contains lot of values). Here's the code for the 1st solution :

<!--- QUERY 1 --->

<CFQUERY NAME="SelCob" DATASOURCE="#CSybDataSource#" USERNAME=#CSybUsername# PASSWORD=#CSybPassword#>
    SELECT distinct convert(char,dBusinessDate,111) 'Cob'
    FROM     tblPastDate
    order by dBusinessDate desc
</CFQUERY>

<!--- QUERY 2 --->

<CFQUERY NAME="SelAcct" DATASOURCE="#CSybDataSource#" USERNAME=#CSybUsername# PASSWORD=#CSybPassword#>
SELECT      distinct cUprFSACode
FROM     tblFSAAcctNameJa
ORDER BY cUprFSACode
</CFQUERY>

<CFIF (IsDefined("Form.AcctList")) AND (Form.AcctList IS NOT "")>
<!--- QUERY 3 --->

<CFQUERY NAME="SelDBFN" DATASOURCE="#CSybDataSource#" USERNAME=#CSybUsername# PASSWORD=#CSybPassword#>
SELECT      distinct cDBFN
FROM     tblDBFN
where cDBFN != ' '
and cAcct_Aum = "#Form.AcctList#"    
order by cDBFN asc
</CFQUERY>

</CFIF>

<HTML>
<HEAD>
    <TITLE>ABCD - Menu</TITLE>
    <SCRIPT LANGUAGE="JavaScript">

</SCRIPT>
</HEAD>

<CFPARAM NAME="Form.CobList" DEFAULT="">
<CFPARAM NAME="Form.AcctList" DEFAULT="">
<CFPARAM NAME="Form.DBFNList" DEFAULT="">
<CFPARAM NAME="Form.ViewType" DEFAULT="">

<CFFORM NAME="form" ACTION="ABCDList.cfm" TARGET="main">
<TABLE BORDER="0">
         <TR>
              <TD>&nbsp;
         </TR>
         <TR>
              <TD COLSPAN="1" ><font color="white"><B>ABCD</B></font></TD>
              <TD COLSPAN="1" ><font color="white"><B>FSACode</B></font></TD>
              <TD COLSPAN="1" ><font color="white"><B>DBFN</B></font></TD>
         </TR>
         <TR>
              <TD>
              <CFSELECT NAME="CobList"
                       QUERY="SelCob"
                       VALUE="Cob"
                       DISPLAY="Cob"
                       REQUIRED="yes"
                       SIZE="1"
                       SELECTED="Form.CobList">
              </CFSELECT>
              </TD>
              <TD>
              <CFSELECT NAME="AcctList"
                       QUERY="SelAcct"
                       VALUE="cUprFSACode"
                       DISPLAY="cUprFSACode"
                       REQUIRED="yes"
                       SIZE="1"
                       SELECTED="Form.AcctList">
              </CFSELECT>
              </TD>
              <TD>
              <CFIF (IsDefined("Form.AcctList")) AND (Form.AcctList IS NOT "")>
              <CFSELECT NAME="DBFNList"
                       QUERY="SelDBFN"
                       VALUE="cDBFN"
                       DISPLAY="cDBFN"
                       REQUIRED="yes"
                       SIZE="1"
                       SELECTED="Form.DBFNList">
              </CFSELECT>
              </CFIF>
              </TD>
              <TD>
                   <INPUT TYPE="submit" NAME="Go" Value="Go" ><font color="red"></font>
                   <INPUT TYPE="radio" NAME="ViewType" Value="Browser" <CFIF (Not IsDefined("Form.ViewType")) OR (Form.ViewType IS "Browser")>CHECKED</CFIF>><font color="white">In
Browser</font>
                   <INPUT TYPE="radio" NAME="ViewType" Value="Excel" <CFIF (IsDefined("Form.ViewType")) AND (Form.ViewType IS "Excel")>CHECKED</CFIF>><font color="white">Excel Download</font>
              </TD>
         </TR>
</TABLE>
</CFFORM>

</BODY>

</HTML>
0
 
cmurugavelAuthor Commented:
It didn't cover my entire requirement. But I have managed with your code and my additional code.

Thanks.
0
 
cmurugavelAuthor Commented:
It didn't cover my entire requirement. But I have managed with your code and my additional code.

Thanks.
0
 
cmurugavelAuthor Commented:
It didn't cover my entire requirement. But I have managed with your code and my additional code.

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.