Display report if meets condition

[MarieM via AccessMonster.com]

Hi,

I have a report which is run from a button on a form.
The report contains a subreport which has a calcualted field displaying a
total of all items on the subreport.
I have created a macro and attached it to the On Format property of the
report detail section to check if the total exceeds 5000. If the the total
exceeds 5000, a message box displays.
I would then like the report to close the report and return to the form so
the user can make adjustments. However, the Close action Halts the macro.

Any ideas would be appreciated as to how to make this work.

TIA,
Marie

 

[Al Campagna]

Marie,
One method would be to interrogate the report's subreport table before
the report is opened, and notify the user if DSum > 5000, and cancel the
report opening.
When the user clicks the report button, you could do a DSum against that
table, and make the decision...
For example... use your own field control names... and use the proper
syntax for your fields types...
If DSum("[Paid']","tblYourTable", "SomeField = " & SomeField)>5000 Then
MsgBox "Over 5000"
Exit Sub
Else
DoCmd.OpenReport "rptYourReport"
End Sub
--
hth
Al Campagna
Access MVP 2007
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love, and you'll never work a day in your life."

 

[MarieM via AccessMonster.com]

Hi Al,

Thank you for your suggestion - it worked like a charm!
However, now when I cancel the report I see the OpenReport was cancelled msg.
I incorporated some error handling code, but the msg is still appearing.
Could you take a look at my code (listed below) and point out what I am doing
wrong.

Private Sub Report_Open(Cancel As Integer)
On Error GoTo OpenReport_Err

If DSum("[ExtendedPrice]", "qryPODetailssubrpt", "PONumber = PONumber") >
5000 Then
MsgBox "The PO Total must not exceed $5000."
Cancel = True
'On Error Resume Next
Exit Sub
Else
DoCmd.OpenReport "rptPurchaseOrder", acViewPreview
DoCmd.Maximize
End If

OpenReport_End:
Exit Sub

OpenReport_Err:
If Err.Number = 2501 Then
Resume OpenReport_End
Else
MsgBox Err.Number & " " & Err.Description
Resume OpenReport_End
End If

End Sub

Thank you for your help!
Marie

Marie,
One method would be to interrogate the report's subreport table before
the report is opened, and notify the user if DSum > 5000, and cancel the
report opening.
When the user clicks the report button, you could do a DSum against that
table, and make the decision...
For example... use your own field control names... and use the proper
syntax for your fields types...
If DSum("[Paid']","tblYourTable", "SomeField = " & SomeField)>5000 Then
MsgBox "Over 5000"
Exit Sub
Else
DoCmd.OpenReport "rptYourReport"
End Sub

Hi,

[quoted text clipped - 11 lines]

TIA,
Marie

 

[Al Campagna]

Marie,
I don't understand that... if POTotal > 5000, just post the MsgBox
message, and then immediately Exit the sub.
That code never attempts to open the report, so...
Cancel = True (is not needed)

If DSum("[ExtendedPrice]", "qryPODetailssubrpt", "PONumber = PONumber")

5000 Then

MsgBox "The PO Total must not exceed $5000."
Exit Sub

Also, remove any Error Handling while we test... we'll keep it simple
for now.

5000 = don't open the report

< 5000 = open the report
--
hth
Al Campagna
Access MVP 2007
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love, and you'll never work a day in your life."

Hi Al,

Thank you for your suggestion - it worked like a charm!
However, now when I cancel the report I see the OpenReport was cancelled
msg.
I incorporated some error handling code, but the msg is still appearing.
Could you take a look at my code (listed below) and point out what I am
doing
wrong.

Private Sub Report_Open(Cancel As Integer)
On Error GoTo OpenReport_Err

If DSum("[ExtendedPrice]", "qryPODetailssubrpt", "PONumber = PONumber")5000 Then
MsgBox "The PO Total must not exceed $5000."
Cancel = True
'On Error Resume Next
Exit Sub
Else
DoCmd.OpenReport "rptPurchaseOrder", acViewPreview
DoCmd.Maximize
End If

OpenReport_End:
Exit Sub

OpenReport_Err:
If Err.Number = 2501 Then
Resume OpenReport_End
Else
MsgBox Err.Number & " " & Err.Description
Resume OpenReport_End
End If

End Sub

Thank you for your help!
Marie

Marie,
One method would be to interrogate the report's subreport table before
the report is opened, and notify the user if DSum > 5000, and cancel the
report opening.
When the user clicks the report button, you could do a DSum against
that
table, and make the decision...
For example... use your own field control names... and use the proper
syntax for your fields types...
If DSum("[Paid']","tblYourTable", "SomeField = " & SomeField)>5000 Then
MsgBox "Over 5000"
Exit Sub
Else
DoCmd.OpenReport "rptYourReport"
End Sub

Hi,

[quoted text clipped - 11 lines]

TIA,
Marie

 

[MarieM via AccessMonster.com]

Al,

Thanks again for your help - I was trying to make it too complicated!
Marie

Marie,
I don't understand that... if POTotal > 5000, just post the MsgBox
message, and then immediately Exit the sub.
That code never attempts to open the report, so...
Cancel = True (is not needed)

If DSum("[ExtendedPrice]", "qryPODetailssubrpt", "PONumber = PONumber")

5000 Then

MsgBox "The PO Total must not exceed $5000."
Exit Sub

Also, remove any Error Handling while we test... we'll keep it simple
for now.

5000 = don't open the report < 5000 = open the report
Hi Al,

[quoted text clipped - 57 lines]