Subreport based on a query

[DanWH]

I have a subreport based on query that looks for a specific record based on
the "ID" field in a form. What I need to do is run that report from two
different forms. when I put the criteria in the query for that ID field to
look in one form OR the other, i always get the message that it can't find
whichever form is not open.

Is there a way for the query to look in either form for the ID field without
getting that message.

thanks
Dan

 

[Allen Browne]

You cannot design the query so that it looks at 2 different forms to get one
value, but there is another approach.

Leave the criteria out of the query. Instead, put a button on your form, so
it can OpenReport with a WhereCondition. Each form can then open the report
with the appropriate filter.

Here's an example of OpenReport with a WhereCondition:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html

Here's another one that uses dates:
http://allenbrowne.com/casu-08.html

 

[Dirk Goldgar]

I have a subreport based on query that looks for a specific record based on
the "ID" field in a form. What I need to do is run that report from two
different forms. when I put the criteria in the query for that ID field
to
look in one form OR the other, i always get the message that it can't find
whichever form is not open.

Is there a way for the query to look in either form for the ID field
without
getting that message.

I would be inclined to write a VBA function that returns the desired ID,
deriving it from whichever form is open, and returning Null if neither form
is open. Then I'd use that function in the criteria for the query. For
example:

'----- start of air code -----
Function IDWantedForSubreport() As Variant

If CurrentProject.AllForms("Form1").IsLoaded Then
IDWantedForSubreport = Forms!Form1!ID
ElseIf CurrentProject.AllForms("Form2").IsLoaded Then
IDWantedForSubreport = Forms!Form2!ID
Else
IDWantedForSubreport = Null
End If

End Function
'----- end of air code -----

Then the query's SQL might be along these lines:

SELECT * FROM MyTable WHERE ID=IDWantedForSubreport()