Can I pass one parameter to two queries at the same time?

[Ann Scharpf]

I'm running Access 2003. I am working on a database for our programmers so
that they can find all code that is affected whenever they change a program.
This "investigation" uses four tables:

MainCode: ExtendedCode

ProgramName
ProgramCode
MainUses <---> ExtCode
ExtUses


XMLPhase1: XMLPhase2:

ProgramName
XMLCode
MainXHTML <---> MainXHTML
ExtXHTML


They want a user to be able to search for a program name and find all the
related records from both sets of tables. If I do the searches independently
for "apple" for example, I get 49 hits on the code query and 15 hits on the
XML query. But, of course, if I try to add the XML to the code query, it
finds all the links mulitple times and I end up with 735 lines in the query
results.

So, I'm trying to figure out if there is a way I can prompt the user one
time for the program name and then somehow pass that string to both queries.
Or is there some other way to link the two queries that I haven't been able
to find?

I hope I've given you enough information. As always, thank you so much for
your help.

 

[Jerry Whittle]

Create an unbound form called something like: frmParameter.

On this form put text fields something like [txtParameter], [txtParameter2],
etc. for all the needed criteria/parameters.

Next put a command button on the form that runs the queries. A nice touch
would be to have the form also go invisible as it needs to stay open for the
report to run.

Now the magic part! Put something like below in the query criteria as needed
with the proper form and text box name.

[Forms]![frmParameter]![txtParameter]

Now instead of getting bothered with multiple requests for parameters, it
will be on the form. If you need to run it again, the stuff will stay there
as the form is closed. You could even get fancy with things like a date
picker, combo box, or list of possible criteria from the tables in question.

You might be able to link the two queries together with a Union All between
their SQL statements. It would look something like this:

SELECT ItemNumber, ItemName
FROM (SELECT
Test1.ItemNumber, Test1.ItemName
FROM Test1
UNION ALL
SELECT Test2.ItemNumber, Test2.ItemName
FROM Test2)
WHERE ItemNumber = [Forms]![frmParameter]![txtParameter]

 

[Ann Scharpf]

Thank you so much! It is going to take a while to digest all this. I will
give it a try.
--
Ann Scharpf

Create an unbound form called something like: frmParameter.

On this form put text fields something like [txtParameter], [txtParameter2],
etc. for all the needed criteria/parameters.

Next put a command button on the form that runs the queries. A nice touch
would be to have the form also go invisible as it needs to stay open for the
report to run.

Now the magic part! Put something like below in the query criteria as needed
with the proper form and text box name.

[Forms]![frmParameter]![txtParameter]

Now instead of getting bothered with multiple requests for parameters, it
will be on the form. If you need to run it again, the stuff will stay there
as the form is closed. You could even get fancy with things like a date
picker, combo box, or list of possible criteria from the tables in question.

You might be able to link the two queries together with a Union All between
their SQL statements. It would look something like this:

SELECT ItemNumber, ItemName
FROM (SELECT
Test1.ItemNumber, Test1.ItemName
FROM Test1
UNION ALL
SELECT Test2.ItemNumber, Test2.ItemName
FROM Test2)
WHERE ItemNumber = [Forms]![frmParameter]![txtParameter]
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I'm running Access 2003. I am working on a database for our programmers so
that they can find all code that is affected whenever they change a program.
This "investigation" uses four tables:

MainCode: ExtendedCode

ProgramName
ProgramCode
MainUses <---> ExtCode
ExtUses


XMLPhase1: XMLPhase2:

ProgramName
XMLCode
MainXHTML <---> MainXHTML
ExtXHTML


They want a user to be able to search for a program name and find all the
related records from both sets of tables. If I do the searches independently
for "apple" for example, I get 49 hits on the code query and 15 hits on the
XML query. But, of course, if I try to add the XML to the code query, it
finds all the links mulitple times and I end up with 735 lines in the query
results.

So, I'm trying to figure out if there is a way I can prompt the user one
time for the program name and then somehow pass that string to both queries.
Or is there some other way to link the two queries that I haven't been able
to find?

I hope I've given you enough information. As always, thank you so much for
your help.