Home Ask Login Register

Developers Planet

Your answer is one click away!

konsti February 2016

MS Access how can i pass my search criteria from top query to subquery?

I have a query in a base-data table that (given that my search criteria are correct) gives back approx. 950 records.

Except of the 3 criteria fields, i want to have about 10 more fields (the Project is still at the beginning) , every single one based on sub-queries, some of them normal select queries, some are aggregated queries.

As far as i know every sub-query must give 1 and only one value back. This value school be individual for every Record of the top query.

My Problem now is, that i don't know how to pass the search criteria from the top query (simple select query) to the sub-query in the in 10 fields i mentioned before.

Is this possible at all, or is my Approach to complicated. Is there possibly an easier way?

I have a Windows 7 System with Office 2010 installed.

Your help is much appreciated.

Thanks a lot.

PS The sub-queries are based on the same table as the top query. Sorry, I forgot to mention.


VolleyballAddictSandiego February 2016

You can pass arguments between things with a function call to set a public variable. This vba must be in a Module, not behind a Form Module. I don't use this approach very often, because the global value is in volatile memory, I prefer to save the variable in a special data Table.

Public strGlobal As String
Function Func_ReadGlobal() As String
    Func_ReadGlobal = strGlobal
End Function
Function Func_WriteGlobal() As String
    strGlobal = Func_WriteGlobal
End Function

Sergey S. February 2016

In all subqueries create parameter(s) and use it as search criteria. Parameter name should be the same for same column. Now, if you use those subqueries in your main query, Access will ask only once per each parameter name, you don't need to pass them explicitly to subqueries.

konsti February 2016

Thank you guys.

I did'nt think of the most obvious solution with the Globals. I will try it out as soon as my Boss gives me the time to continue with the Project.

@Sergey I can't use the Parameter(s) way, because the whole query, incl. Subqueries shall run completely alone in VBA, without human input at all.

Post Status

Asked in February 2016
Viewed 3,427 times
Voted 10
Answered 3 times


Leave an answer

Quote of the day: live life