Error 2485 - Can't Find Macro

  • Thread starter Thread starter Rob Parker
  • Start date Start date
R

Rob Parker

I'm trying to use the technique described in KB299016 to dynamically filter
a report prior to using SendObject. This includes the following
instruction:
<quote>
Set the OnOpen property of the report to the following event procedure:
Me.Filter = strFilter
<endquote>
(Well, actually, the KB article uses strInvoiceWhere, but I'm not dealing
with Invoices.)

strFilter is a Public string variable, defined in the code module for the
form containing my command button to run the SendObject code.

When I run the code, I get Error 2485 "Microsoft Access can't find the macro
'Me.'". If I change the entry in the OnOpen property to:
Filter = strFilter
I get Error 2485 "Microsoft Access can't find the macro 'Filter =
strFilter'".

A search of the Access newsgroups revealed KB209582, which includes this
error being generated by space characters in an event entry; however,
removing the spaces in the entry has no effect - I still get the same error.

Any fix/ideas/suggestions gladly received.

TIA,

Rob

PS. For completeness, my code (direct cut/paste) is as follows:

Option Compare Database
Option Explicit

Public strFilter As String

Private Sub cmdSnpReports_Click()
Dim rst As DAO.Recordset
Dim strSQL As String

On Error GoTo cmdSnpReports_Click_Error

strSQL = "SELECT DISTINCT PersonName FROM tblEmployeeReviews;"

Set rst = CurrentDb.OpenRecordset(strSQL)
rst.MoveFirst
Do While Not rst.EOF
strFilter = "PersonName = '" & rst!PersonName & "'"
DoCmd.SendObject acSendReport, "rptEmployeeReviews", acFormatSNP,
rst!PersonName
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

ExitPoint:
Exit Sub

cmdSnpReports_Click_Error:
Select Case Err.Number
Case 2501
Resume Next
Case Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ")" &
vbNewLine _
& "in procedure cmdSnpReports_Click" & vbNewLine _
& "of VBA Document Form_frmVariousActions"
End Select
Resume ExitPoint
Resume 'set this line as next statement, when debugging after Ctrl-Brk,
to find error line

End Sub
 
Set the report's On Open property to:
[Event Procedure]

Then click the Build button (...) beside the property.
Access opens the code window.
The line of code goes in there, between the "Private Sub..." and "End Sub"
lines.
 
Thanks for the quick reply, Allen.

I'd originally done that, but it gave a compile error ("variable not
defined" for strFilter - but that's probably understandable, since it's not
a local variable in the report, and when the report is opened it will be
available as it's public in the form's module), so I tried the alternative I
showed in my first post.

However, I've just tried it again, and my code fails to produce any messages
containing the .snp file(s). I've changed the SendObject line to:
DoCmd.SendObject acSendReport, "rptEmployeeReviews", acFormatSNP, , , ,
"Report for " & rst!PersonName, , True
so that the message(s) should simply be displayed in my email program, but
nothing happens.

