Table queries not updating when parameter changes
 

Using Excel 2007.

I have a table with a database query which contains parameters.
The SQL for the query looks like:
{Call p_GetData(?, ?) }
and the parameter is named.

Let's say that this is on a sheet called QuerySheet.

Let's say there is another sheet called DataSheet. It has a cell on it which
is named "ImportantData"

When I right click on the table on the sheet, and bring up the Parameters
form, I can see that both parameters are marked "Refresh automatically when
cell value changes"

The cell that the parameter is tied to is calculated on another sheet, but
I have a cell on the sheet that contains the query, which duplicates the
value, i.e.
QuerySheet!$A$1 is set to =ImportantData, and Parameter1 for the query is
set to QuerySheet!$A$1

When I change the value on the DataSheet, and switch back to QuerySheet I
see that QuerySheet!A1 has been updated, but the query has not been re-run.

I notice that there was a SP for Office 2000 that fixed a problem that
seemed related, but I don't see anything regarding the problem in Excel 2007.

Table queries not updating when parameter changes
 

Is the problem resolved if you change the cell that the parameter is in to a
value instead of a formula? That way the change is linked to the actual cell
that is changing and not to the result of a formula that changes as the
result of a change elsewhere...
--
HTH...

Jim Thomlinson


"Benjamin Peikes" wrote:

Using Excel 2007.

I have a table with a database query which contains parameters.
The SQL for the query looks like:
{Call p_GetData(?, ?) }
and the parameter is named.

Let's say that this is on a sheet called QuerySheet.

Let's say there is another sheet called DataSheet. It has a cell on it which
is named "ImportantData"

When I right click on the table on the sheet, and bring up the Parameters
form, I can see that both parameters are marked "Refresh automatically when
cell value changes"

The cell that the parameter is tied to is calculated on another sheet, but
I have a cell on the sheet that contains the query, which duplicates the
value, i.e.
QuerySheet!$A$1 is set to =ImportantData, and Parameter1 for the query is
set to QuerySheet!$A$1

When I change the value on the DataSheet, and switch back to QuerySheet I
see that QuerySheet!A1 has been updated, but the query has not been re-run.

I notice that there was a SP for Office 2000 that fixed a problem that
seemed related, but I don't see anything regarding the problem in Excel 2007.

Table queries not updating when parameter changes
 

I'm not sure if I understand what you are asking me to do. I originally had
the parameter tied directly to the cell that had the calculation in it and it
wasn't updating. I thought it might have been a problem related to the cell
being on a different sheet, and that's why I put a "copy" of the data on the
sheet where the query is being run, but that did not fix the issue.

"Jim Thomlinson" wrote:

Is the problem resolved if you change the cell that the parameter is in to a
value instead of a formula? That way the change is linked to the actual cell
that is changing and not to the result of a formula that changes as the
result of a change elsewhere...
--
HTH...

Jim Thomlinson


"Benjamin Peikes" wrote:

Using Excel 2007.

I have a table with a database query which contains parameters.
The SQL for the query looks like:
{Call p_GetData(?, ?) }
and the parameter is named.

Let's say that this is on a sheet called QuerySheet.

Let's say there is another sheet called DataSheet. It has a cell on it which
is named "ImportantData"

When I right click on the table on the sheet, and bring up the Parameters
form, I can see that both parameters are marked "Refresh automatically when
cell value changes"

The cell that the parameter is tied to is calculated on another sheet, but
I have a cell on the sheet that contains the query, which duplicates the
value, i.e.
QuerySheet!$A$1 is set to =ImportantData, and Parameter1 for the query is
set to QuerySheet!$A$1

When I change the value on the DataSheet, and switch back to QuerySheet I
see that QuerySheet!A1 has been updated, but the query has not been re-run.

I notice that there was a SP for Office 2000 that fixed a problem that
seemed related, but I don't see anything regarding the problem in Excel 2007.

Table queries not updating when parameter changes
 

Additionally, I added some VBA code for the sheet which has the query on it
and added code to handle the Worksheet_Change event, and it does not get
fired when clearly there is a change in certain cells on the worksheet.

"Jim Thomlinson" wrote:

Is the problem resolved if you change the cell that the parameter is in to a
value instead of a formula? That way the change is linked to the actual cell
that is changing and not to the result of a formula that changes as the
result of a change elsewhere...
--
HTH...

Jim Thomlinson


"Benjamin Peikes" wrote:

Using Excel 2007.

I have a table with a database query which contains parameters.
The SQL for the query looks like:
{Call p_GetData(?, ?) }
and the parameter is named.

Let's say that this is on a sheet called QuerySheet.

Let's say there is another sheet called DataSheet. It has a cell on it which
is named "ImportantData"

When I right click on the table on the sheet, and bring up the Parameters
form, I can see that both parameters are marked "Refresh automatically when
cell value changes"

The cell that the parameter is tied to is calculated on another sheet, but
I have a cell on the sheet that contains the query, which duplicates the
value, i.e.
QuerySheet!$A$1 is set to =ImportantData, and Parameter1 for the query is
set to QuerySheet!$A$1

When I change the value on the DataSheet, and switch back to QuerySheet I
see that QuerySheet!A1 has been updated, but the query has not been re-run.

I notice that there was a SP for Office 2000 that fixed a problem that
seemed related, but I don't see anything regarding the problem in Excel 2007.

I noticed this issue seams to manifest itself when formulas contain volatile functions such as Today() or Now() etc. If you are using formulae in your parameter cells, I would build them up one step at a time until you find the culprit function.