Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
|
|||
|
|||
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.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Parameter queries in Excel 2007 | Excel Discussion (Misc queries) | |||
MS query--can it handle parameter queries from Access? | Excel Discussion (Misc queries) | |||
Queries on Pivot table | Excel Discussion (Misc queries) | |||
Where can I learn how to create parameter queries in Excel? | Excel Discussion (Misc queries) |