Duane:
I hope you come across this the original post was several days old. I am
trying to somthing similar. I have a two queries. One pulls the quality
control info for a given lot of material (criteria for this field is set
to
[LOT] which gives a pop up box to ask for the lot in question. The second
query pulls the raw material lots used to produce the product lot (again
criteria for this field is set to [LOT]). I want prepare a report with
the
first querry is shown in the top half and the second querry is shown in
the
bottom half. I have this set up but when I run it it asks for [LOT]
multiple
times.
I tried to set up a form that asks for lot and refier to that form using
forms!formX!lot in the criteria of the queries but it just gives me a text
box that asks for forms!formX!lot.
Am I missing somthing in the way the forms, queries, and reports are
linked?
Duane Hookom said:
You either change the SQL of all 12 subreports or you enter criteria
like:
Between Forms!frmX!txtStart and Forms!frmX!txtEnd
--
Duane Hookom
MS Access MVP
--
Jane Walker said:
What if you have a report that has 12 subreports based on 12 subforms
and
queries and you need to change all of the dates to be the same range?
:
Thank you very much
Ok, assuming you have a subreport with a record source of the saved
query
"qselMyQuery" that you would like filtered by "txtStartDate" and
"txtEndDate" on the current form. The query has a SQL property of:
Select *
FROM tblSales;
You would add code to run in the form prior to opening the report
with
the
subreport:
Dim strSQL as String
strSQL = "SELECT * FROM tblSales WHERE 1=1 "
If Not IsNull(Me.txtStartDate) Then
strSQL= strSQL & " And [SalesDate]>=#" & _
Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
strSQL= strSQL & " And [SalesDate]<=#" & _
Me.txtEndDate & "# "
End If
CurrentDb.QueryDefs("qselMyQuery").SQL = strSQL
Then open the main report with the subreport. The subreport will be
filtered by the date range.
--
Duane Hookom
MS Access MVP
--
Okay, could you give me an idea as to how to pass a date range to
the
query with SQL. I read that you could only send a single date not
a
range.
I tried a Select, Where but the where didn't seem to work with a
range.
I assumed by "recordset" and "VBA" you meant a recordset like:
Dim rs As DAO.Recordset
This would rarely be used in a report.
You can change the SQL property of a saved query prior to opening
your
report with subreports. The saved query could be used as the
record
source of the subreport.
--
Duane Hookom
MS Access MVP
--
Well I thought that you might be able to assign the Recordsource
with
the desired query string. So what does have 99.9% to do with
this
part?
Recordsets have little or nothing to do with 99.9% of all
reports.
What would you expect to do with a recordset and a subreport?
--
Duane Hookom
MS Access MVP
--
I would really like to learn how to do the whole recordset
thing
in
VBA which would get its parameters from a module. I'm just not
sure
how to do it. I see examples of it but don't have a clue where
to
begin. On the examples it show opening a database source but
do
you
really need to do that if the database is the one your using?
I'm
also working on a database that is a front and back end, would
I
need
to reference the back end with the code? Would I need to take
the
record source out of the properties of the report or what.
This
just
starts me into an area I need to learn more about. I was just
hoping
to pass a simple "Between startdate and enddate" to a
subreport.
If
I could of had the two variables drop into the subreport query
from
the module it would have been so much easier.
message
You can include calculated fields in the subreports' queries
that
read the
values from the form on which the dates / values are entered;
for
example:
StDateField: Forms!FormName!ControlName
Bind controls to those calculated fields so that you can use
their
values
in your subreport.
Or filter on those values directly in the query with the
calculated
fields.
Not sure why you would have a problem if you use a "different
form" -- any
reason that form could not include similar textboxes or
controls
for
entering the parameter values?
Otherwise, you're going to have use VBA code in the Open event
of
the
subreport to create and assign the Recordsource with the
desired
query
string; and the code will need to get the dates from
somewhere....
--
Ken Snell
<MS ACCESS MVP>
Well that does work but what if I want to print from a
different
form or
I want to print all reports for the first qtr. that would
mean
they
get
three reports, one for each month?
Isn't there a more dynamic way of passing parameters? From
my
understanding the subreports open before the main report so
even
if
I
place those two field on the report it couldn't pass them.
Thanks for you help.
message
And keep the form open (can be hidden) while the report is
being
opened
and viewed/printed.
--
Ken Snell
<MS ACCESS MVP>
message
All you should have to do is allow users to enter the start
and
end
dates on the form. Then set the subreport record source
query
to
use
the controls on the form like:
Between Forms!frmX!txtStart and Forms!frmX!txtEnd
--
Duane Hookom
MS Access MVP
--
This one would seem easy but I have not figured it out for
the
past 3
days.
Like everyone else I have a main report with subreports.
They
are all
linked to the main report by a common index but one
subreport
I
want
to find information pertaining to the corresponding month
of
the
report not all records. All records would be easy.
The user does data entry in a form, clicks a button,
report
prints
everyone is happy but now we want more info which involves
a
new
subreport.
Okay lets get to meat of the subject:
In a form the user type a plethora of items which includes
a
date in
one of the fields, could be any date. I take that date
run
it
up to a
global procedure in the module which gives me the first
and
last
day
of the month and year in two separate variables.
I want that date range to be used in one of the subreports
to
give me
everything associated with this particular subjects
records
for
the
month that corresponds with the original date entered.
I've tried to put unbound text boxes on the main report as
well
as the
subreport so the subreport's query can use those for
parameters.
I've
tried Select Where clauses but found that they can only
take
one
date
not a range. I tried concatenating variables and text into
one
and
passing that. I felt like I got really close when I
manually
entered
the parameters and it worked but like everyone else on
this
newsgroup
I'm missing it.
I don't want the user who is printing these reports to
type
the
date
ranges a hundred times for all the reports, I've already
got
it
done
for them.
