Cant get a criteria form to close

Any help with this would be greatly appreciated, as cannot work out how
to resolve.

I have a report called "3_Strikes". In its 'On open' event is command
to also open a criteria form popup form called, "3_Strikes_Search".
The purpose of the form is to allow input of data to filter the results
of the report, (In this case mail-merged letters).

The issue I have is that when I open the report via a command button,
the "3_Strikes_Search" form pops up. However if at this stage I decide
not to run the report, I cant close the pop-up form without various
error messages.

I imagine this is because the Open Report event has already taken place
in the background.

The first error is when click on the close button is:
This action cant be carried out while processing a form or report
event.
When I click okay it then prompting for date via parameter query input
boxes.

I am not experienced enough to devise a solution by VBA. So a point in
the right direction would be really useful.
Thanks
Zack

re: Cant get a criteria form to close

On 3 May 2006 03:54:15 -0700, zack wrote:
[color=blue]
> Any help with this would be greatly appreciated, as cannot work out how
> to resolve.
>
> I have a report called "3_Strikes". In its 'On open' event is command
> to also open a criteria form popup form called, "3_Strikes_Search".
> The purpose of the form is to allow input of data to filter the results
> of the report, (In this case mail-merged letters).
>
> The issue I have is that when I open the report via a command button,
> the "3_Strikes_Search" form pops up. However if at this stage I decide
> not to run the report, I cant close the pop-up form without various
> error messages.
>
> I imagine this is because the Open Report event has already taken place
> in the background.
>
> The first error is when click on the close button is:
> This action cant be carried out while processing a form or report
> event.
> When I click okay it then prompting for date via parameter query input
> boxes.
>
> I am not experienced enough to devise a solution by VBA. So a point in
> the right direction would be really useful.
> Thanks
> Zack[/color]

Open the form from the Report's Open event, then check if it is still
open. If not, cancel the event (and the report).

If you have Access 2000 or newer:
Code the Report's Open event:

DoCmd.OpenForm "3_Strikes_Search", , , , , acDialog

If Not CurrentProject.AllForms("3_Strikes_Search").IsLoad ed Then
MsgBox "Report has been cancelled"
Cancel = True
End If

If the Report has been opened from a code event (on a switchboard, for
example), closing the report will generate error 2501. Trap that error
in the switchboard event:

On Error GoTo Err_Handler
DoCmd.OpenReport "3_Strikes", acViewPreview
Exit_This_Sub:
Exit Sub
Err_Handler:
If Err = 2501 Then
Else
MsgBox "Error #: " & Err.Number & " " & Err.Description
End If
Resume Exit_This_Sub

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

re: Cant get a criteria form to close

fredg wrote:[color=blue]
> On 3 May 2006 03:54:15 -0700, zack wrote:
>[color=green]
> > Any help with this would be greatly appreciated, as cannot work out how
> > to resolve.
> >
> > I have a report called "3_Strikes". In its 'On open' event is command
> > to also open a criteria form popup form called, "3_Strikes_Search".
> > The purpose of the form is to allow input of data to filter the results
> > of the report, (In this case mail-merged letters).
> >
> > The issue I have is that when I open the report via a command button,
> > the "3_Strikes_Search" form pops up. However if at this stage I decide
> > not to run the report, I cant close the pop-up form without various
> > error messages.
> >
> > I imagine this is because the Open Report event has already taken place
> > in the background.
> >
> > The first error is when click on the close button is:
> > This action cant be carried out while processing a form or report
> > event.
> > When I click okay it then prompting for date via parameter query input
> > boxes.
> >
> > I am not experienced enough to devise a solution by VBA. So a point in
> > the right direction would be really useful.
> > Thanks
> > Zack[/color]
>
> Open the form from the Report's Open event, then check if it is still
> open. If not, cancel the event (and the report).
>
> If you have Access 2000 or newer:
> Code the Report's Open event:
>
> DoCmd.OpenForm "3_Strikes_Search", , , , , acDialog
>
> If Not CurrentProject.AllForms("3_Strikes_Search").IsLoad ed Then
> MsgBox "Report has been cancelled"
> Cancel = True
> End If
>
> If the Report has been opened from a code event (on a switchboard, for
> example), closing the report will generate error 2501. Trap that error
> in the switchboard event:
>
> On Error GoTo Err_Handler
> DoCmd.OpenReport "3_Strikes", acViewPreview
> Exit_This_Sub:
> Exit Sub
> Err_Handler:
> If Err = 2501 Then
> Else
> MsgBox "Error #: " & Err.Number & " " & Err.Description
> End If
> Resume Exit_This_Sub
>
> --
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail[/color]

Fred, Thank you for your response, this issue is that I have inherited
this database and so is still in Access 97. Could you help with a
conversion of the above code that would work in 97.
Many thanks
Zack

re: Cant get a criteria form to close