I've set a breakpoint on the Do While Not rst.EOF line, and stepped through
the code. The code steps fine until the first DoCmd.SendObject statement,
then finishes. No error, nothing. I've set an error trap in the Open event
of the report, and there's also no error there. If I comment out the
Me.Filter = strFilter line in the report's code, then the code loops
correctly, and the report (unfiltered - I removed the previous string from
the report's Filter property) is generated for each person, and displays in
my email program.

Any suggestions?

Rob


Allen Browne said:
Set the report's On Open property to:
[Event Procedure]

Then click the Build button (...) beside the property.
Access opens the code window.
The line of code goes in there, between the "Private Sub..." and "End Sub"
lines.

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

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

message
I'm trying to use the technique described in KB299016 to dynamically
filter a report prior to using SendObject. This includes the following
instruction:
<quote>
Set the OnOpen property of the report to the following event procedure:
Me.Filter = strFilter
<endquote>
(Well, actually, the KB article uses strInvoiceWhere, but I'm not dealing
with Invoices.)

strFilter is a Public string variable, defined in the code module for the
form containing my command button to run the SendObject code.

When I run the code, I get Error 2485 "Microsoft Access can't find the
macro 'Me.'". If I change the entry in the OnOpen property to:
Filter = strFilter
I get Error 2485 "Microsoft Access can't find the macro 'Filter =
strFilter'".

A search of the Access newsgroups revealed KB209582, which includes this
error being generated by space characters in an event entry; however,
removing the spaces in the entry has no effect - I still get the same
error.

Any fix/ideas/suggestions gladly received.

TIA,

Rob

PS. For completeness, my code (direct cut/paste) is as follows:

Option Compare Database
Option Explicit

Public strFilter As String

Private Sub cmdSnpReports_Click()
Dim rst As DAO.Recordset
Dim strSQL As String

On Error GoTo cmdSnpReports_Click_Error

strSQL = "SELECT DISTINCT PersonName FROM tblEmployeeReviews;"

Set rst = CurrentDb.OpenRecordset(strSQL)
rst.MoveFirst
Do While Not rst.EOF
strFilter = "PersonName = '" & rst!PersonName & "'"
DoCmd.SendObject acSendReport, "rptEmployeeReviews", acFormatSNP,
rst!PersonName
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

ExitPoint:
Exit Sub

cmdSnpReports_Click_Error:
Select Case Err.Number
Case 2501
Resume Next
Case Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ")" &
vbNewLine _
& "in procedure cmdSnpReports_Click" & vbNewLine _
& "of VBA Document Form_frmVariousActions"
End Select
Resume ExitPoint
Resume 'set this line as next statement, when debugging after Ctrl-Brk,
to find error line

End Sub
Click to expand...
Click to expand...
 
I would expect a message if the SendObject failed.
And I would expect a window to open if it succeeds.
Not sure what's going on.

It might be worth checking if the error message is being suppressed
somewhere. For example, in the code window, choose Tools | Options, and make
sure Error Trapping is set to:
Break on Unhandled Errors
Then temporarily comment out any error handler in your code.

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

Reply to group, rather than allenbrowne at mvps dot org.
Rob Parker said:
Thanks for the quick reply, Allen.

I'd originally done that, but it gave a compile error ("variable not
defined" for strFilter - but that's probably understandable, since it's
not a local variable in the report, and when the report is opened it will
be available as it's public in the form's module), so I tried the
alternative I showed in my first post.

However, I've just tried it again, and my code fails to produce any
messages containing the .snp file(s). I've changed the SendObject line
to:
DoCmd.SendObject acSendReport, "rptEmployeeReviews", acFormatSNP, , , ,
"Report for " & rst!PersonName, , True
so that the message(s) should simply be displayed in my email program, but
nothing happens.

