Stopping report

I have several reports that open one of several forms (depending on the
report) to allow users to select a date range/department/machine/shift for
their particular report. Each specific form is initiated by the OnOpen
function of the reports and is set to Modal so that the report will not open
until the form is closed. Mulitple reports use the same form.

In some cases, a user will start the report and, after seeing the form that
pops up, will decide that they've tried to run the wrong report. Closing
the form or entering blanks for dates still runs the query (time consuming)
for the report. Is there a way to give the user a "Cancel" button that will
close the form and stop the report/query from running? I've tried End, but
that closes Access altogether. I can't specify which Report to cancel
because each form is used by several reports.

On Thu, 14 Oct 2004 07:59:01 -0700, albertsong wrote:

I have several reports that open one of several forms (depending on the
report) to allow users to select a date range/department/machine/shift for
their particular report. Each specific form is initiated by the OnOpen
function of the reports and is set to Modal so that the report will not open
until the form is closed. Mulitple reports use the same form.

In some cases, a user will start the report and, after seeing the form that
pops up, will decide that they've tried to run the wrong report. Closing
the form or entering blanks for dates still runs the query (time consuming)
for the report. Is there a way to give the user a "Cancel" button that will
close the form and stop the report/query from running? I've tried End, but
that closes Access altogether. I can't specify which Report to cancel
because each form is used by several reports.

It will depend upon your version of Access.
Access 2000 or newer:
Code the Report's Open event

DoCmd.OpenForm "FormName", , , , , acDialog
If Not CurrentProject.AllForms("FormName").IsLoaded Then
MsgBox Cancelling the report"
Cancel = True
End If

===============

In Access 97, copy this function (from the Northwind.mdb sample
database) to a Module. Watch out for line wrapping.

Function IsLoaded(ByVal strFormName As String) As Integer
' Returns True if the specified form is open in Form view or
Datasheet
view.
Const conObjStateClosed = 0
Const conDesignView = 0
If SysCmd(acSysCmdGetObjectState, acForm, strFormName)
conObjStateClosed Then
If Forms(strFormName).CurrentView conDesignView Then
IsLoaded = True
End If
End If
End Function
================
Then Code the Report's Open event:
DoCmd.OpenForm "FormName", , , , , acDialog
If Not IsLoaded("FormName") Then
MsgBox "Cancelling Report"
Cancel = true
End if


--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

How about working the other way around?

Create a form that acts as a front-end to your reports. It will contain some
way the user can select the report to open (e.g. option group or combo). In
the AfterUpdate event of that control, display the various boxes that the
user can use to filter that particular report, keeping irrelevant ones
hidden. Below all that is a Preview button. When button is clicked, build up
a WhereCondition string from the controls where the user entered something,
and the OpenReport the one specified in the option group/combo.

This way:
- the user has already entered any filtering before the report is opened;
- there is only one form to open many reports (simple interface);
- nothing has to be modal;
- you don't have any jammed events in the queue.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"albertsong" wrote in message
...
I have several reports that open one of several forms (depending on the
report) to allow users to select a date range/department/machine/shift for
their particular report. Each specific form is initiated by the OnOpen
function of the reports and is set to Modal so that the report will not
open
until the form is closed. Mulitple reports use the same form.

In some cases, a user will start the report and, after seeing the form
that
pops up, will decide that they've tried to run the wrong report. Closing
the form or entering blanks for dates still runs the query (time
consuming)
for the report. Is there a way to give the user a "Cancel" button that
will
close the form and stop the report/query from running? I've tried End,
but
that closes Access altogether. I can't specify which Report to cancel
because each form is used by several reports.

I have to agree with Allen on this one. I've always wondered what the
benefits (if any) are of opening the form via the open event on the report.

--
Shaun Beane, MCDBA, MCT
http://dbageek.blogspot.com
"Allen Browne" wrote in message
...
How about working the other way around?

Create a form that acts as a front-end to your reports. It will contain
some way the user can select the report to open (e.g. option group or
combo). In the AfterUpdate event of that control, display the various
boxes that the user can use to filter that particular report, keeping
irrelevant ones hidden. Below all that is a Preview button. When button is
clicked, build up a WhereCondition string from the controls where the user
entered something, and the OpenReport the one specified in the option
group/combo.

