All,

I have ColdFusion page that contains a Company dropdown, a To Date and From
Date and submit button

The user can select an ?individual company? or ?All?. If the user selects an
individual company, the ColdFusion calls a stored proc in that company database
and display the returned values. (I can get this process to work fine).
However, when the user selects ?All?, I need to have the page loop through each
company and call the stored proc in each company database and display all the
results from each stored proc.

(I?m not able to get this to work. The page calls the stored proc in the
first company and just stops.)

This is the main area, where I?m trying to do the loop:

<!-- Query that populates the Company dropdown -->
<cfquery name="qry_companies" datasource="core">
SELECT company_name, company_id, db_name
FROM pltmaster..smcomp
ORDER by company_name
</cfquery>

<cfif isDefined("submit")>
<cfquery name = "qry_glentry" datasource="core">
<cfif isDefined("submit")>
<cfloop query = "qry_company_loop">
<cfif #selectCompany# eq #qry_companies.company_id# or #selectCompany# eq
0>

{call #qry_companies.db_name#..mw_GLCode_sp('#proc_from_ date#',
'#proc_to_date#', '#qry_companies.company_id#') }

</cfif>
</cfloop>
</cfif>
</cfquery>
</cfif>


Here is the full code listing of the page. (note: I?ve removed the inlined
javascript for the data validation)

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<!-- Secure this page --->
<< Company Related stuff removed >>

<!-- Define Variables -->
<cfparam name="p_co_id" default ="0">
<cfparam name="proc_from_date" default = "">
<cfparam name="proc_to_date" default = "">

<cfoutput>

<cfif IsDefined("submit")>
<cfset p_co_id = #selectCompany#>
<cfset proc_from_date = #ProcFromDate#>
<cfset proc_to_date = #ProcToDate#>

<cfelse>
<cfset p_date = #CreateDate(year(now()), month(now()), day(now()))#>
<cfset proc_from_date = #DateFormat("#p_date#", "mm/dd/yyyy")#>
<cfset p_date = #CreateDate(year(now()), month(now()), day(now()))#>
<cfset proc_to_date = #DateFormat("#p_date#", "mm/dd/yyyy")#>
</cfif>

<!-- Query that populates the Company dropdown -->
<cfquery name="qry_companies" datasource="core">
SELECT company_name, company_id, db_name
FROM pltmaster..smcomp
ORDER by company_name
</cfquery>

<cfif isDefined("submit")>
<cfquery name = "qry_glentry" datasource="core">
<cfif isDefined("submit")>
<cfloop query = "qry_company_loop">
<cfif #selectCompany# eq #qry_companies.company_id# or #selectCompany# eq
0>

{call #qry_companies.db_name#..mw_GLCode_sp('#proc_from_ date#',
'#proc_to_date#', '#qry_companies.company_id#') }

</cfif>
</cfloop>
</cfif>
</cfquery>
</cfif>

<head>

</head>
<!-- Create HTML Page -->
<table border="0" width="525">
<tr>
<td><h3><strong>GL Codes Report</strong></h3></td>
</tr>

<tr>
<!-- Create the Table Headers -->
<td><strong>Company:</strong></td>
<td><strong>From Date:</strong></td>
<td><strong>To Date:</strong></td>
</tr>

<!-- Create Form -->
<form name="formMain" action=#ThisPage# method="POST" onsubmit="return
ValidateForm()">
<tr>
<!-- Company -->
<td valign="middle">
<select name="selectCompany"><option value="0">All
<cfloop query="qry_companies">
<cfif #p_co_id# eq #company_id#>
<option selected value = #qry_companies.company_id#>#company_name#
<cfelse>
<option value=#qry_companies.company_id#>#company_name#
</cfif>
</cfloop>
</select>
</td>

<!-- From Date -->
<td>
<input type=text name="ProcFromDate" value="#proc_from_date#" size=10
required="yes">
<!-- <input type="hidden" name="ProcFromDate_required" value="You must enter
a from date."> -->
<a href="javascript:show_calendar('formMain.ProcFromD ate');"
onmouseover="window.status='Date Picker';return true;"
onmouseout="window.status='';return true;"><img
src="/markwest/images/show-calendar.gif" width=24 height=22 border=0
align="top"></a>
</td>

<!-- To Date -->
<td>
<input type = text name="ProcToDate" value="#proc_to_date#" size=10
required="yes">
<!-- <input type="hidden" name="ProcToDate_required" value="You must enter a
to date."> -->
<a href="javascript:show_calendar('formMain.ProcToDat e');"
onmouseover="window.status='Date Picker';return true;"
onmouseout="window.status='';return true;"><img
src="/markwest/images/show-calendar.gif" width=24 height=22 border=0
align="top"></a>
</td>
</tr>

<tr>
<!-- Submit Button -->
<td colspan="3">
<input type="Submit" name="submit" value="Submit">
</td>
</tr>
</form>
<!-- End Form -->
</table>

<!-- Display Data -->
<cfif isDefined("submit")>
<cfif #qry_glentry.recordCount# eq "0">
<h3>No records found matching your criteria.</h3>
<cfabort>
</cfif>

<table border = "0">
<tr><td height = "15"></td></tr>

<!-- Data Headers -->
<tr>
<td nowrap style="font-size:8pt"><strong>Company</strong></td>
<td nowrap style="font-size:8pt"><strong>Date</strong></td>
<td nowrap style="font-size:8pt"><strong>Account Code</strong></td>
<td nowrap style="font-size:8pt"><strong>Account Description</strong></td>
</tr>

<!-- Loop through the Data -->
<cfloop query = "qry_glentry">
<tr>
<td nowrap style="font-size:8pt">#company_name#</td>
<td nowrap style="font-size:8pt">#date_applied#</td>
<td nowrap style="font-size:8pt">#account_code#</td>
<td nowrap style="font-size:8pt">#account_desc#</td>
</tr>
</cfloop>
</table>
</cfif>
</cfoutput>