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.

What type of query is it? SELECT, MAKE TABLE, etc, etc? How you want to handle the query results is important in determining the best route to run the query. You might want to create a recordset if you need the results right then and there. You might just want to call the query if it's making a table or updating a table, etc, etc. — Bill N.
It is a SELECT Query, I am using it as a foundation for building a Report. It needs to be dynamic so that I do not need to come back and build new reports everytime they add some new category to the records in the database. — Mr. Finn
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. — Bill N.
That actually helps quite a bit. You are absolutely correct that this same form will have buttons to open the requested reports. I had not thought about when you initiate the Open Form command that it would force the queries to rerun in that instant. I am going to run some tests on that to ensure it works for my needs but I'd say that might solve my problem — Mr. Finn
Yeah That solved the issue. Throw an answer onto this so I can give you credit for helping me solve the problem — Mr. Finn

Bill N. Bill N. · Accepted Answer · 2013-12-30T23:30:26

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.

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

2 Answers