Cancel Open Report from a parameter form

[Tina Hudson]

Good afternoon,

I have a report menu that gives user the option of previewing several
reports. User clicks on a cmdbutton to open a specific report, and a
parameter form opens, prompting user to select in a cbo box, for example, a
unit name, and press OK, or Cancel.

After unit name is selected, and user presses cmdbutton "OK", the report
opens with info for that unit. No problems here.

PROBLEM: However, if a user changes mind and wants to back out, and clicks
on "cancel", the default Access parameter box opens asking for the Unit name
after "cancel" button is pressed.

Question: how can I make the access parameter box from appearing when the
user clicks cancel?

Here's the code:

cmdbutton on the frmReportMenu to open report:

Dim stDocName As String
stDocName = "rptMonthlyUnitReport"

DoCmd.OpenReport stDocName, acPreview


OnOpen event for report:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmParamUnit", , , , , acDialog
DoCmd.Maximize
End Sub

The query behind rptMonthlyUnitReport" has the parameter
"=[forms]![frmParamUnit]![cboUnitName]" in the field "UnitName"


The code behind the "cancel" cmdbutton on the parameter form is:

Private Sub cmdCancel_Click()
On Error GoTo Err_cmdCancel_Click

DoCmd.Close

Exit_cmdCancel_Click:
Exit Sub

Err_cmdCancel_Click:
MsgBox Err.Description
Resume Exit_cmdCancel_Click

End Sub

 

[fredg]

Good afternoon,

I have a report menu that gives user the option of previewing several
reports. User clicks on a cmdbutton to open a specific report, and a
parameter form opens, prompting user to select in a cbo box, for example, a
unit name, and press OK, or Cancel.

After unit name is selected, and user presses cmdbutton "OK", the report
opens with info for that unit. No problems here.

PROBLEM: However, if a user changes mind and wants to back out, and clicks
on "cancel", the default Access parameter box opens asking for the Unit name
after "cancel" button is pressed.

Question: how can I make the access parameter box from appearing when the
user clicks cancel?

Here's the code:

cmdbutton on the frmReportMenu to open report:

Dim stDocName As String
stDocName = "rptMonthlyUnitReport"

DoCmd.OpenReport stDocName, acPreview

OnOpen event for report:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmParamUnit", , , , , acDialog
DoCmd.Maximize
End Sub

The query behind rptMonthlyUnitReport" has the parameter
"=[forms]![frmParamUnit]![cboUnitName]" in the field "UnitName"

The code behind the "cancel" cmdbutton on the parameter form is:

Private Sub cmdCancel_Click()
On Error GoTo Err_cmdCancel_Click

DoCmd.Close

Exit_cmdCancel_Click:
Exit Sub

Err_cmdCancel_Click:
MsgBox Err.Description
Resume Exit_cmdCancel_Click

End Sub

Trap the error (2501) in the form's code that opens the report.

Private Sub CommandName_click()
On Error GoTo Err_Handler
Dim stDocName As String
stDocName = "rptMonthlyUnitReport"

DoCmd.OpenReport stDocName, acPreview
Exit_This_Sub:
Exit Sub
Err_Handler:
If Err = 2501 Then
Else
MsgBox "Error: " & Err.Number & " " & Err.Description
End If
Resume Exit_this_Sub
End Sub

 

[Tina Hudson]

Jim,

I use the parameter form with more than one report so that I don't have to
create a lot of forms that are duplicates. So, depending upon which report a
user wants, the same parameter form opens. I have about 4 parameter forms
that are associated with about 15 different reports.

If there is a better way of doing this, then please let me know!

--
Thanks,
Tina Hudson

Why are you opening the parameter form from the report's open event proc?
Seems like you could do that in the event proc where the button is that is
used to open the form. You would need to have a way of knowing when the user
clicks Cancel - I would just create a hidden text box on the report menu, if
the user clicks cancel then set that field to some value, say -1. Something
like this in the command button click event on frmReportMenu:

txtStatusField = 0
DoCmd.OpenForm "frmParamUnit", , , , , acDialog
if txtStatusField = 0 then
DoCmd.OpenReport stDocName, acPreview
end if

