By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,474 Members | 3,244 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,474 IT Pros & Developers. It's quick & easy.

Updating a query automatically when changing combo box value

100+
P: 119
I have a query that selects data based on a particular date. This date is selected from a combo box on a form. The data from the query is then used to generate a report. This report can be filtered using other features on the form where the date is set.

I am able to modify the query using the date set on the form.

Expand|Select|Wrap|Line Numbers
  1. SELECT AssetPrices.Symbol, Max(AssetPrices.Date) AS MaxOfDate
  2. FROM AssetPrices, PricingDate
  3. WHERE (((AssetPrices.Date)<=[Forms]![frmReportFilter_2].[cboDate].[Value]))
  4. GROUP BY AssetPrices.Symbol;
However, the only way I know how to update the query is by switching the query from table view to design view, and back again. It does not refresh when I select 'refresh' from the Records menu.

Even if I am able to refresh the query by switching views as mentioned above, the changes do not follow through to the report. I have to close the report down and then reload it.

Can anyone suggest a method that will allow the query and report to update dynamically, based on the date selected in the combo box, rather than closing each object down and then reloading it?
Feb 21 '07 #1
Share this Question
Share on Google+
7 Replies


ADezii
Expert 5K+
P: 8,597
I have a query that selects data based on a particular date. This date is selected from a combo box on a form. The data from the query is then used to generate a report. This report can be filtered using other features on the form where the date is set.

I am able to modify the query using the date set on the form.

Expand|Select|Wrap|Line Numbers
  1. SELECT AssetPrices.Symbol, Max(AssetPrices.Date) AS MaxOfDate
  2. FROM AssetPrices, PricingDate
  3. WHERE (((AssetPrices.Date)<=[Forms]![frmReportFilter_2].[cboDate].[Value]))
  4. GROUP BY AssetPrices.Symbol;
However, the only way I know how to update the query is by switching the query from table view to design view, and back again. It does not refresh when I select 'refresh' from the Records menu.

Even if I am able to refresh the query by switching views as mentioned above, the changes do not follow through to the report. I have to close the report down and then reload it.

Can anyone suggest a method that will allow the query and report to update dynamically, based on the date selected in the combo box, rather than closing each object down and then reloading it?
Try re-opening the Report from the Form in the After Update Event() of cboDate with a where condition as in (assuming Report name is Report1):
Expand|Select|Wrap|Line Numbers
  1. DoCmd.Close acReport, "Report1", acSaveNo
  2. DoCmd.OpenReport "Report1", acViewPreview, , "[Date] = #" & Me![cboDate] & "#" 
Feb 22 '07 #2

100+
P: 119
Thanks...I have the report and queries updating using the method you suggested. It seems like a very forced solution though. Is there not a way to update a query without opening/closing each object?
Feb 22 '07 #3

Rabbit
Expert Mod 10K+
P: 12,315
I don't think there's a way, why does it matter if it's being done automatically through code anyways? It should only take all of 2 seconds to accomplish, probably even less.
Feb 22 '07 #4

100+
P: 119
Elegance...That's all. I'm just surprised that Access doesn't appear to update objects in a more direct way.
Feb 22 '07 #5

NeoPa
Expert Mod 15k+
P: 31,186
Elegance...That's all. I'm just surprised that Access doesn't appear to update objects in a more direct way.
Elegance should never be underestimated.
Elegant solutions have a habit of working more reliably than bodge-fixes.
Elegant code, reused elsewhere, is less likely to fall over due to its inherent consistency.
The Requery procedure can be used to do what you want but I confess to a little confusion as you refer separately to reports and queries. They are different objects (even though a report will often have a record source of a query).
I would also like to confirm my understanding of exactly what you're trying to achieve here - is it that you want a form and a report both to be active at the same time and that making a change to the selection on the form will update the results shown by the report?
Feb 26 '07 #6

100+
P: 119
Elegance should never be underestimated.
Elegant solutions have a habit of working more reliably than bodge-fixes.
Elegant code, reused elsewhere, is less likely to fall over due to its inherent consistency.
The Requery procedure can be used to do what you want but I confess to a little confusion as you refer separately to reports and queries. They are different objects (even though a report will often have a record source of a query).
I would also like to confirm my understanding of exactly what you're trying to achieve here - is it that you want a form and a report both to be active at the same time and that making a change to the selection on the form will update the results shown by the report?
Sorry for the delay...I have been distracted with other problems. To answer your question, yes, but the report doesn't necessarily have to be open at the same time. Rather, when the report is opened, it should have the required data in it. Here is another example.

I have a form "frmEnterTrade" that populates a table "tblTradeInfo". tblTradeInfo forms the source to part of a query, "qryTradeHistory". The qryTradeHistory query is the source to a report, "rptTradeSummary".

From the user's perspective, data is entered into frmEnterTrade, and that data summarised in the rptTradeSummary report.

However, for newly entered data in frmEnterTrade to appear in rptTradeSummary, the qryTradeHistory query must first be updated. The user obviously isn't concerned with the query, only the inputting of data and viewing the final report.

Access doesn't update a query automatically, hence I need to have code to update the query before opening the report. The only way I know how to do this is by opening and then closing the query. This works, but for more complicated table/query combinations this gets quite clunky, hence my wondering if there is a more elegant method. Especially given that this must be a reasonably common need.
Mar 15 '07 #7

NeoPa
Expert Mod 15k+
P: 31,186
Are you looking for the WhereCondition parameter to the DoCmd.OpenReport() function.
This will allow you to apply a dynamic filter to the underlying query without updating the query (or even the report) permanently.
Mar 15 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.