Hi friends,
i've spent all the morning searching an answer to my problem (I would like to think that it's a very simple problem).

I try to simplify the project that I would like to write:

Query_A:
SELECT * FROM PRODUCTS WHERE (((PRODUCTS.CODICE)=[COD]));

and this works well. If I try to open the query it asks for the COD parameter. My problem is the following: how can I recall the Query_A from another query passing the COD parameter?

Query_B:
SELECT * FROM Query_A WHERE [COD]='0001009';

I've tried many other syntax without any result. The Query_B continues to ask me for the COD parameter.

Any help would be appreciated and obviously I won't do that using VBA. Thanks in advance.

-Albert

How are you passing the COD value to query B?

Here is a link to a sample database that uses public variable.
Link (http://www.access-programmers.co.uk/forums/showthread.php?t=167718)

David

Many thanks to your prompt replay.

I'm looking for the example in the link that you have posted but YES that value of COD must be in the second query. The project is a bit more complicated.

Every query must pass it's own values to the parameteres. Is it possible to specify the COD value directly in the QUERY_B using the "function" trick?

Why does query B have control over query A? surely if the filter was on query A then no matter what was in query B would not affect query A. I assume that query A is a master of all COD's and Query B will act as a filter on QueryA

David

Thanks again for your help.
The question is a little bit complex. As I writed in my first post I've tried to simply the problem. Query_A must contain a PARAMETER because is the first aggregation query that is the source of other queries that select the right PRICE for every COD in my database.

Now, when I recall the last query of this chain it obviously appears a message box that asks me the PARAMETER of the first query in that chain that I want to recall. I can't simple eliminate the parameter for QUERY_A and doing a filter to the final result because the result because it's not possible.

The real problem is that I've to recall a query with a parameter inside (or inside another subquery of that) and I've to specify this parameter when I call it.

There is a solution for doing that?

(sorry for my bad english)

A little step forward: using the function trick I've associated the field COD of QUERY_A to function that returns the global variable:

Query_A:
SELECT * FROM PRODUCTS WHERE PRODUCTS.CODICE=GetCOD();

Public sCod As String
Public Function GetListino(Optional sNewCod As String) As String
If sNewCod = "" Then
GetListino= sCod
Else
sCod = sNewCod
End If
End Function

Now in QUERY_B i've to call the GetCod function before the QUERY_A asks for the sCod variable.

Query_B:
SELECT GetListino("001900"), * FROM Query_A;

But nothing. The first time I try to recall the query it return a null results because the QUERY_A is called before the function GetListino("xxxx"). The second time obviouly it returns the right result only because the global variable has already been set.

Any trick to do followig this method to solve the problem?

In your function you are missing a line

Public Function GetListino(Optional sNewCod As String) As String
If sNewCod = "" Then
GetListino= sCod
Else
sCod = sNewCod
GetListino= sCod

End If
End Function

The else condtion is not returning a string


What you could try is to use queryDefs to rewrite the sql prior to ruinning them

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim StrCOD As String

Set db = CurrentDb
Set qdf = db.QueryDefs("QueryA")
StrCOD = GetCOD()

qdf.SQL = "SELECT * FROM PRODUCTS WHERE PRODUCTS.CODICE='" & StrCOD & "';"

Set qdf = db.QueryDefs("QueryB")

qdf.SQL = "SELECT " & GetListino(StrCOD), * FROM Query_A;


qdf.close
Set db = Nothing
Set qdf = Nothing


What you have effectively done is to rewrite the SQL of each query to contain the correct parameters. Examine each one first then run queryB to see if it works


David