Updating a query automatically when changing combo box value

[billelev]

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.

SELECT AssetPrices.Symbol, Max(AssetPrices.Date) AS MaxOfDate
FROM AssetPrices, PricingDate
WHERE (((AssetPrices.Date)<=[Forms]![frmReportFilter_2].[cboDate].[Value]))
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?

[ADezii]

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.

SELECT AssetPrices.Symbol, Max(AssetPrices.Date) AS MaxOfDate
FROM AssetPrices, PricingDate
WHERE (((AssetPrices.Date)<=[Forms]![frmReportFilter_2].[cboDate].[Value]))
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):

DoCmd.Close acReport, "Report1", acSaveNo
DoCmd.OpenReport "Report1", acViewPreview, , "[Date] = #" & Me![cboDate] & "#" 

[billelev]

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?

[Rabbit]

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.

[billelev]

Elegance...That's all. I'm just surprised that Access doesn't appear to update objects in a more direct way.

[NeoPa]

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?

[billelev]

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.

[NeoPa]

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.