Re: Passing date range Parameter to subreport

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance


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.


"Ken Snell (MVP)" <kthsneisllis9@xxxxxxxxxxxxxxxxxx> wrote in message
news:ustisXQUGHA.1576@xxxxxxxxxxxxxxxxxxxxxxx
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>



"John Buehler" <john.buehler@xxxxxxxxx> wrote in message
news:OJVpPsOUGHA.4608@xxxxxxxxxxxxxxxxxxxxxxx
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.

"Ken Snell (MVP)" <kthsneisllis9@xxxxxxxxxxxxxxxxxx> wrote in message
news:%23I$kenIUGHA.5108@xxxxxxxxxxxxxxxxxxxxxxx
And keep the form open (can be hidden) while the report is being opened
and viewed/printed.

--

Ken Snell
<MS ACCESS MVP>

"Duane Hookom" <duanehookom@xxxxxxxxxxxxxxxxx> wrote in message
news:eXPhQRHUGHA.2244@xxxxxxxxxxxxxxxxxxxxxxx
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
--

"John Buehler" <john.buehler@xxxxxxxxx> wrote in message
news:%23$cNMCGUGHA.5148@xxxxxxxxxxxxxxxxxxxxxxx
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.













.

Quantcast