This way:
- the user has already entered any filtering before the report is opened;
- there is only one form to open many reports (simple interface);
- nothing has to be modal;
- you don't have any jammed events in the queue.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"albertsong" wrote in message
...
I have several reports that open one of several forms (depending on the
report) to allow users to select a date range/department/machine/shift
for
their particular report. Each specific form is initiated by the OnOpen
function of the reports and is set to Modal so that the report will not
open
until the form is closed. Mulitple reports use the same form.

In some cases, a user will start the report and, after seeing the form
that
pops up, will decide that they've tried to run the wrong report.
Closing
the form or entering blanks for dates still runs the query (time
consuming)
for the report. Is there a way to give the user a "Cancel" button that
will
close the form and stop the report/query from running? I've tried End,
but
that closes Access altogether. I can't specify which Report to cancel
because each form is used by several reports.

On Thu, 14 Oct 2004 07:59:01 -0700, albertsong wrote:

I have several reports that open one of several forms (depending on the
report) to allow users to select a date range/department/machine/shift for
their particular report. Each specific form is initiated by the OnOpen
function of the reports and is set to Modal so that the report will not open
until the form is closed. Mulitple reports use the same form.

In some cases, a user will start the report and, after seeing the form that
pops up, will decide that they've tried to run the wrong report. Closing
the form or entering blanks for dates still runs the query (time consuming)
for the report. Is there a way to give the user a "Cancel" button that will
close the form and stop the report/query from running? I've tried End, but
that closes Access altogether. I can't specify which Report to cancel
because each form is used by several reports.

It will depend upon your version of Access.
Access 2000 or newer:
Code the Report's Open event

DoCmd.OpenForm "FormName", , , , , acDialog
If Not CurrentProject.AllForms("FormName").IsLoaded Then
MsgBox Cancelling the report"
Cancel = True
End If

===============

In Access 97, copy this function (from the Northwind.mdb sample
database) to a Module. Watch out for line wrapping.

Function IsLoaded(ByVal strFormName As String) As Integer
' Returns True if the specified form is open in Form view or
Datasheet
view.
Const conObjStateClosed = 0
Const conDesignView = 0
If SysCmd(acSysCmdGetObjectState, acForm, strFormName)
conObjStateClosed Then
If Forms(strFormName).CurrentView conDesignView Then
IsLoaded = True
End If
End If
End Function
================
Then Code the Report's Open event:
DoCmd.OpenForm "FormName", , , , , acDialog
If Not IsLoaded("FormName") Then
MsgBox "Cancelling Report"
Cancel = true
End if

--
I am using Access 2002 (but most users are using Access 2000).

Anyway, I have set the form's Modal property to Yes so that the user cannot
continue past the form without entering criteria. The "Continue to Report"
button is essentially a "CloseForm" button, so the form is always closed
before the report generates. Wouldn't the code above would always bring up
the MsgBox?

I need the "Cancel" button that I add to close the form, but to halt the
report (and associated query) from continuing.

I guess I should have mentioned that this a single databse on a network where
several users may be using it at the same time. So far, I've resisted
loading the front-end on everyone's PC because of how widely everyone is
distributed (the back end is a centralized SQL database). To this point,
I've just dealt with the small number of overlaps on the pop-up forms. If I
create a central form, would a user change the query criteria while another
user is running a report with a differnt set of reports?

I guess a lot of semi-literate Access users like myself open the form from
the report because we just use the Switchboard Manager for the user interface
for the report selection.


"Allen Browne" wrote:

How about working the other way around?

Create a form that acts as a front-end to your reports. It will contain some
way the user can select the report to open (e.g. option group or combo). In
the AfterUpdate event of that control, display the various boxes that the
user can use to filter that particular report, keeping irrelevant ones
hidden. Below all that is a Preview button. When button is clicked, build up
a WhereCondition string from the controls where the user entered something,
and the OpenReport the one specified in the option group/combo.

This way:
- the user has already entered any filtering before the report is opened;
- there is only one form to open many reports (simple interface);
- nothing has to be modal;
- you don't have any jammed events in the queue.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"albertsong" wrote in message
...
I have several reports that open one of several forms (depending on the
report) to allow users to select a date range/department/machine/shift for
their particular report. Each specific form is initiated by the OnOpen
function of the reports and is set to Modal so that the report will not
open
until the form is closed. Mulitple reports use the same form.

