Mulitple Form Parameter Boxes before Opening Report

[Thorson]

I currently have a report that relies on 2 different forms for parameter
entries before the report will run correctly. I have the first form
parameter set to open the second form and then the report and then close the
form:

Private Sub cmdOK_Click()
DoCmd.OpenReport "rptMonthlyInventoryPG4"
DoCmd.OpenForm "frmCurrentInventoryDateQuery", acNormal
DoCmd.Close acForm, "frmDSACMonthlyInventoryReport"

End Sub

However the reports open before the person even has a chance to enter the
parameter in the second form. Is there any way I can put a requirement that
the second form first be entered in? or are there any other suggestions?

Thanks

 

[Paul Shapiro]

I think it's easier (for programmer and user) if you have a single parameter
form. You could create a new form with the two existing forms as subforms.

I do this with the parameter form opening in dialog mode. When the user has
finished entering parameters they click OK, which hides the form but does
not close it. If they click Cancel, it sets a Quit property to True and then
hides the form.

I put the code to open the parameter form in the Report's Open event, so the
form will run no matter how the user runs the report. When you open the
dialog mode parameter form, code execution will stop until that form is
either hidden or closed. In your code, you calculate a new record source for
the report, based on the parameter settings, and assign that to
Me.RecordSource. Close the parameter form and now the report opens with your
updated record source.

If you want to do it all externally, as you suggested below, then you need
to open the forms and let the user assign parameters. Once the parameters
are assigned, the user can click another button which opens the report. If
the report's record source is using parameters based on those form names, it
will now have the necessary values when it opens.

 

[Thorson]

I am new at this... How do you open the form in Dialog Mode?

 

[Thorson]

I looked up info about how to build a dialog box and my form is actually
already set as a dialog box (if I understand correctly). I also set the
report so that the report's Open Event opens the parameter form, however the
report continues to open and does not wait for the parameter to be entered in.

I found a great how-to article on it at
http://www.fontstuff.com/access/acctut19.htm

If I still don't understand things I'll ask again.

Thanks!

 

[Thorson]

I put the second form that requests parameters as a subform in the main form,
the problem I am having is that the report won't recognize the parameter
simply because it is open, do I have something set up wrong?

When I run the form that is currently the subform separate I have it set to
open a query based upon the date entered into the form. The report that I
would like to run is then based upon that query. I would still like to have
the option to open the query with the form separately or to open the form as
a subform in the mainform to run the report.

Can you offer any suggestions? Maybe I am doing this all wrong?

 

[Paul Shapiro]

Not completely clear on your details. Open your form(s) and enter whatever
parameters you want to specify. Then open the query that is the report's
record source. If you have not specified the parameters correctly the query
will prompt you for the parameters. Adjust the query parameter
specifications until the parameters are correctly read from your open form.

Some of the Access template databases probably demonstrate this technique
(an older version of the Time & Billing sample db definitely did, and
probably still does in current versions), and MS used to have a developer
sample database which illustrated many common techniques. Don't know if that
still exists but you could look for it.

 

[Thorson]

I just found the e-mail in regards to this reply in my spam box... You did
help me out, but I ended up creating a form that asks for all the parameters
on one form, therefore I don't have to worry about one not be entered before
the report opens, it works great. I used the website you gave me:
http://www.fontstuff.com/access/acctut19.htm

Thanks for the help!