Pause code issue

[Tara]

I need to find a way that users can click on a button to begin running a
report, have a form pop up that allows them to set criteria for whatever
report they chose, and then continue to that report. I have all the pieces
in place including the form that pops up (frmCriteria) and pauses the code so
the user can set the criteria. The problem I'm having is that the reports
each require frmCriteria be open in order to determine which RecordSet to
use. Because frmCriteria is opened as Dialog, it must be closed in order to
resume the code. So, I can suspend the code, but I can't start it again (by
closing frmCriteria) because then the report doesn't get the correct
RecordSet.

Any ideas as to how to overcome this?

 

[Jeff Boyce]

Tara

Instead of instigating the report first, then popping open the criteria
form, how about popping open the form first, then adding a "Do It" button on
that form that actually opens the report?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Tara]

Unfortunately, that won't work. Several different reports use this same form
to set the same criteria.

I suppose I could put multiple buttons on the form, one for each report, but
I was hoping there was maybe just some code that I could use with a single
command button to resume the code that called the frmCriteria to begin with.

 

[fredg]

I need to find a way that users can click on a button to begin running a
report, have a form pop up that allows them to set criteria for whatever
report they chose, and then continue to that report. I have all the pieces
in place including the form that pops up (frmCriteria) and pauses the code so
the user can set the criteria. The problem I'm having is that the reports
each require frmCriteria be open in order to determine which RecordSet to
use. Because frmCriteria is opened as Dialog, it must be closed in order to
resume the code. So, I can suspend the code, but I can't start it again (by
closing frmCriteria) because then the report doesn't get the correct
RecordSet.

Any ideas as to how to overcome this?

regarding ... Because frmCriteria is opened as Dialog, it must be
closed in order to resume the code ...

Not true.
Instead of closing the form simply code that command button to make
the form not visible:
Me.Visible = False

Then the report will still have access to the form data and the report
will open.
Close the form in the Report's Close event:
DoCmd.Close acForm, "frmCrriteria"

The method I use is to first open the report.
In the report's Open event I open the frmCriteria in dialog:
DoCmd.OpenForm "frmCriteria", , , , , acDialog

Then I would continue as I suggested above, making the form not
visible and then closing it when the report closes.

 

[Jeff Boyce]

Tara

When faced with a similar issue (multiple reports, some of them potentially
using the same criteria ... or not), here's the solution I came up with.

* create a table that contains the Access report names, user-friendly
report names, and descriptions
* create a form with a combobox allowing selection of a report
* on that form, add selection criteria controls
* on that form, add a SINGLE command button that opens a report

?Which report? The one selected in the combobox!

The DoCmd.OpenReport command includes syntax to allow for a where
clause/filter ... which I build dynamically, "behind the form", based on the
selection criteria, and controlled by that single command button.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Tara]

You've both given me some great ideas here. Both would accomplish what I
want. It just depends on which direction I want to go.

Jeff, I've actually done what you suggested before. Why that idea didn't
come to me when I was designing this, I don't know...

Fred, I think your idea is what I was originally trying to accomplish here.
I didn't realize that by making the form invisible it waould then resume to
the code...I'm off to add that piece in now and see what happens.

Thanks so much to both of you!