Module suddenly won't run

[Dorci]

I have the following module that suddenly stopped running. The subroutine runs just fine, but it won't get past the declarations section. The module is only called by manually executing a macro, which contains just the command to run the module. Could I have done something elsewhere in the program that caused it to stop running? Thanks for your help

Option Explici
Option Compare Databas

Private Sub Mod_AM_Rpt(

Dim A, C As Intege
Dim stDocName, stQueryName, stCriteria As Strin
Dim stMsgSubject, stMsgText As Strin

stDocName = "Account Manager Report
stQueryName = "Account Manager Query

'Close report just in case it was left open by an incomplete proces
DoCmd.Close acReport, stDocNam

stMsgSubject = "Account Manager Project Tracking Report
stMsgText = "Dear Account Manager," & Chr(10) & Chr(10) &
"Please double-click the attached snapshot file to view your Account Manager Project Tracking " &
"Report. If you cannot open the report, click on the link below to download and install the free " &
"Snapshot Viewer. This one-time installation will enable you to view this and future reports. " &
"Please contact me if you have any problems or questions. " & Chr(10) & Chr(10) &
"http://www.microsoft.com/downloads/...3F-6D74-423D-8274-8B7E6313EDFB&displaylang=en

'Run report for every customer per Account Manager
For A = 1 To 5
For C = 1 To 1
stCriteria = "[AMNum]=" & A & "and [CustNum]=" &
If DCount("*", stQueryName, stCriteria) > 0 The
DoCmd.OpenReport "Account Manager Report", acViewPreview, , stCriteria, acIco
DoCmd.SendObject acSendReport, stDocName, acFormatSNP, Reports![Account Manager Report].[ACCOUNTMGR], , , stMsgSubject, stMsgText,
DoCmd.Close acReport, stDocNam
End I
Next
Next
MsgBox ("Finished sending Account Mgr reports."

End Su

 

[Dirk Goldgar]

I have the following module that suddenly stopped running. The
subroutine runs just fine, but it won't get past the declarations
section. The module is only called by manually executing a macro,
which contains just the command to run the module. Could I have done
something elsewhere in the program that caused it to stop running?
Thanks for your help.

Option Explicit
Option Compare Database

Private Sub Mod_AM_Rpt()

Dim A, C As Integer
Dim stDocName, stQueryName, stCriteria As String
Dim stMsgSubject, stMsgText As String

stDocName = "Account Manager Report"
stQueryName = "Account Manager Query"

'Close report just in case it was left open by an incomplete process
DoCmd.Close acReport, stDocName

stMsgSubject = "Account Manager Project Tracking Report"
stMsgText = "Dear Account Manager," & Chr(10) & Chr(10) & _
"Please double-click the attached snapshot file to view
your Account Manager Project Tracking " & _ "Report. If
you cannot open the report, click on the link below to
download and install the free " & _ "Snapshot Viewer.
This one-time installation will enable you to view this
and future reports. " & _ "Please contact me if you have any problems
or questions. " & Chr(10) & Chr(10) & _
"http://www.microsoft.com/downloads/details.aspx?familyid=B73DF33F-6D74-
423D-8274-8B7E6313EDFB&displaylang=en"

'Run report for every customer per Account Manager.
For A = 1 To 50
For C = 1 To 13
stCriteria = "[AMNum]=" & A & "and [CustNum]=" & C
If DCount("*", stQueryName, stCriteria) > 0 Then
DoCmd.OpenReport "Account Manager Report", acViewPreview,
, stCriteria, acIcon DoCmd.SendObject acSendReport,
stDocName, acFormatSNP, Reports![Account Manager
Report].[ACCOUNTMGR], , , stMsgSubject, stMsgText, 0
DoCmd.Close acReport, stDocName End If Next C
Next A
MsgBox ("Finished sending Account Mgr reports.")

End Sub

When you compile your project (in the VB Editor, click Debug ->
Compile), are any errors found? If you set a breakpoint on the first
line of the procedure and step through the code line by line using the
F8 key, what happens? I see a few things wrong, but nothing that would
obviously cause it to fail. Note that:

Dim A, C As Integer
Dim stDocName, stQueryName, stCriteria As String
Dim stMsgSubject, stMsgText As String

should be something like

Dim A As Integer, C As Integer
Dim stDocName As String, stQueryName As String
Dim stCriteria As String
Dim stMsgSubject As String, stMsgText As String

The way you had it, the variables A, stDocName, stQueryName, and
strMsgSubject were all being declared as Variant. Also, this line

stCriteria = "[AMNum]=" & A & "and [CustNum]=" & C

doesn't leave a space between the value of A and the keyword "and".
However, I think that will still be parsed correctly. So I need more
detailed information about what is happening.

 

[Dorci]

Hi Dirk
I made the suggested changes to the code (see below). Thanks. I got no errors when I compiled. When I step through the code with F8 it works fine -- that is, starting at "Private Sub..." When I start at "Option Explicit", F8 just beeps. The problem seems to occur before processing ever gets to the subroutine. No error message is shown; when I run the macro, the code window just opens. Now that I think about it, the one thing I remember doing before I noticed this problem is "Compact and Repair". Was that a no-no? Thanks so much for your time
----------------------------
Option Explici
Option Compare Databas

Private Sub Mod_AM_Rpt(

Dim A As Integer, C As Intege
Dim stDocName As String, stQueryName As String, stCriteria As Strin
Dim stMsgSubject As String, stMsgText As Strin

stDocName = "Account Manager Report
stQueryName = "Account Manager Query

'Close report just in case it was left open by an incomplete proces
DoCmd.Close acReport, stDocNam

stMsgSubject = "Account Manager Project Tracking Report
stMsgText = "Dear Account Manager," & Chr(10) & Chr(10) &
"Please double-click the attached snapshot file to view your Account Manager Project Tracking " &
"Report. If you cannot open the report, click on the link below to download and install the free " &
"Snapshot Viewer. This one-time installation will enable you to view this and future reports. " &
"Please contact me if you have any problems or questions. " & Chr(10) & Chr(10) &
"http://www.microsoft.com/downloads/...3F-6D74-423D-8274-8B7E6313EDFB&displaylang=en

'Run report for every customer per Account Manager
For A = 1 To 1
For C = 1 To 1
stCriteria = "[AMNum]=" & A & " and [CustNum]=" &
If DCount("*", stQueryName, stCriteria) > 0 The
DoCmd.OpenReport "Account Manager Report", acViewPreview, , stCriteria, acIco
'DoCmd.SendObject acSendReport, stDocName, acFormatSNP, Reports![Account Manager Report].[ACCOUNTMGR], , , stMsgSubject, stMsgText,
DoCmd.Close acReport, stDocNam
End I
Next
Next
MsgBox ("Finished sending Account Mgr reports."

End Su
--------------------------

 

[Dirk Goldgar]

Hi Dirk,
I made the suggested changes to the code (see below). Thanks. I got
no errors when I compiled. When I step through the code with F8 it
works fine -- that is, starting at "Private Sub..."

Okay, that's good.

When I start at "Option Explicit", F8 just beeps.

That's natural. Unless the cursor is actually in a procedure, Access
doesn't know what procedure you want to step into when you press F8.

The problem seems to occur before
processing ever gets to the subroutine. No error message is shown;
when I run the macro, the code window just opens.

I think you'd better paste the actions that the macro runs, with their
arguments (if any). I believe you said it was a very simple one.

Now that I think
about it, the one thing I remember doing before I noticed this
problem is "Compact and Repair". Was that a no-no?

Shouldn't have been. Let's have a look at the macro, and see if there's
somethign there that explains what's going on.

 

[Dirk Goldgar]

The macro has just one action:
Action = OpenModule
Module Name = Acct_Mgr_Reports
There's absolutely nothing else in the macro. I even copied the
subroutine into a new module and created a new macro, and it still
didn't work. Lastly, I copied the subroutine into a new module of a
BLANK database (after copying the necessary tables, queries, etc.)
and it still wouldn't run.

But OpenModule will *never* run any code! All that does is open the
specified code module for editing. You said this used to work, but I
can't see how this macro could ever have resulted in the execution of
the procedure. Did you change it?

 

[Dorci]

Nope, I haven't changed a thing. I know it used to work somehow because I had to reduce the two For...Next counters so it wouldn't run so long while I was testing it. I'm so sorry if I sent you on a wild goose chase. I tried RunCode, but that didn't work either. What should I do? (That's if you're still speaking to me.)

 

[Dirk Goldgar]

Nope, I haven't changed a thing. I know it used to work somehow
because I had to reduce the two For...Next counters so it wouldn't
run so long while I was testing it. I'm so sorry if I sent you on a
wild goose chase. I tried RunCode, but that didn't work either.
What should I do? (That's if you're still speaking to me.)

<g>

Change the procedure from a Sub to a Function, and change it from
Private to Public -- you only have to change the procedure declaration,
and the VB Editor will change the End Sub line to End Function. Then
you can use the RunCode action to run it. I think you may have to put
parentheses on the end of the function name in the macro argument, like
this:

Action: RunCode
Function Name: Mod_AM_Rpt()

That ought to do it.

 

[Dorci]

Dirk, you are an angel!!! All's well now. Thanks so much!!!