In some cases, a user will start the report and, after seeing the form
that
pops up, will decide that they've tried to run the wrong report. Closing
the form or entering blanks for dates still runs the query (time
consuming)
for the report. Is there a way to give the user a "Cancel" button that
will
close the form and stop the report/query from running? I've tried End,
but
that closes Access altogether. I can't specify which Report to cancel
because each form is used by several reports.

True, but even with the switchboard manager you can open the form instead of
the report and then trigger the report from the form. Please don't think
I'm trying to be argumentative, just trying to see think outloud about when
to use the open event. Thank you for the feedback!

On using one form for all reports with multiple users, one user will not
affect the criteria of another if they both happen to be running the report
at the same time. Even though the database is on the network, the
"instance" of the form is still unique.

--
Shaun Beane, MCDBA, MCT
http://dbageek.blogspot.com
"albertsong" wrote in message
...
I guess I should have mentioned that this a single databse on a network
where
several users may be using it at the same time. So far, I've resisted
loading the front-end on everyone's PC because of how widely everyone is
distributed (the back end is a centralized SQL database). To this point,
I've just dealt with the small number of overlaps on the pop-up forms. If
I
create a central form, would a user change the query criteria while
another
user is running a report with a differnt set of reports?

I guess a lot of semi-literate Access users like myself open the form from
the report because we just use the Switchboard Manager for the user
interface
for the report selection.


"Allen Browne" wrote:

How about working the other way around?

Create a form that acts as a front-end to your reports. It will contain
some
way the user can select the report to open (e.g. option group or combo).
In
the AfterUpdate event of that control, display the various boxes that the
user can use to filter that particular report, keeping irrelevant ones
hidden. Below all that is a Preview button. When button is clicked, build
up
a WhereCondition string from the controls where the user entered
something,
and the OpenReport the one specified in the option group/combo.

This way:
- the user has already entered any filtering before the report is opened;
- there is only one form to open many reports (simple interface);
- nothing has to be modal;
- you don't have any jammed events in the queue.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"albertsong" wrote in message
...
I have several reports that open one of several forms (depending on the
report) to allow users to select a date range/department/machine/shift
for
their particular report. Each specific form is initiated by the OnOpen
function of the reports and is set to Modal so that the report will not
open
until the form is closed. Mulitple reports use the same form.

In some cases, a user will start the report and, after seeing the form
that
pops up, will decide that they've tried to run the wrong report.
Closing
the form or entering blanks for dates still runs the query (time
consuming)
for the report. Is there a way to give the user a "Cancel" button that
will
close the form and stop the report/query from running? I've tried End,
but
that closes Access altogether. I can't specify which Report to cancel
because each form is used by several reports.

No, you're not argumentative at all...I'm just trying to understand.

I know that I can open the form from the switchboard and then open the
report from the form, but I have a group of ~10 forms that feed ~42 reports
and ~12 macros (for those users that want the numbers and not a pretty
report).

If I understand you all correctly, I would have a single form that would:
1. Allow the users to select a report or macro
2. Then update the Visible value of the appropriate filter criteria (based
on the selection in step 1)
3. Run the report or macro

Would you recommend having a table behind the form with report/macro names,
the criteria that should be visible for each, and the values the user selects
for the criteria for the queries to pull from? Or should the form be
unbound, coded to make the appropriate criteria selection visible and then
pass criteria selection to the query right from the form?

"Shaun Beane" wrote:

True, but even with the switchboard manager you can open the form instead of
the report and then trigger the report from the form. Please don't think
I'm trying to be argumentative, just trying to see think outloud about when
to use the open event. Thank you for the feedback!

On using one form for all reports with multiple users, one user will not
affect the criteria of another if they both happen to be running the report
at the same time. Even though the database is on the network, the
"instance" of the form is still unique.

--
Shaun Beane, MCDBA, MCT
http://dbageek.blogspot.com
"albertsong" wrote in message
...
I guess I should have mentioned that this a single databse on a network
where
several users may be using it at the same time. So far, I've resisted
loading the front-end on everyone's PC because of how widely everyone is
distributed (the back end is a centralized SQL database). To this point,
I've just dealt with the small number of overlaps on the pop-up forms. If
I
create a central form, would a user change the query criteria while
another
user is running a report with a differnt set of reports?

