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



--
Thanks,
Tina Hudson

On Mon, 13 Jul 2009 14:29:01 -0700, Tina Hudson wrote:

> 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

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

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


"JimBurke via AccessMonster.com" wrote:

> 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
>
> Tina Hudson wrote:
> >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
> >
>
> --
> Jim Burke
>
> Message posted via http://www.accessmonster.com
>
>

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


"fredg" wrote:

> On Mon, 13 Jul 2009 14:29:01 -0700, Tina Hudson wrote:
>
> > 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
>
> --
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail
>

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


"JimBurke via AccessMonster.com" wrote:

> 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.
>
>
> Tina Hudson wrote:
> >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
>
> --
> Jim Burke
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/200907/1
>
>

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


"JimBurke via AccessMonster.com" wrote:

> 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.
>
> Tina Hudson wrote:
> >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
>
> --
> Jim Burke
>
> Message posted via http://www.accessmonster.com
>
>

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


"JimBurke via AccessMonster.com" wrote:

> 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.
>
> Tina Hudson wrote:
> >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
>
> --
> Jim Burke
>
> Message posted via http://www.accessmonster.com
>
>

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


"JimBurke via AccessMonster.com" wrote:

> 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.
>
> Tina Hudson wrote:
> >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
>
> --
> Jim Burke
>
> Message posted via http://www.accessmonster.com
>
>