in the Cancel button 'on click' event on frmParamUnit include this before you
close the form:

Forms!frmReportMenu!txtStatusField = -1

txtStatusField would be a hidden textbox (visible property set to false) on
frmReportMenu

Good afternoon,

I have a report menu that gives user the option of previewing several
reports. User clicks on a cmdbutton to open a specific report, and a
parameter form opens, prompting user to select in a cbo box, for example, a
unit name, and press OK, or Cancel.

After unit name is selected, and user presses cmdbutton "OK", the report
opens with info for that unit. No problems here.

PROBLEM: However, if a user changes mind and wants to back out, and clicks
on "cancel", the default Access parameter box opens asking for the Unit name
after "cancel" button is pressed.

Question: how can I make the access parameter box from appearing when the
user clicks cancel?

Here's the code:

cmdbutton on the frmReportMenu to open report:

Dim stDocName As String
stDocName = "rptMonthlyUnitReport"

DoCmd.OpenReport stDocName, acPreview

OnOpen event for report:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmParamUnit", , , , , acDialog
DoCmd.Maximize
End Sub

The query behind rptMonthlyUnitReport" has the parameter
"=[forms]![frmParamUnit]![cboUnitName]" in the field "UnitName"

The code behind the "cancel" cmdbutton on the parameter form is:

Private Sub cmdCancel_Click()
On Error GoTo Err_cmdCancel_Click

DoCmd.Close

Exit_cmdCancel_Click:
Exit Sub

Err_cmdCancel_Click:
MsgBox Err.Description
Resume Exit_cmdCancel_Click

End Sub

 

[Tina Hudson]

Fred,

I'm sorry - I should have posted the entire code. I do have the code to
trap the error:

Private Sub cmdActiveByUnit_Click()
On Error GoTo Err_Handler:
Dim stDocName As String

stDocName = "rptMonthlyUnitReport"
DoCmd.OpenReport stDocName, acPreview

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description
End If


Resume Exit_Handler:

End Sub

Any other suggestions?


--
Thanks,
Tina Hudson

Good afternoon,

I have a report menu that gives user the option of previewing several
reports. User clicks on a cmdbutton to open a specific report, and a
parameter form opens, prompting user to select in a cbo box, for example, a
unit name, and press OK, or Cancel.

After unit name is selected, and user presses cmdbutton "OK", the report
opens with info for that unit. No problems here.

PROBLEM: However, if a user changes mind and wants to back out, and clicks
on "cancel", the default Access parameter box opens asking for the Unit name
after "cancel" button is pressed.

Question: how can I make the access parameter box from appearing when the
user clicks cancel?

Here's the code:

cmdbutton on the frmReportMenu to open report:

Dim stDocName As String
stDocName = "rptMonthlyUnitReport"

DoCmd.OpenReport stDocName, acPreview

OnOpen event for report:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmParamUnit", , , , , acDialog
DoCmd.Maximize
End Sub

The query behind rptMonthlyUnitReport" has the parameter
"=[forms]![frmParamUnit]![cboUnitName]" in the field "UnitName"

The code behind the "cancel" cmdbutton on the parameter form is:

Private Sub cmdCancel_Click()
On Error GoTo Err_cmdCancel_Click

DoCmd.Close

Exit_cmdCancel_Click:
Exit Sub

Err_cmdCancel_Click:
MsgBox Err.Description
Resume Exit_cmdCancel_Click

End Sub

Trap the error (2501) in the form's code that opens the report.

Private Sub CommandName_click()
On Error GoTo Err_Handler
Dim stDocName As String
stDocName = "rptMonthlyUnitReport"

DoCmd.OpenReport stDocName, acPreview
Exit_This_Sub:
Exit Sub
Err_Handler:
If Err = 2501 Then
Else
MsgBox "Error: " & Err.Number & " " & Err.Description
End If
Resume Exit_this_Sub
End Sub

 

[Tina Hudson]

Jim,

I'm not sure I'm following you. But, I'm not always using the same param
form to open a report on the frmReportMenu.

I can get this to work with one parameter form and one report (many, many
thanks!), but not if I have different parameter forms opening up multiple
reports.

In the GetParms function, you have the code opening up just one parameter
form:
DoCmd.OpenForm "frmParamUnit", , , , , acDialog

