Clear Filter from Report after use

  • Thread starter Thread starter TigerTechie
  • Start date Start date
T

TigerTechie

Hello,

I have a command button [BUTTON B] in a form linked to call a report up and
email it based on the current record of the Form.

I also have another command button [BUTTON A] linked to the same report to
email ALL the records to the respective recipients, based on data within the
report.

The problem is this: When I run BUTTON B, and email the report to one
specific person based on the criteria, all runs well. If I attempt to run
BUTTON A after I have run BUTTON B, no information populates to the report.

I have isolated the problem to the Filter I am passing in the code on BUTTON
B. After BUTTON B runs, the Filter criteria passed from the form to the
report, remains in the report, and I am having a problem clearing it out.

I have posted my code below. I have also tried putting the following in the
Close Command of the report being called. This has not worked:

Private Sub Report_Close()

Me.Filter = ""
Me.FilterOn = False


End Sub

Any help would be appreciated. Here is the code for the [BUTTON B] Command:

========================================
Private Sub EmailSecondApprover_Click()
On Error GoTo Err_EmailSecondApprover_Click

Dim strFilter As String

'Debug.Print strSQL

If Me.[SecEmail] = "" Then

MsgBox "There is no secondary approver. Please forward the email to an" _
& " alternate AP Supervisor with the appropriate level of SOA Authority.
Thank You.", vbOKOnly, "No Secondary Approver"

End If

strFilter = "[AcctNum] = " & "'" & Me!AcctNum & "'"

DoCmd.OpenReport "1-BILLS TO REVIEW REPORT", acPreview, , strFilter,
acHidden

DoCmd.SendObject acSendReport, "1-BILLS TO REVIEW REPORT", acFormatRTF _
, SecEmail _
, _
, _
, "Utility Bill Needs Your Approval for Payment" _
, "Please review the attached file and follow any additional
instructions noted to approve this utility bill. " & _
"Send your approval email with the attached report back to
(e-mail address removed) so the bill may be processed. " & _
"Please remember that utility bills must be paid as soon as possible to
prevent late fees and disconnects. Thank you for your cooperation." _
, -1

DoCmd.Close acReport, "1-BILLS TO REVIEW REPORT", acSaveNo


Exit_EmailSecondApprover_Click:
Exit Sub

Err_EmailSecondApprover_Click:
MsgBox Err.Description
Resume Exit_EmailSecondApprover_Click

End Sub

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

Thanks to whomever can help me with this issue.
 
You should not have to clear the filter. The important thing is to ensure
the report is not already open. If it is open (even in design view), then
opening it again won't set its filter properly.

What I do is to use a little function called OpenTheReport() instead of
DoCmd.OpenReport. The function checks if the report is already open, and
closes it. It also handles the error 2501 that occurs if you cancel the
report (or if the NoData event does.) The function's return value lets you
know if the report opened or not if you want to know. The function is
basically this one:
http://allenbrowne.com/AppPrintMgtCode.html#OpenTheReport
but you can omit the line that does extra work you don't need:
Call SetupPrinter4Report(strDoc, strErrMsg)

There may also be a problem with checking the value of SecEmail. Null is not
the same as a zero-length string, so you may need to test for both:
If Me.[SecEmail] = "" OR IsNull(Me.SecEmail) Then

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

TigerTechie said:
I have a command button [BUTTON B] in a form linked to call a report up
and
email it based on the current record of the Form.

I also have another command button [BUTTON A] linked to the same report to
email ALL the records to the respective recipients, based on data within
the
report.

The problem is this: When I run BUTTON B, and email the report to one
specific person based on the criteria, all runs well. If I attempt to run
BUTTON A after I have run BUTTON B, no information populates to the
report.

I have isolated the problem to the Filter I am passing in the code on
BUTTON
B. After BUTTON B runs, the Filter criteria passed from the form to the
report, remains in the report, and I am having a problem clearing it out.

I have posted my code below. I have also tried putting the following in
the
Close Command of the report being called. This has not worked:

Private Sub Report_Close()

Me.Filter = ""
Me.FilterOn = False


End Sub

Any help would be appreciated. Here is the code for the [BUTTON B]
Command:

========================================
Private Sub EmailSecondApprover_Click()
On Error GoTo Err_EmailSecondApprover_Click

