Cancel print on subreport based on date range

  • Thread starter Thread starter Miss Kait
  • Start date Start date
M

Miss Kait

I have a MRP report that contains two subreports, one with planned
releases and the other with the last three buys. The user enters
criteria on a form; release dates, item number, vendor, commodity
code, and buyer. The main report only contains a header with info
related to the item number (i.e. last cost rec'd, description, abc
code, and so on). The planned releases subreport contains releases
for dates ranging from 9/01/04 to 6/01/05. I would like to show only
the planned releases for the dates that have occurred in the past up
to the end date specified by the user on the form (i.e. the user
enters a date range of 10/04/04 to 10/09/04 and the subreport shows
all planned releases from 9/01/04 to 10/09/04).

Currently I have a subroutine in the subreport that cancels the
printing when the release date is greater than the range entered on
the form. The release dates in the table are date data type; the
dates entered on the form are formatted to date data type also. Two
problems occur:

1. The dates being passed from the form are strings, which causes all
of the 2005 dates to print on the subreport.

2. The dates that are not being printed are leaving white space on
the subreport; it will print 9/01/04 to 10/09/04 releases, skip the
other dates in 2004 (leaving white space) and print the dates in 2005.
I have tried setting the Can Shrink to yes on the subreport.

What am I missing for the dates to be passed to vba as strings? Am I
setting the Can Shrink on the correct report or are there ways to
control white space in code? Should I be using Cancel = True to stop
the print or is there a better way to exit the subroutine?

I am using Windows and Access 2000.

I have searched the groups and can't seem to find a solution for this
problem. Please excuse the long post. Thanks for any suggestions!
 
I may not fully understand your situation, but it sounded like you are
returning all the records in the table to your report, then canceling if
they are dated beyond your selection criteria.

Another approach would be to use the selection criteria on the form as
parameters in a query against the table, only returning the records that
match your criteria. That way, basing your report on the query means only
have the records you want go to the report.

Or have I missed the mark...?
 
The subreport is based on a query. And I have considered using
parameters to return only the records I want. I am guessing you are
suggesting using Between ‘a date' And ‘another date'. What would my
first date be? I could use:

Between [Forms]![frmBuySheets]![SDate] And
[Forms]![frmBuySheets]![EDate]

This would only return items with releases dates between those two
dates. Essentially I need to find the earliest date for an item and
return the records between the earliest date and the user specified
end date for each item. I cannot use a constant for either date; they
will change frequently.

Is it possible to open a recordset in a subreport, find the earliest
date for that item and return only the planned releases that fall
between the earliest date and the user specified end date? If so, how
would the code look?

I hope I have explained a little more clearly. Thanks for any
suggestions!
 
If you are saying that you will accept (select) any date on or before the
form's end-date, you don't need to use the "Between ... and ..." expression.
You could use something like:

< Forms!YourForm!YourEndDate

Am I still missing something?
 
I have solved this problem. I moved the code to cancel the print from
the Detail_Print event to the Detail_Format. The code is as follows:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Do Until Me.[M_PO_R_Release_Date] <= Forms!frmBuySheets!EDate
Cancel = True
Exit Sub
Loop
End Sub

Thanks for your help Jeff!
 
Back
Top