Sorry to hear its fairly simple, because it's going right over my head. : )


--
Thanks,
Tina Hudson

What I suggested won;t require any additional forms and will require no code
at all in any of the reports. I guess you can put code in each report's open
event, but I personally wouldn't do that.
Like I said, I would create one hidden text field on the report form (the one
that has the buttons that you click to open the reports). Then I'd create a
function in that form's module, something like this:

Private function GetParms() as Boolean

DoCmd.OpenForm "frmParamUnit", , , , , acDialog
if txtStatusField = 0 then
GetParms = true
else
GetParms = False
end if

End Function

in the Cancel button 'on click' event on frmParamUnit include this before you
close the form:

Forms!frmReportMenu!txtStatusField = -1

and in the OK button On CLick event (or whatever you call it if they don't
cancel)

Forms!frmReportMenu!txtStatusField = 0

Then for each button that you click on the reports menu, have something like

If GetParms Then
DOCmd.OpenReport....
End IF

This would require no code in any reports, no new forms. You won't hav to
worry about how to handle any errors if the user cancels the parms. And any
time you added a new report to the menu you'd just have to include the 'If
GetParms...' in that buttons On Click event. You'd always be using that same
parms form. Hope this makes sense. It might sound complicated but it's really
fairly simple.

Jim,

I use the parameter form with more than one report so that I don't have to
create a lot of forms that are duplicates. So, depending upon which report a
user wants, the same parameter form opens. I have about 4 parameter forms
that are associated with about 15 different reports.

If there is a better way of doing this, then please let me know!

Why are you opening the parameter form from the report's open event proc?
Seems like you could do that in the event proc where the button is that is

[quoted text clipped - 68 lines]

End Sub

 

[Tina Hudson]