On 5 May 2006 02:40:23 -0700, zack wrote:
[color=blue]
> fredg wrote:[color=green]
>> On 3 May 2006 03:54:15 -0700, zack wrote:
>>[color=darkred]
>>> Any help with this would be greatly appreciated, as cannot work out how
>>> to resolve.
>>>
>>> I have a report called "3_Strikes". In its 'On open' event is command
>>> to also open a criteria form popup form called, "3_Strikes_Search".
>>> The purpose of the form is to allow input of data to filter the results
>>> of the report, (In this case mail-merged letters).
>>>
>>> The issue I have is that when I open the report via a command button,
>>> the "3_Strikes_Search" form pops up. However if at this stage I decide
>>> not to run the report, I cant close the pop-up form without various
>>> error messages.
>>>
>>> I imagine this is because the Open Report event has already taken place
>>> in the background.
>>>
>>> The first error is when click on the close button is:
>>> This action cant be carried out while processing a form or report
>>> event.
>>> When I click okay it then prompting for date via parameter query input
>>> boxes.
>>>
>>> I am not experienced enough to devise a solution by VBA. So a point in
>>> the right direction would be really useful.
>>> Thanks
>>> Zack[/color]
>>
>> Open the form from the Report's Open event, then check if it is still
>> open. If not, cancel the event (and the report).
>>
>> If you have Access 2000 or newer:
>> Code the Report's Open event:
>>
>> DoCmd.OpenForm "3_Strikes_Search", , , , , acDialog
>>
>> If Not CurrentProject.AllForms("3_Strikes_Search").IsLoad ed Then
>> MsgBox "Report has been cancelled"
>> Cancel = True
>> End If
>>
>> If the Report has been opened from a code event (on a switchboard, for
>> example), closing the report will generate error 2501. Trap that error
>> in the switchboard event:
>>
>> On Error GoTo Err_Handler
>> DoCmd.OpenReport "3_Strikes", acViewPreview
>> Exit_This_Sub:
>> Exit Sub
>> Err_Handler:
>> If Err = 2501 Then
>> Else
>> MsgBox "Error #: " & Err.Number & " " & Err.Description
>> End If
>> Resume Exit_This_Sub
>>
>> --
>> Fred
>> Please respond only to this newsgroup.
>> I do not reply to personal e-mail[/color]
>
> Fred, Thank you for your response, this issue is that I have inherited
> this database and so is still in Access 97. Could you help with a
> conversion of the above code that would work in 97.
> Many thanks
> Zack[/color]

Access 97 did not come with a built-in IsLoaded function so copy this
function (which is from the Northwind.mdb sample database) into a new
Module. Do not make any changes to it:

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 change the code in the Report's Open event to:

DoCmd.OpenForm "3_Strikes_Search", , , , , acDialog
If Not IsLoaded("3_Strikes_Search") Then
MsgBox "Report has been cancelled"
Cancel = True
End If

The code to trap error 2501 stays the same as I previously wrote.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

re: Cant get a criteria form to close

fredg wrote:[color=blue]
> On 5 May 2006 02:40:23 -0700, zack wrote:
>[color=green]
> > fredg wrote:[color=darkred]
> >> On 3 May 2006 03:54:15 -0700, zack wrote:
> >>
> >>> Any help with this would be greatly appreciated, as cannot work out how
> >>> to resolve.
> >>>
> >>> I have a report called "3_Strikes". In its 'On open' event is command
> >>> to also open a criteria form popup form called, "3_Strikes_Search".
> >>> The purpose of the form is to allow input of data to filter the results
> >>> of the report, (In this case mail-merged letters).
> >>>
> >>> The issue I have is that when I open the report via a command button,
> >>> the "3_Strikes_Search" form pops up. However if at this stage I decide
> >>> not to run the report, I cant close the pop-up form without various
> >>> error messages.
> >>>
> >>> I imagine this is because the Open Report event has already taken place
> >>> in the background.
> >>>
> >>> The first error is when click on the close button is:
> >>> This action cant be carried out while processing a form or report
> >>> event.
> >>> When I click okay it then prompting for date via parameter query input
> >>> boxes.
> >>>
> >>> I am not experienced enough to devise a solution by VBA. So a point in
> >>> the right direction would be really useful.
> >>> Thanks
> >>> Zack
> >>
> >> Open the form from the Report's Open event, then check if it is still
> >> open. If not, cancel the event (and the report).
> >>
> >> If you have Access 2000 or newer:
> >> Code the Report's Open event:
> >>
> >> DoCmd.OpenForm "3_Strikes_Search", , , , , acDialog
> >>
> >> If Not CurrentProject.AllForms("3_Strikes_Search").IsLoad ed Then
> >> MsgBox "Report has been cancelled"
> >> Cancel = True
> >> End If
> >>
> >> If the Report has been opened from a code event (on a switchboard, for
> >> example), closing the report will generate error 2501. Trap that error
> >> in the switchboard event:
> >>
> >> On Error GoTo Err_Handler
> >> DoCmd.OpenReport "3_Strikes", acViewPreview
> >> Exit_This_Sub:
> >> Exit Sub
> >> Err_Handler:
> >> If Err = 2501 Then
> >> Else
> >> MsgBox "Error #: " & Err.Number & " " & Err.Description
> >> End If
> >> Resume Exit_This_Sub
> >>
> >> --
> >> Fred
> >> Please respond only to this newsgroup.
> >> I do not reply to personal e-mail[/color]
> >
> > Fred, Thank you for your response, this issue is that I have inherited
> > this database and so is still in Access 97. Could you help with a
> > conversion of the above code that would work in 97.
> > Many thanks
> > Zack[/color]
>
> Access 97 did not come with a built-in IsLoaded function so copy this
> function (which is from the Northwind.mdb sample database) into a new
> Module. Do not make any changes to it:
>
> 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 change the code in the Report's Open event to:
>
> DoCmd.OpenForm "3_Strikes_Search", , , , , acDialog
> If Not IsLoaded("3_Strikes_Search") Then
> MsgBox "Report has been cancelled"
> Cancel = True
> End If
>
> The code to trap error 2501 stays the same as I previously wrote.
> --
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail[/color]

Fred, Thanks for the response. It was really useful. I am still getting
an error popup,
'The OpenReport action was cancelled', but I am sure I will get there.
Thanks for all your help.
Zack