Dim strFilter As String

'Debug.Print strSQL

If Me.[SecEmail] = "" Then

MsgBox "There is no secondary approver. Please forward the email to an"
_
& " alternate AP Supervisor with the appropriate level of SOA
Authority.
Thank You.", vbOKOnly, "No Secondary Approver"

End If

strFilter = "[AcctNum] = " & "'" & Me!AcctNum & "'"

DoCmd.OpenReport "1-BILLS TO REVIEW REPORT", acPreview, , strFilter,
acHidden

DoCmd.SendObject acSendReport, "1-BILLS TO REVIEW REPORT", acFormatRTF
_
, SecEmail _
, _
, _
, "Utility Bill Needs Your Approval for Payment" _
, "Please review the attached file and follow any additional
instructions noted to approve this utility bill. " & _
"Send your approval email with the attached report back to
(e-mail address removed) so the bill may be processed. " & _
"Please remember that utility bills must be paid as soon as possible to
prevent late fees and disconnects. Thank you for your cooperation." _
, -1

DoCmd.Close acReport, "1-BILLS TO REVIEW REPORT", acSaveNo


Exit_EmailSecondApprover_Click:
Exit Sub

Err_EmailSecondApprover_Click:
MsgBox Err.Description
Resume Exit_EmailSecondApprover_Click

End Sub

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

Thanks to whomever can help me with this issue.
Click to expand...
 
--
-Tiger


Allen Browne said:
You should not have to clear the filter. The important thing is to ensure
the report is not already open. If it is open (even in design view), then
opening it again won't set its filter properly.

What I do is to use a little function called OpenTheReport() instead of
DoCmd.OpenReport. The function checks if the report is already open, and
closes it. It also handles the error 2501 that occurs if you cancel the
report (or if the NoData event does.) The function's return value lets you
know if the report opened or not if you want to know. The function is
basically this one:
http://allenbrowne.com/AppPrintMgtCode.html#OpenTheReport
but you can omit the line that does extra work you don't need:
Call SetupPrinter4Report(strDoc, strErrMsg)

There may also be a problem with checking the value of SecEmail. Null is not
the same as a zero-length string, so you may need to test for both:
If Me.[SecEmail] = "" OR IsNull(Me.SecEmail) Then

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

TigerTechie said:
I have a command button [BUTTON B] in a form linked to call a report up
and
email it based on the current record of the Form.

I also have another command button [BUTTON A] linked to the same report to
email ALL the records to the respective recipients, based on data within
the
report.

The problem is this: When I run BUTTON B, and email the report to one
specific person based on the criteria, all runs well. If I attempt to run
BUTTON A after I have run BUTTON B, no information populates to the
report.

I have isolated the problem to the Filter I am passing in the code on
BUTTON
B. After BUTTON B runs, the Filter criteria passed from the form to the
report, remains in the report, and I am having a problem clearing it out.

I have posted my code below. I have also tried putting the following in
the
Close Command of the report being called. This has not worked:

Private Sub Report_Close()

Me.Filter = ""
Me.FilterOn = False


End Sub

Any help would be appreciated. Here is the code for the [BUTTON B]
Command:

========================================
Private Sub EmailSecondApprover_Click()
On Error GoTo Err_EmailSecondApprover_Click

Dim strFilter As String

'Debug.Print strSQL

If Me.[SecEmail] = "" Then

MsgBox "There is no secondary approver. Please forward the email to an"
_
& " alternate AP Supervisor with the appropriate level of SOA
Authority.
Thank You.", vbOKOnly, "No Secondary Approver"

End If

strFilter = "[AcctNum] = " & "'" & Me!AcctNum & "'"

DoCmd.OpenReport "1-BILLS TO REVIEW REPORT", acPreview, , strFilter,
acHidden

DoCmd.SendObject acSendReport, "1-BILLS TO REVIEW REPORT", acFormatRTF
_
, SecEmail _
, _
, _
, "Utility Bill Needs Your Approval for Payment" _
, "Please review the attached file and follow any additional
instructions noted to approve this utility bill. " & _
"Send your approval email with the attached report back to
(e-mail address removed) so the bill may be processed. " & _
"Please remember that utility bills must be paid as soon as possible to
prevent late fees and disconnects. Thank you for your cooperation." _
, -1