Ok. Still not working. : (

I know the code below doesn't work, but I was hoping you could point out the
errors of my ways!

Thanks for your patience, by the way.

Here's my code for one of the command buttons in the frmReport Menu:

Private Sub cmdActiveByUnit_Click()
On Error GoTo Err_cmdActiveByUnit_Click

Dim stDocName As String
stDocName = "rptMonthlyUnitReport"

DoCmd.OpenForm "frmParamUnit" 'this parameter form is set as popup

If txtStatusField = 0 Then

DoCmd.OpenReport stDocName, acPreview
DoCmd.Maximize

End If

Exit_cmdActiveByUnit_Click:
Exit Sub

Err_cmdActiveByUnit_Click:
MsgBox Err.Description
Resume Exit_cmdActiveByUnit_Click

End Sub



Here's the code for the parameter form for the above button:

Option Compare Database
Option Explicit

Private Sub cmdOk_Click()
'This is used when user enters 1 parameter and wants to open report
Forms!frmReportMenu!txtStatusField = 0
DoCmd.Close acForm, "frmParamUnit"

End Sub


Private Sub cmdCancel_Click()
'This is if user changes mind and doesn't want to open report
Forms!frmReportMenu!txtStatusField = -1

DoCmd.Close acForm, "frmParamUnit", acSaveNo

End Sub

--
Thanks,
Tina Hudson

Sorry about that, I only noticed the part of the earlier note that said 'So,
depending upon which report a user wants, the same parameter form opens.' So
I assumed it was always the same form. I missed the next part that said ' I
have about 4 parameter forms that are associated with about 15 different
reports.

You can still use the hidden field that I mentioned on the reports menu.
This would be used to let you know whether the user clicked the OK or Cancel
button (or whatever you call the buttons). On each parameter form, set that
field to 0 in the OK button's click event, and to -1 in the Cancel button's
click event (you can used whatever values you wnat, really, as long as
they're different)

the format you would use to set the hidden field is:

Forms!frmReportsMenu!txtHiddenFIeld = 0 (or -1 in the other case)

substitute the actual form name and field name above.

I would still open that parameter form from the reports menu rather than the
individual report Open events. On the report menu, In each button click
procedure that is used to open a report, you can open the parm form
(obviously it has to be a Pop-up form, which I think you already have it as),
then right after the OpenForm put

If txtHiddenField = 0 then
DoCmd.OpenReport...
end if

If you do it in each Report's open event, you would have to cancel the Open
event, which generates an error. You would then have to handle that error
thru error handling so that the user doesn't get the error message.

Jim,

I'm not sure I'm following you. But, I'm not always using the same param
form to open a report on the frmReportMenu.

I can get this to work with one parameter form and one report (many, many
thanks!), but not if I have different parameter forms opening up multiple
reports.

In the GetParms function, you have the code opening up just one parameter
form:
DoCmd.OpenForm "frmParamUnit", , , , , acDialog

Sorry to hear its fairly simple, because it's going right over my head. : )

What I suggested won;t require any additional forms and will require no code
at all in any of the reports. I guess you can put code in each report's open

[quoted text clipped - 51 lines]

End Sub

 

[Tina Hudson]

Hey Jim! Now Access (in its infinite wisdom) totally disregards the
frmParamUnit and displays once again it's default parameter dialog box.


Here's my code for rptMenuForm:

Private Sub cmdActiveByUnit_Click()
On Error GoTo Err_cmdActiveByUnit_Click

Dim stDocName As String
stDocName = "rptMonthlyUnitReport"

DoCmd.OpenForm "frmParamUnit", acNormal, , , acFormEdit, acDialog

If txtFieldStatus = 0 Then

DoCmd.OpenReport stDocName, acPreview
DoCmd.Maximize

End If


Exit_cmdActiveByUnit_Click:
Exit Sub

Err_cmdActiveByUnit_Click:
MsgBox Err.Description

Resume Exit_cmdActiveByUnit_Click:

End Sub


And here's my code for frmParamUnit:

Private Sub cmdOk_Click()
On Error GoTo Err_cmdOk_Click

Forms!frmReportMenu!txtFieldStatus = 0

DoCmd.Close acForm, "frmParamUnit"

Exit_cmdOk_Click:
Exit Sub

Err_cmdOk_Click:
MsgBox Err.Description
Resume Exit_cmdOk_Click

End Sub

Private Sub cmdCancel_Click()
On Error GoTo Err_cmdCancel_Click

Forms!frmReportMenu!txtFieldStatus = -1

DoCmd.Close acForm, "frmParamUnit"

Exit_cmdCancel_Click:
Exit Sub

Err_cmdCancel_Click:
MsgBox Err.Description

Resume Exit_cmdCancel_Click

End Sub

Have a great weekend.
--
Thanks,
Tina Hudson

It looks like you'e not opening the form as a pop-up. Even if you set the
form's popup property to Yes, you still have to open the form using the
acDialog parameter...

DoCmd.OpenForm "frmWhatever", acNormal, , , acFormEdit, acDialog

If you don't use the acDialog parm then as soon as you open the form the next
statement executes. In your case, it's checking the value of txtStatusField
as soon as the form is opened, before you've had a chance to do anything on
the popup form. By opening it with acDialog, the next statement doesn't run
until the popup form is closed. Pretty sure that's what's going on.
Everything else looks OK from what I can see.

Ok. Still not working. : (

I know the code below doesn't work, but I was hoping you could point out the
errors of my ways!

Thanks for your patience, by the way.

Here's my code for one of the command buttons in the frmReport Menu:

Private Sub cmdActiveByUnit_Click()
On Error GoTo Err_cmdActiveByUnit_Click

Dim stDocName As String
stDocName = "rptMonthlyUnitReport"

DoCmd.OpenForm "frmParamUnit" 'this parameter form is set as popup

If txtStatusField = 0 Then

DoCmd.OpenReport stDocName, acPreview
DoCmd.Maximize

End If

Exit_cmdActiveByUnit_Click:
Exit Sub

Err_cmdActiveByUnit_Click:
MsgBox Err.Description
Resume Exit_cmdActiveByUnit_Click

End Sub


Here's the code for the parameter form for the above button:

Option Compare Database
Option Explicit

Private Sub cmdOk_Click()
'This is used when user enters 1 parameter and wants to open report
Forms!frmReportMenu!txtStatusField = 0
DoCmd.Close acForm, "frmParamUnit"

End Sub

Private Sub cmdCancel_Click()
'This is if user changes mind and doesn't want to open report
Forms!frmReportMenu!txtStatusField = -1

DoCmd.Close acForm, "frmParamUnit", acSaveNo

End Sub

Sorry about that, I only noticed the part of the earlier note that said 'So,
depending upon which report a user wants, the same parameter form opens.' So

[quoted text clipped - 49 lines]

End Sub

 

[Tina Hudson]

Hey! I tried posting yesterday but for some reason, I keep getting an error
message and can't post.

So, if you are reading this, I was successful.

I made the changes you suggested and everything now works! I just wanted
you to know that I truly appreciate your patience and perseverance. I knew
there was a solution (well, I didn't know what the solution was, just knew
there was one) and again, you guys at Microsoft know your stuff.

Thanks again.


--
Thanks,
Tina Hudson

Well I keep missing things in your earlier explanations. Sorry again. This
time it was this:

The query behind rptMonthlyUnitReport" has the parameter
"=[forms]![frmParamUnit]![cboUnitName]" in the field "UnitName"

In order for the query to use that value the form needs to be open. You are
clicking on the button on the parameter form and then closing the form and
then attempting to open the report. Because of that there is no value for the
query to use, so Access is prompting you for the value. I apologize for not
getting all that earlier - could have had this working a long time ago.

I guess at this point this might be the 'easiest' thing to do (easy being
relative here!), as long as you don't mind making a simple change to each of
the report's queries along with a few more coding changes.

Rather than using the hidden field on the reports menu for a 0/-1 value, use
it to contain the parameter value that was selected. Then in each parm form,
instead of setting the hidden field to 0 if they choose a value and click OK,
set it to the value that was selected, e.g.

[forms]![frmReportMenu]![txtStatusFIeld] = cboUnitName (or whatever the
form field name is)

If they click cancel, set the hidden field to vbNullString (VB's name for "")

In each query replace [forms]![frmParamUnit]![cboUnitName] (or whatever the
appropriate value is for that particular query/parms form combination) with
[forms]![frmReportMenu]![txtStatusFIeld]

In the report button's click events, rather than checking for a value of 0 to
open the report, check for anything but vbNullString, e.g.

If txtStatusFIeld <> vbNullString Then
DoCmd.OpenReport.....

Even with the way you had it set up originally you would have had to make
quite a few changes. Unfortunately I added to the work by missing some of
your original intentions. I hope I haven't missed anything else! I think I
now have a complete understanding of the process.

Hey Jim! Now Access (in its infinite wisdom) totally disregards the
frmParamUnit and displays once again it's default parameter dialog box.

Here's my code for rptMenuForm:

Private Sub cmdActiveByUnit_Click()
On Error GoTo Err_cmdActiveByUnit_Click

Dim stDocName As String
stDocName = "rptMonthlyUnitReport"

DoCmd.OpenForm "frmParamUnit", acNormal, , , acFormEdit, acDialog

If txtFieldStatus = 0 Then

DoCmd.OpenReport stDocName, acPreview
DoCmd.Maximize

End If


Exit_cmdActiveByUnit_Click:
Exit Sub

Err_cmdActiveByUnit_Click:
MsgBox Err.Description

Resume Exit_cmdActiveByUnit_Click:

End Sub

And here's my code for frmParamUnit:

Private Sub cmdOk_Click()
On Error GoTo Err_cmdOk_Click

Forms!frmReportMenu!txtFieldStatus = 0

DoCmd.Close acForm, "frmParamUnit"

Exit_cmdOk_Click:
Exit Sub

Err_cmdOk_Click:
MsgBox Err.Description
Resume Exit_cmdOk_Click

End Sub

Private Sub cmdCancel_Click()
On Error GoTo Err_cmdCancel_Click

Forms!frmReportMenu!txtFieldStatus = -1

DoCmd.Close acForm, "frmParamUnit"

Exit_cmdCancel_Click:
Exit Sub

Err_cmdCancel_Click:
MsgBox Err.Description

Resume Exit_cmdCancel_Click

End Sub

Have a great weekend.

It looks like you'e not opening the form as a pop-up. Even if you set the
form's popup property to Yes, you still have to open the form using the

[quoted text clipped - 68 lines]

End Sub