VBA Access Requerying a Query that is outside of the current Form Object

Question

I am working on a complicated project in MS Access 2007. I am having some difficulty finding the correct method/Syntax for having a query outside of the open form be requeried. I am still fairly new at the VBA so forgive me if I make mistake or I am incorrect.

I have created a query which uses the value of a particular Combo Box in my form as part of its WHERE criteria. I have tested that and it works just fine. Now I am working on an "After Update" event for the Combo Box in question so that When I change the value of that Combo Box in question it will automatically tell my query to rerun itself with the new value in the WHERE Clause.

I was originally thinking of using the following command in VBA

    DoCmd.Requery [Queries]![TestQuery]

But I am unclear on if I can use the DoCmd.Requery since the query is outside of the open form and not imbedded into it.

I am looking for options on how best to accomplish this effect, Not Strictly VBA Only. So if a Macro would work better please give me an example to work from

UPDATE Just to make things a little clearer Here is the actual SQL Code for the Select Query that I want to requery through this after Update event.

    SELECT ForcastTrans.Location, ForcastTrans.Client, ForcastTrans.Department, ForcastTrans.Account, ForcastTrans.Currency, ForcastTrans.Month1, ForcastTrans.Month2, ForcastTrans.Month3, ForcastTrans.Month4, ForcastTrans.Month5, ForcastTrans.Month6, ForcastTrans.Month7, ForcastTrans.Month8, ForcastTrans.Month9, ForcastTrans.Month10, ForcastTrans.Month11, ForcastTrans.Month12
    FROM ForcastTrans
    WHERE (((ForcastTrans.EntityID)=[Forms]![ReportSelect]![BusinessUnit]));

As I said before this Query works just fine by itself I just need to be able to issue an after update event which will tell this query to Rerun based on the updated WHERE criteria.

Answer 1

Danke.

It still matters how you're building the report. I would assume that this query is the record source for the report and that the report is only generated when you request it from this very form you're updating. In which case, the query should automatically take the updated value when you load the report; If you're looking to generate the report after you close the form, then the query won't work once the combobox is destroyed. I'm still speculating on what exactly you want to do here, but suffice it to say, I don't recommend having a stored query that depends on an object in a form.

Answer 2

A cleaner way of doing this is to use a WhereCondition in your OpenReport call:

(inside a button click on ReportSelect)

DoCmd.OpenReport "YourReportName", acViewPreview,,"EntityID=" & Me.BusinessUnit

This opens your report filtered by the form that opens it, but still allows the report to open showing all of the data when the form is closed.

Kevin