DoCmd.Close acReport, "1-BILLS TO REVIEW REPORT", acSaveNo


Exit_EmailSecondApprover_Click:
Exit Sub

Err_EmailSecondApprover_Click:
MsgBox Err.Description
Resume Exit_EmailSecondApprover_Click

End Sub

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

Thanks to whomever can help me with this issue.
Click to expand...
Click to expand...
 
Allen,

Couple Questions:

1. Where do I put this function so that it will work? I tried putting it
inside my sub, commenting out the DoCmd.OpenReport, but it would not work for
some reason.

2. What is the proper syntax I should be using for the strWhere variable?
I tried using the "AcctNum = '" & Me!AcctNum & "'" as the definition, but it
did not work. Gave me an error.

Thanks Again!
--
-Tiger
Allen Browne said:
You should not have to clear the filter. The important thing is to ensure
the report is not already open. If it is open (even in design view), then
opening it again won't set its filter properly.

What I do is to use a little function called OpenTheReport() instead of
DoCmd.OpenReport. The function checks if the report is already open, and
closes it. It also handles the error 2501 that occurs if you cancel the
report (or if the NoData event does.) The function's return value lets you
know if the report opened or not if you want to know. The function is
basically this one:
http://allenbrowne.com/AppPrintMgtCode.html#OpenTheReport
but you can omit the line that does extra work you don't need:
Call SetupPrinter4Report(strDoc, strErrMsg)

There may also be a problem with checking the value of SecEmail. Null is not
the same as a zero-length string, so you may need to test for both:
If Me.[SecEmail] = "" OR IsNull(Me.SecEmail) Then

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

TigerTechie said:
I have a command button [BUTTON B] in a form linked to call a report up
and
email it based on the current record of the Form.

I also have another command button [BUTTON A] linked to the same report to
email ALL the records to the respective recipients, based on data within
the
report.

The problem is this: When I run BUTTON B, and email the report to one
specific person based on the criteria, all runs well. If I attempt to run
BUTTON A after I have run BUTTON B, no information populates to the
report.

I have isolated the problem to the Filter I am passing in the code on
BUTTON
B. After BUTTON B runs, the Filter criteria passed from the form to the
report, remains in the report, and I am having a problem clearing it out.

I have posted my code below. I have also tried putting the following in
the
Close Command of the report being called. This has not worked:

Private Sub Report_Close()

Me.Filter = ""
Me.FilterOn = False


End Sub

Any help would be appreciated. Here is the code for the [BUTTON B]
Command:

========================================
Private Sub EmailSecondApprover_Click()
On Error GoTo Err_EmailSecondApprover_Click

Dim strFilter As String

'Debug.Print strSQL

If Me.[SecEmail] = "" Then

MsgBox "There is no secondary approver. Please forward the email to an"
_
& " alternate AP Supervisor with the appropriate level of SOA
Authority.
Thank You.", vbOKOnly, "No Secondary Approver"

End If

strFilter = "[AcctNum] = " & "'" & Me!AcctNum & "'"

DoCmd.OpenReport "1-BILLS TO REVIEW REPORT", acPreview, , strFilter,
acHidden

DoCmd.SendObject acSendReport, "1-BILLS TO REVIEW REPORT", acFormatRTF
_
, SecEmail _
, _
, _
, "Utility Bill Needs Your Approval for Payment" _
, "Please review the attached file and follow any additional
instructions noted to approve this utility bill. " & _
"Send your approval email with the attached report back to
(e-mail address removed) so the bill may be processed. " & _
"Please remember that utility bills must be paid as soon as possible to
prevent late fees and disconnects. Thank you for your cooperation." _
, -1

DoCmd.Close acReport, "1-BILLS TO REVIEW REPORT", acSaveNo


Exit_EmailSecondApprover_Click:
Exit Sub

Err_EmailSecondApprover_Click:
MsgBox Err.Description
Resume Exit_EmailSecondApprover_Click

End Sub

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

Thanks to whomever can help me with this issue.
Click to expand...
Click to expand...
Click to expand...
 
