Multiple forms using common select query 1

[ITchappie]

I have 3 forms and for arguments sake I will call them A, B & C. Currently each form runs an individual query based on select criteria that can be found on the form. However the underlying query is exactly the same for each of the 3 forms.

Is there a way that I can build 1 single query that can be called by any of the 3 forms? At the moment I have to use 3 identical queries to be able to use fixed calling form names like the syntax "[Forms]![frmA]![selContact]" or "[Forms]![frmB]![selContact]" etc..

In essence I want 1 query that via a passed parameter knows what form to look on for its select criteria rather than my current clumsy hard coding of 3 queries. Is this possible?

Many thanks
Glenn

 

[Remou]

You can open a form with a Where statement or open arguments:

http://msdn2.microsoft.com/en-us/library/aa141520(office.10).aspx

The Where statement can use form data, but in a slightly different way:
[tt]DoCmd.OpenForm "Employees", , ,"LastName = '" & Forms!f1.LastName & "'"[/tt]

 

[ITchappie]

Hi Remou

Thanks for your answer but i dont think it applies to my situation somehow. My problem is not with opening the form but the underlying query that the form uses. I open form A which is bound to query A, Open form B which is bound to query B, Open form C bound to query C etc.

In reality Queries A, B & C are identical only differing in the form name they refer to for their select criteria which is a control in the calling form. As each form's select criteria field names are the same (although the form itself may be different) it would be nice to somehow to open say form A and have a general query (called say Q) "know" the name of the form that has called it. I could therefore code the query select criteria something like "Equal to customer number from form name "X"". Where "X" would be the name of the form that was opened to kick the query off (either A, B or C).

I apologise if my example is crude and doesn't actually use the correct syntax but I hope you can see what I am trying to achive.

Thanks again
Glenn

 

[Remou]

Please post the sql of one of the queries.

 

[PHV]

Anyway, having a form with an underlaying query depending of the value of a control of this form is not (IMHO) a safe practice.
Why not having a front end form asking for the selContact value and the type of result expected (A, B or C) ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeAtWork]

Instead of using the form's controls directly in the query, you could create global functions that the query uses for parameters.

Example, replace this:

WHERE Salary > Forms!frmEmployees!cboSalaries

with

WHERE Salary > GetSalaryParameter()

In a module, you would have something like this:

Public g_curSalaryParam As Currency

Public Sub SetSalaryParameter(SalaryParam As Currency)
  g_curSalaryParam = SaleryParam
End Sub

Public Function GetSalaryParameter() As Currency
  GetSalaryParameter = g_curSalaryParam
End Function

Finally, in whatever control you are currently using for the query parameter, in it's AfterUpdate event (or maybe it's Click event if it's a dropdown), you do something like:

Private Sub cboSalary_Click()
  SetSalaryParameter cboSalary.Value
End Sub

 

[TheAceMan1]

How are ya ITchappie . . .

Not so much query as much as I reference SQL. All you need is a base SQL to which you [blue]append a proper Where Clause![/blue]

[blue]PHV's[/blue] suggestion is in parallel with this.

[blue]Your Thoughts! . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[ITchappie]

Hi everyone, Many thanks for all your suggestions and I take your point PVS. However, using JoeatWork's global function I can see how I can do what I want. I think I will have to revisit the overall logic at a later date but I am up against a change date deadline so thanks to all.
Glenn

 

[JoeAtWork]

One possible problem I just realized with my solution is if you have two or more of these forms open at the same time. In that case, they will influence each other's data source.