I've set a breakpoint on the Do While Not rst.EOF line, and stepped
through the code. The code steps fine until the first DoCmd.SendObject
statement, then finishes. No error, nothing. I've set an error trap in
the Open event of the report, and there's also no error there. If I
comment out the Me.Filter = strFilter line in the report's code, then the
code loops correctly, and the report (unfiltered - I removed the previous
string from the report's Filter property) is generated for each person,
and displays in my email program.

Any suggestions?

Rob


Allen Browne said:
Set the report's On Open property to:
[Event Procedure]

Then click the Build button (...) beside the property.
Access opens the code window.
The line of code goes in there, between the "Private Sub..." and "End
Sub" lines.

message
I'm trying to use the technique described in KB299016 to dynamically
filter a report prior to using SendObject. This includes the following
instruction:
<quote>
Set the OnOpen property of the report to the following event procedure:
Me.Filter = strFilter
<endquote>
(Well, actually, the KB article uses strInvoiceWhere, but I'm not
dealing with Invoices.)

strFilter is a Public string variable, defined in the code module for
the form containing my command button to run the SendObject code.

When I run the code, I get Error 2485 "Microsoft Access can't find the
macro 'Me.'". If I change the entry in the OnOpen property to:
Filter = strFilter
I get Error 2485 "Microsoft Access can't find the macro 'Filter =
strFilter'".

A search of the Access newsgroups revealed KB209582, which includes this
error being generated by space characters in an event entry; however,
removing the spaces in the entry has no effect - I still get the same
error.

Any fix/ideas/suggestions gladly received.

TIA,

Rob

PS. For completeness, my code (direct cut/paste) is as follows:

Option Compare Database
Option Explicit

Public strFilter As String

Private Sub cmdSnpReports_Click()
Dim rst As DAO.Recordset
Dim strSQL As String

On Error GoTo cmdSnpReports_Click_Error

strSQL = "SELECT DISTINCT PersonName FROM tblEmployeeReviews;"

Set rst = CurrentDb.OpenRecordset(strSQL)
rst.MoveFirst
Do While Not rst.EOF
strFilter = "PersonName = '" & rst!PersonName & "'"
DoCmd.SendObject acSendReport, "rptEmployeeReviews", acFormatSNP,
rst!PersonName
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

ExitPoint:
Exit Sub

cmdSnpReports_Click_Error:
Select Case Err.Number
Case 2501
Resume Next
Case Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ")" &
vbNewLine _
& "in procedure cmdSnpReports_Click" & vbNewLine _
& "of VBA Document Form_frmVariousActions"
End Select
Resume ExitPoint
Resume 'set this line as next statement, when debugging after
Ctrl-Brk, to find error line

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Thanks again Allen,

I agree entirely with what you say in your first paragraph. I've no idea
what's going on, and it seems so simple.

I've created a new database, imported the table from my previous mdb file,
and recreated the form and report from scratch, just in case this was a
corruption problem. I have not added any error handling code, and the VBA
editor is set to Break on All Errors. I'm still experiencing exactly the
same behaviour as I reported in my last post.

I've added debug.print statements in both subs, to display the value of
strFilter. In the Do loop, the first value is as I expect. However, with
only the Debug.Print statement in the report's Open event code, nothing is
printed - not even the text string in my debug.print statement. And the
form's code does not loop. It seems that the report does not open, and no
error is thrown. I've put a messagebox in the report's Error event, but
it's also not triggered.

The one thing which I do notice now (and on careful examination, it was
happening in the original database) is that a small window flickers briefly:
it is titled "Printing", and contains a message that the report is being
output to a mail message; it also contains a Cancel button. However, the
mail item does not open. And the window only appears once, not once for
each expected pass through the loop; the debug statement shows that the loop
only executes once. As before, the DoCmd.SendObject line works as expected
when the report is not filtered.

The problem appears to be related to the report trying to use strFilter,
either via
Me.Filter = strFilter
or via
Debug.Print "rptEmployeeReviews: strFilter = """ & strFilter & """"

My understanding is that the Public declaration of strFilter in the form's
code module should allow that variable to be accessed from other objects
while the form is open. Is this incorrect? It certainly seems to be the
implication in the KB299016 article, where this technique is described.

I've tried adding DoEvents statements, both before and after the
DoCmd.SendObject, and in the report's Open event, in case it was a timing
problem, but that has no effect.

What next? I've got no idea what to try. In case it helps, I'm running
Access 2002 SP3 under Windows XP Pro.

Rob


Allen Browne said:
I would expect a message if the SendObject failed.
And I would expect a window to open if it succeeds.
Not sure what's going on.

It might be worth checking if the error message is being suppressed
somewhere. For example, in the code window, choose Tools | Options, and
make sure Error Trapping is set to:
Break on Unhandled Errors
Then temporarily comment out any error handler in your code.

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

Reply to group, rather than allenbrowne at mvps dot org.
Rob Parker said:
Thanks for the quick reply, Allen.

I'd originally done that, but it gave a compile error ("variable not
defined" for strFilter - but that's probably understandable, since it's
not a local variable in the report, and when the report is opened it will
be available as it's public in the form's module), so I tried the
alternative I showed in my first post.

However, I've just tried it again, and my code fails to produce any
messages containing the .snp file(s). I've changed the SendObject line
to:
DoCmd.SendObject acSendReport, "rptEmployeeReviews", acFormatSNP, , ,
, "Report for " & rst!PersonName, , True
so that the message(s) should simply be displayed in my email program,
but nothing happens.

I've set a breakpoint on the Do While Not rst.EOF line, and stepped
through the code. The code steps fine until the first DoCmd.SendObject
statement, then finishes. No error, nothing. I've set an error trap in
the Open event of the report, and there's also no error there. If I
comment out the Me.Filter = strFilter line in the report's code, then the
code loops correctly, and the report (unfiltered - I removed the previous
string from the report's Filter property) is generated for each person,
and displays in my email program.

Any suggestions?

Rob


Allen Browne said:
Set the report's On Open property to:
[Event Procedure]

Then click the Build button (...) beside the property.
Access opens the code window.
The line of code goes in there, between the "Private Sub..." and "End
Sub" lines.

message
I'm trying to use the technique described in KB299016 to dynamically
filter a report prior to using SendObject. This includes the following
instruction:
<quote>
Set the OnOpen property of the report to the following event procedure:
Me.Filter = strFilter
<endquote>
(Well, actually, the KB article uses strInvoiceWhere, but I'm not
dealing with Invoices.)

strFilter is a Public string variable, defined in the code module for
the form containing my command button to run the SendObject code.

When I run the code, I get Error 2485 "Microsoft Access can't find the
macro 'Me.'". If I change the entry in the OnOpen property to:
Filter = strFilter
I get Error 2485 "Microsoft Access can't find the macro 'Filter =
strFilter'".

A search of the Access newsgroups revealed KB209582, which includes
this error being generated by space characters in an event entry;
however, removing the spaces in the entry has no effect - I still get
the same error.

Any fix/ideas/suggestions gladly received.

TIA,

Rob

PS. For completeness, my code (direct cut/paste) is as follows:

Option Compare Database
Option Explicit

Public strFilter As String

Private Sub cmdSnpReports_Click()
Dim rst As DAO.Recordset
Dim strSQL As String

On Error GoTo cmdSnpReports_Click_Error

strSQL = "SELECT DISTINCT PersonName FROM tblEmployeeReviews;"

Set rst = CurrentDb.OpenRecordset(strSQL)
rst.MoveFirst
Do While Not rst.EOF
strFilter = "PersonName = '" & rst!PersonName & "'"
DoCmd.SendObject acSendReport, "rptEmployeeReviews", acFormatSNP,
rst!PersonName
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

ExitPoint:
Exit Sub

cmdSnpReports_Click_Error:
Select Case Err.Number
Case 2501
Resume Next
Case Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ")" &
vbNewLine _
& "in procedure cmdSnpReports_Click" & vbNewLine _
& "of VBA Document Form_frmVariousActions"
End Select
Resume ExitPoint
Resume 'set this line as next statement, when debugging after
Ctrl-Brk, to find error line

End Sub
Click to expand...
Click to expand...
Click to expand...
 
And replying to my own post ...

The problem was that I had the Public declaration of strFilter in the form's
code module, rather than in a standard module. Careful re-reading of the KB
article found the extremely important line:
<quote>
Note If you plan to change this example and put the code behind a form,
leave the line "Public strInvoiceWhere As String" in a standard module.
<endquote>

Rob


Rob Parker said:
Thanks again Allen,

I agree entirely with what you say in your first paragraph. I've no idea
what's going on, and it seems so simple.

I've created a new database, imported the table from my previous mdb file,
and recreated the form and report from scratch, just in case this was a
corruption problem. I have not added any error handling code, and the VBA
editor is set to Break on All Errors. I'm still experiencing exactly the
same behaviour as I reported in my last post.

I've added debug.print statements in both subs, to display the value of
strFilter. In the Do loop, the first value is as I expect. However, with
only the Debug.Print statement in the report's Open event code, nothing is
printed - not even the text string in my debug.print statement. And the
form's code does not loop. It seems that the report does not open, and no
error is thrown. I've put a messagebox in the report's Error event, but
it's also not triggered.

The one thing which I do notice now (and on careful examination, it was
happening in the original database) is that a small window flickers
briefly: it is titled "Printing", and contains a message that the report
is being output to a mail message; it also contains a Cancel button.
However, the mail item does not open. And the window only appears once,
not once for each expected pass through the loop; the debug statement
shows that the loop only executes once. As before, the DoCmd.SendObject
line works as expected when the report is not filtered.

The problem appears to be related to the report trying to use strFilter,
either via
Me.Filter = strFilter
or via
Debug.Print "rptEmployeeReviews: strFilter = """ & strFilter & """"

My understanding is that the Public declaration of strFilter in the form's
code module should allow that variable to be accessed from other objects
while the form is open. Is this incorrect? It certainly seems to be the
implication in the KB299016 article, where this technique is described.

I've tried adding DoEvents statements, both before and after the
DoCmd.SendObject, and in the report's Open event, in case it was a timing
problem, but that has no effect.

What next? I've got no idea what to try. In case it helps, I'm running
Access 2002 SP3 under Windows XP Pro.

Rob


Allen Browne said:
I would expect a message if the SendObject failed.
And I would expect a window to open if it succeeds.
Not sure what's going on.

It might be worth checking if the error message is being suppressed
somewhere. For example, in the code window, choose Tools | Options, and
make sure Error Trapping is set to:
Break on Unhandled Errors
Then temporarily comment out any error handler in your code.

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

Reply to group, rather than allenbrowne at mvps dot org.
Rob Parker said:
Thanks for the quick reply, Allen.

I'd originally done that, but it gave a compile error ("variable not
defined" for strFilter - but that's probably understandable, since it's
not a local variable in the report, and when the report is opened it
will be available as it's public in the form's module), so I tried the
alternative I showed in my first post.

However, I've just tried it again, and my code fails to produce any
messages containing the .snp file(s). I've changed the SendObject line
to:
DoCmd.SendObject acSendReport, "rptEmployeeReviews", acFormatSNP, , ,
, "Report for " & rst!PersonName, , True
so that the message(s) should simply be displayed in my email program,
but nothing happens.

I've set a breakpoint on the Do While Not rst.EOF line, and stepped
through the code. The code steps fine until the first DoCmd.SendObject
statement, then finishes. No error, nothing. I've set an error trap in
the Open event of the report, and there's also no error there. If I
comment out the Me.Filter = strFilter line in the report's code, then
the code loops correctly, and the report (unfiltered - I removed the
previous string from the report's Filter property) is generated for each
person, and displays in my email program.

Any suggestions?

Rob


Set the report's On Open property to:
[Event Procedure]

Then click the Build button (...) beside the property.
Access opens the code window.
The line of code goes in there, between the "Private Sub..." and "End
Sub" lines.

message
I'm trying to use the technique described in KB299016 to dynamically
filter a report prior to using SendObject. This includes the
following instruction:
<quote>
Set the OnOpen property of the report to the following event
procedure:
Me.Filter = strFilter
<endquote>
(Well, actually, the KB article uses strInvoiceWhere, but I'm not
dealing with Invoices.)

strFilter is a Public string variable, defined in the code module for
the form containing my command button to run the SendObject code.

When I run the code, I get Error 2485 "Microsoft Access can't find the
macro 'Me.'". If I change the entry in the OnOpen property to:
Filter = strFilter
I get Error 2485 "Microsoft Access can't find the macro 'Filter =
strFilter'".

A search of the Access newsgroups revealed KB209582, which includes
this error being generated by space characters in an event entry;
however, removing the spaces in the entry has no effect - I still get
the same error.

Any fix/ideas/suggestions gladly received.

TIA,

Rob

PS. For completeness, my code (direct cut/paste) is as follows:

Option Compare Database
Option Explicit

Public strFilter As String

Private Sub cmdSnpReports_Click()
Dim rst As DAO.Recordset
Dim strSQL As String

On Error GoTo cmdSnpReports_Click_Error

strSQL = "SELECT DISTINCT PersonName FROM tblEmployeeReviews;"

Set rst = CurrentDb.OpenRecordset(strSQL)
rst.MoveFirst
Do While Not rst.EOF
strFilter = "PersonName = '" & rst!PersonName & "'"
DoCmd.SendObject acSendReport, "rptEmployeeReviews", acFormatSNP,
rst!PersonName
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

ExitPoint:
Exit Sub

cmdSnpReports_Click_Error:
Select Case Err.Number
Case 2501
Resume Next
Case Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ")" &
vbNewLine _
& "in procedure cmdSnpReports_Click" & vbNewLine _
& "of VBA Document Form_frmVariousActions"
End Select
Resume ExitPoint
Resume 'set this line as next statement, when debugging after
Ctrl-Brk, to find error line

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top