I guess a lot of semi-literate Access users like myself open the form from
the report because we just use the Switchboard Manager for the user
interface
for the report selection.


"Allen Browne" wrote:

How about working the other way around?

Create a form that acts as a front-end to your reports. It will contain
some
way the user can select the report to open (e.g. option group or combo).
In
the AfterUpdate event of that control, display the various boxes that the
user can use to filter that particular report, keeping irrelevant ones
hidden. Below all that is a Preview button. When button is clicked, build
up
a WhereCondition string from the controls where the user entered
something,
and the OpenReport the one specified in the option group/combo.

This way:
- the user has already entered any filtering before the report is opened;
- there is only one form to open many reports (simple interface);
- nothing has to be modal;
- you don't have any jammed events in the queue.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"albertsong" wrote in message
...
I have several reports that open one of several forms (depending on the
report) to allow users to select a date range/department/machine/shift
for
their particular report. Each specific form is initiated by the OnOpen
function of the reports and is set to Modal so that the report will not
open
until the form is closed. Mulitple reports use the same form.

In some cases, a user will start the report and, after seeing the form
that
pops up, will decide that they've tried to run the wrong report.
Closing
the form or entering blanks for dates still runs the query (time
consuming)
for the report. Is there a way to give the user a "Cancel" button that
will
close the form and stop the report/query from running? I've tried End,
but
that closes Access altogether. I can't specify which Report to cancel
because each form is used by several reports.

Yes, that's exactly the way I've done that in the main database that I
support. I don't use a table to store the macro/module names but I can
definitely see the benefit in that. My prompt form is pretty big, but like
you said, I enable/disable the controls based on which report the user
clicks on. I actually pass the criteria using VBA, but you could certainly
go right to the query.

--
Shaun Beane, MCDBA, MCT
http://dbageek.blogspot.com
"albertsong" wrote in message
...
No, you're not argumentative at all...I'm just trying to understand.

I know that I can open the form from the switchboard and then open the
report from the form, but I have a group of ~10 forms that feed ~42
reports
and ~12 macros (for those users that want the numbers and not a pretty
report).

If I understand you all correctly, I would have a single form that would:
1. Allow the users to select a report or macro
2. Then update the Visible value of the appropriate filter criteria
(based
on the selection in step 1)
3. Run the report or macro

Would you recommend having a table behind the form with report/macro
names,
the criteria that should be visible for each, and the values the user
selects
for the criteria for the queries to pull from? Or should the form be
unbound, coded to make the appropriate criteria selection visible and then
pass criteria selection to the query right from the form?

"Shaun Beane" wrote:

True, but even with the switchboard manager you can open the form instead
of
the report and then trigger the report from the form. Please don't think
I'm trying to be argumentative, just trying to see think outloud about
when
to use the open event. Thank you for the feedback!

On using one form for all reports with multiple users, one user will not
affect the criteria of another if they both happen to be running the
report
at the same time. Even though the database is on the network, the
"instance" of the form is still unique.

--
Shaun Beane, MCDBA, MCT
http://dbageek.blogspot.com
"albertsong" wrote in message
...
I guess I should have mentioned that this a single databse on a network
where
several users may be using it at the same time. So far, I've resisted
loading the front-end on everyone's PC because of how widely everyone
is
distributed (the back end is a centralized SQL database). To this
point,
I've just dealt with the small number of overlaps on the pop-up forms.
If
I
create a central form, would a user change the query criteria while
another
user is running a report with a differnt set of reports?

I guess a lot of semi-literate Access users like myself open the form
from
the report because we just use the Switchboard Manager for the user
interface
for the report selection.


"Allen Browne" wrote:

How about working the other way around?

Create a form that acts as a front-end to your reports. It will
contain
some
way the user can select the report to open (e.g. option group or
combo).
In
the AfterUpdate event of that control, display the various boxes that
the
user can use to filter that particular report, keeping irrelevant ones
hidden. Below all that is a Preview button. When button is clicked,
build
up
a WhereCondition string from the controls where the user entered
something,
and the OpenReport the one specified in the option group/combo.

