Parameters with queries and reports

S

stokely

I have followed to the letter the instructions for using a parameter
form with a report to initiate a query and create the report.

http://office.microsoft.com/en-us/assistance/HA011170771033.aspx

I cannot get the form to fire the query, even though I have downloaded
the example and checked the code carefully (given in the above
article). I have checked the form and by itself it will pass parameters
to the query correctly. The report accesses the correct query without
the VB code to fire the form. Where do you tell the form to fire the
query and then have the query pass the result back to the report???

Any suggestions???
 
D

Duane Hookom

You can add a button to a form that will open the report. The report's
record source is your query.
 
S

stokely

Yes, I have the query name specified as the record source in the
report. I can test this by commenting out the VB code that opens the
form on "open."

BTW, the referenced article shows how to design a report that opens a
parameter form that accepts manually entered criteria that are then
passed to a query which does the query on tables, passing the results
back to the form. This is what I need. Can't make my version work.

The parameter form opens, but when I click 'OK' which fires a VB script
that should simply make the form invisible, I assume passing the
parameters on to the query, nothing happens.
 
D

Duane Hookom

What is the SQL view of your report's record source?
Is the form opened acDialog?
 
S

stokely

I have given up trying to make this work (by opening the report which
fires the dialog form, passes the parms to the query, and back to the
report). It seems more logical to me to open the form first, fill in
the parms, then fire the report which will pull the data from the
query.

When I rewrote the scripts to try and do this, the output of the report
is going to the printer directly. Why?

Here is the script for the form and the "OK" button:
Option Compare Database


Private Sub Cancel_Click_Click()
DoCmd.Close 'Close Form
End Sub

Private Sub OK_Click_Click()
DoCmd.OpenReport ("Report - Sales Summary by Member and Dates"), ,
, , , acDialog
Me_Visible = False
End Sub

In the report I only have script to close the form when the report
closes. I still need to insert script so that the report cannot be
opened manually.

Any advice on this approach?
 
S

stokely

Got it to work using this approach! Printer issue was fixed by using
correct parameter on the Report_Open. Added a global switch variable to
set on when the form is open so that the report tests this variable on
open and uses a popup message to disallow opening the report when the
form is closed. Added code to be sure the switch gets turned off when
the report is closed and when the form is cancelled.
 
Top