Same query, multiple forms

[kstuart]

Good morning,

I have a query in my database that can be accessed from multiple forms. I am using the date and time picker to feed the date range to the query. The date and time picker is on the form and the form must be open for this to work.

On the criteria line of my query I point to the form and object name to find the date as follows:

Between [Forms]![MYFORM]![StartDate] And [Forms]![MYFORM]![EndDate]

My question is this. Since I want to be able to run this query from several forms, does this mean I have to create a new version of the query for each form I want to run from?

Thanks in advance,

Ken

 

[CJBIRKIN]

Hello
The easiest answer is to use a sub form in the main form. The three "main form"s would have the same sub form attached, the sub form would contain the date selection object which would be the one you have referenced in the query. This would mean you can keep the same (sub)form name and object in the query.

Hope this helps Chris

 

[kstuart]

Same query, multi[ple forms

Thanks Chris,

I'll give this a shot. Should save me a lot of work and confusion.

 

[CJBIRKIN]

no worries

 

[trickg]

Global variables and Functions

When I encounter this problem, I create global variables in a module and create functions that are set to the value of the global variable so that way you can use them from the Expression builder. This is a typical way to code them in a module:
_____________________________________________

Option Compare Database
Global g_dStartDate as Date
Global g_dEndDate as Date

Public Function GetStartDate() As Date
GetStartDate = g_dStartDate
End Function

Public Function GetEndDate() As Date
GetEndDate = g_dEndDate
End Function


Using this methodology, you can now set your criteria in your query by using the expression builder and using functions that are application specific. your statement would look somthing like this:

"Between GetStartDate() And GetEndDate()"

The trick is to make sure that the correct values have been assigned to the global variables from whatever form you are accessing the query from. You can throw the code into your command button that opens the form that runs the query or whatever. The good news is that you can now access this query from wherever you want to in your application without having to worry about what forms you may or may not have open or running.

This method may not be perfect, but it's solved a lot of problems for me! Just make sure you don't end up with a lot of global variables sucking up memory.

Another tip: I have a module that has some neat functions in it that can and do come in handy. Make sure that you save that module and import it into your Access applications because you never know when they might come in handy and it saves you the time of having to recode stuff.

 

[The_Doc_Man]

You can also do this with a single query that isn't selective and add filters to the forms (sometimes).

You could also do this with parameter queries, which is a nice topic to research in the help files.

 

[Ingeneeus]

Hi, I hope my reply to this oooolllld thread pushes it back up to the top.

I've got very much the same problem as the original poster. I've got a query that I need to run from multiple forms.

The reply that seemed most workable to me was from

CJBIRKIN: Hello
The easiest answer is to use a sub form in the main form. The three "main form"s would have the same sub form attached, the sub form would contain the date selection object which would be the one you have referenced in the query. This would mean you can keep the same (sub)form name and object in the query.

I created a small test database and built a table, a query (QueryFromForm), a form (FormSource), and the subform in question (FormSub1). Initially, my subform was just a textbox (txtParam1) into which I could input my query parameter, which was [Forms]![FormSub1]![txtParam1]

I put a button on the main form with an On Click function to DoCmd.OpenQuery "QueryFromForm" and tested it. Unfortunately, I got an input popup asking me to enter a value for [Forms]![FormSub1]![txtParam1], which had a value in it. Picture is attached.

The query works if I enter a value, but that doesn't exactly help me.

I thought perhaps that the issue was that I was running the query from the main form instead of the subform, so I changed the subform and moved the command button to it.

Same input popup.

Have I grasped what CJ said all those years ago wrong, was he wrong, or was he right way back in 2002 but not in 2016 because I'm running Access 2010 and Microsoft has changed something fundamental?

I'm not married to this solution, but the other ones pretty much went over my head; this one seemed like something I could do.

As always, enlightenment will be appreciated!

 

[Galaxiom]

Have I grasped what CJ said all those years ago wrong, was he wrong, or was he right way back in 2002 but not in 2016 because I'm running Access 2010 and Microsoft has changed something fundamental?

CJBIRKIN was indeed wrong. When opened as subform that form can only be referred to via its main form and subformcontrol.

I wouldn't use global variables as suggested by trickg either. Nor would I use TempVars. Both have excessive scope.

One simple way is to use the Where Condition of the OpenForm command.

The best performance would be to use a Parameter query and set the parameters before opening the form. This allows them to be passed from anywhere convenient, including a record in another form.

No matter how you do it, the parameter values should be available somewhere on form that is open. Prompts to type them in are a serious inconvenience to users.

 

[Ingeneeus]

Posted by Galaxium
The best performance would be to use a Parameter query and set the parameters before opening the form. This allows them to be passed from anywhere convenient, including a record in another form.

No matter how you do it, the parameter values should be available somewhere on form that is open. Prompts to type them in are a serious inconvenience to users.

Hi, Galaxium -- thank you for the response. Using a query parameter was my first idea. Unfortunately, I have not been able to figure out how to write a parameter that essentially says "If form a ("FormSource")is open then use the value in the textbox ("TxtSrc") on that form as the query parameter, and if form b ("FormSource2") is open then use the value in the textbox on that form as the query parameter." Since I'm launching the query from either one form or the other, only one of them will be open at the time I need to run the query.

Unfortunately, If-Thens don't seem to play well in SQL. I tried a simple OR parameter ([Forms]![FormSource]![TxtSrc] or [Forms]![FormSource2]![TxtSrc]), which works if both forms are open. If only one of the forms is open, I get the parameter prompt again.

Is there a way to write this so that the query will run from either form?

 

[Galaxiom]

Set the parameters and run the query using VBA from whichever form is open.

You can use a form reference in the query so it runs automatically if the form is open. If it is closed then pass the parameters.

With DoCmd
     .SetParameter "Forms.closedform.controlname" = Forms.openedform.controlname
     .OpenQuery "queryname"
 .End With

You can use OpenForm instead of OpenQuery if the query is the RecordSource of the form.

 

[Ingeneeus]

Oooohhhh . . .

That is slick!
I'm going to try that as soon as I get the chance.

Thanks so much for sticking with me!

~Gene