Displaying report criterion...again.

[Tom MacKay]

To set the table...

1) I have a simple database with a report that...you guessed it...requires a
pair of date parameters to trap the data to be reported.
2) I have looked at the methods to do this...and to report the values on the
report...and chosen the simplest (I thought) route.
3) I have a make-table query...with a Criterion value of 'Between [A] and
' on the date column
4) That make-table query is called whenever a certain report is run.
4.5) The report refers to the table created by the make-table query.
5) The report tries to display the date values entered...[A] for
example...by setting the Control Source of a text box to '=[A]'.

Instead of working the way I need...when I run the report, I am first
prompted for my dates, which get used correctly to trap the records I
need...but then I am prompted again for those values, and whatever I enter
in, text or date or whatever, that gets displayed on the report!

I guess I have not refered to my parameters properly on the report...maybe
because the make-table query contains the parameters, which makes the actual
table which the report then gets its information from. I have tried adding
the specific query name to the start of the parameter in the report's control
source values but that doesn't work. Nothing works...I always have to enter
data twice.

I would be happy to try building an unbound form to enter the parameters,
which might give me a more reliable object to refer to...but I tried very
hard to do that, and got bogged down by the setup of all the
controls/functions to open/close/whatever that form, the function ISLOADED in
particular...

Let me know what you think...

Regards,
Tom

 

[Tom MacKay]

I have a second version now...which tries to use a form, I think it is
working.
(i)The form properly traps my date criterion...uses it to filter the records
properly.
(ii) the form will allow display of the parameters on the report.

But all independently...meaning...the problem with this one is now...I need
an event on the Report which will, when the report is launched (i) launch the
form for parameter input (ii) run the make table query which uses those
parameters and finaly (iii) presents the report using the data from the
made-table.

The On Open event procedure I used for my first database was...

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenQuery "Make Report Transactions"
End Sub
where Make report Transactions is the make-table query

Now I need to have something like

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Report Criterion"
End Sub

And teach the form to execute the make-table query on OK...

 

[Tom MacKay]

All done now...have the form as the single place to go to enter criterion,
run the make-table query and display the report

I bet a lot of my form "paths" are broken...ie on Cancel or other
non-standard usage...but it basically does what I need.

Anyone who wants to post a question about what I did, or offer any other
advice is still appreciated.

Txs,
Tom