This way:
- the user has already entered any filtering before the report is
opened;
- there is only one form to open many reports (simple interface);
- nothing has to be modal;
- you don't have any jammed events in the queue.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"albertsong" wrote in message
...
I have several reports that open one of several forms (depending on
the
report) to allow users to select a date
range/department/machine/shift
for
their particular report. Each specific form is initiated by the
OnOpen
function of the reports and is set to Modal so that the report will
not
open
until the form is closed. Mulitple reports use the same form.

In some cases, a user will start the report and, after seeing the
form
that
pops up, will decide that they've tried to run the wrong report.
Closing
the form or entering blanks for dates still runs the query (time
consuming)
for the report. Is there a way to give the user a "Cancel" button
that
will
close the form and stop the report/query from running? I've tried
End,
but
that closes Access altogether. I can't specify which Report to
cancel
because each form is used by several reports.

Yes, you've understood that correctly.

Personally, I don't bind this form to a table (like switchboard) or read the
report names from the system table (because I don't want to expose the
subreports). You could create your own table to manage this if you wanted
to.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"albertsong" wrote in message
...
No, you're not argumentative at all...I'm just trying to understand.

I know that I can open the form from the switchboard and then open the
report from the form, but I have a group of ~10 forms that feed ~42
reports
and ~12 macros (for those users that want the numbers and not a pretty
report).

If I understand you all correctly, I would have a single form that would:
1. Allow the users to select a report or macro
2. Then update the Visible value of the appropriate filter criteria
(based
on the selection in step 1)
3. Run the report or macro

Would you recommend having a table behind the form with report/macro
names,
the criteria that should be visible for each, and the values the user
selects
for the criteria for the queries to pull from? Or should the form be
unbound, coded to make the appropriate criteria selection visible and then
pass criteria selection to the query right from the form?

"Shaun Beane" wrote:

True, but even with the switchboard manager you can open the form instead
of
the report and then trigger the report from the form. Please don't think
I'm trying to be argumentative, just trying to see think outloud about
when
to use the open event. Thank you for the feedback!

On using one form for all reports with multiple users, one user will not
affect the criteria of another if they both happen to be running the
report
at the same time. Even though the database is on the network, the
"instance" of the form is still unique.

--
Shaun Beane, MCDBA, MCT
http://dbageek.blogspot.com
"albertsong" wrote in message
...
I guess I should have mentioned that this a single databse on a network
where
several users may be using it at the same time. So far, I've resisted
loading the front-end on everyone's PC because of how widely everyone
is
distributed (the back end is a centralized SQL database). To this
point,
I've just dealt with the small number of overlaps on the pop-up forms.
If
I
create a central form, would a user change the query criteria while
another
user is running a report with a differnt set of reports?

I guess a lot of semi-literate Access users like myself open the form
from
the report because we just use the Switchboard Manager for the user
interface
for the report selection.


"Allen Browne" wrote:

How about working the other way around?

Create a form that acts as a front-end to your reports. It will
contain
some
way the user can select the report to open (e.g. option group or
combo).
In
the AfterUpdate event of that control, display the various boxes that
the
user can use to filter that particular report, keeping irrelevant ones
hidden. Below all that is a Preview button. When button is clicked,
build
up
a WhereCondition string from the controls where the user entered
something,
and the OpenReport the one specified in the option group/combo.

This way:
- the user has already entered any filtering before the report is
opened;
- there is only one form to open many reports (simple interface);
- nothing has to be modal;
- you don't have any jammed events in the queue.


"albertsong" wrote in message
...
I have several reports that open one of several forms (depending on
the
report) to allow users to select a date
range/department/machine/shift
for
their particular report. Each specific form is initiated by the
OnOpen
function of the reports and is set to Modal so that the report will
not
open
until the form is closed. Mulitple reports use the same form.

In some cases, a user will start the report and, after seeing the
form
that
pops up, will decide that they've tried to run the wrong report.
Closing
the form or entering blanks for dates still runs the query (time
consuming)
for the report. Is there a way to give the user a "Cancel" button
that
will
close the form and stop the report/query from running? I've tried
End,
but
that closes Access altogether. I can't specify which Report to
cancel
because each form is used by several reports.