1. Where do I put this function so that it will work? I tried putting it
inside my sub, commenting out the DoCmd.OpenReport, but it would not work for
some reason.
Click to expand...

Try importing the code into a new standard module (not a class module or one
associated with a form). Compile and save before running anything.

2. What is the proper syntax I should be using for the strWhere variable?
I tried using the "AcctNum = '" & Me!AcctNum & "'" as the definition, but it
did not work. Gave me an error.
Click to expand...

Type mismatch error??
The syntax you have is correct for a string datatype, however numbers can't
have enclosing quotes, else they are read as a string. Try this instead:

"AcctNum = " & Me!AcctNum


If you are still getting errors post back with the error number and
descriptions.

hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



TigerTechie said:
Allen,

Couple Questions:

1. Where do I put this function so that it will work? I tried putting it
inside my sub, commenting out the DoCmd.OpenReport, but it would not work for
some reason.

2. What is the proper syntax I should be using for the strWhere variable?
I tried using the "AcctNum = '" & Me!AcctNum & "'" as the definition, but it
did not work. Gave me an error.

Thanks Again!
--
-Tiger
Allen Browne said:
You should not have to clear the filter. The important thing is to ensure
the report is not already open. If it is open (even in design view), then
opening it again won't set its filter properly.

What I do is to use a little function called OpenTheReport() instead of
DoCmd.OpenReport. The function checks if the report is already open, and
closes it. It also handles the error 2501 that occurs if you cancel the
report (or if the NoData event does.) The function's return value lets you
know if the report opened or not if you want to know. The function is
basically this one:
http://allenbrowne.com/AppPrintMgtCode.html#OpenTheReport
but you can omit the line that does extra work you don't need:
Call SetupPrinter4Report(strDoc, strErrMsg)

There may also be a problem with checking the value of SecEmail. Null is not
the same as a zero-length string, so you may need to test for both:
If Me.[SecEmail] = "" OR IsNull(Me.SecEmail) Then

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I have a command button [BUTTON B] in a form linked to call a report up
and
email it based on the current record of the Form.

I also have another command button [BUTTON A] linked to the same report to
email ALL the records to the respective recipients, based on data within
the
report.

The problem is this: When I run BUTTON B, and email the report to one
specific person based on the criteria, all runs well. If I attempt to run
BUTTON A after I have run BUTTON B, no information populates to the
report.

I have isolated the problem to the Filter I am passing in the code on
BUTTON
B. After BUTTON B runs, the Filter criteria passed from the form to the
report, remains in the report, and I am having a problem clearing it out.

I have posted my code below. I have also tried putting the following in
the
Close Command of the report being called. This has not worked:

Private Sub Report_Close()

Me.Filter = ""
Me.FilterOn = False


End Sub

Any help would be appreciated. Here is the code for the [BUTTON B]
Command:

========================================
Private Sub EmailSecondApprover_Click()
On Error GoTo Err_EmailSecondApprover_Click

Dim strFilter As String

'Debug.Print strSQL

If Me.[SecEmail] = "" Then

MsgBox "There is no secondary approver. Please forward the email to an"
_
& " alternate AP Supervisor with the appropriate level of SOA
Authority.
Thank You.", vbOKOnly, "No Secondary Approver"

End If

strFilter = "[AcctNum] = " & "'" & Me!AcctNum & "'"

DoCmd.OpenReport "1-BILLS TO REVIEW REPORT", acPreview, , strFilter,
acHidden

DoCmd.SendObject acSendReport, "1-BILLS TO REVIEW REPORT", acFormatRTF
_
, SecEmail _
, _
, _
, "Utility Bill Needs Your Approval for Payment" _
, "Please review the attached file and follow any additional
instructions noted to approve this utility bill. " & _
"Send your approval email with the attached report back to
(e-mail address removed) so the bill may be processed. " & _
"Please remember that utility bills must be paid as soon as possible to
prevent late fees and disconnects. Thank you for your cooperation." _
, -1

DoCmd.Close acReport, "1-BILLS TO REVIEW REPORT", acSaveNo


Exit_EmailSecondApprover_Click:
Exit Sub

Err_EmailSecondApprover_Click:
MsgBox Err.Description
Resume Exit_EmailSecondApprover_Click

End Sub

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

Thanks to whomever can help me with this issue.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top