The following two lines should be at the top of every code page
Code:
Option Compare Database ' <<= Should be at the top of every module
Option Explicit ' <<= Should be at the top of every module
"Option Explicit" requires that every variable be explicitly declared (the Dim statement).
In the IDE, click on TOOLS/Options. Click on the "Editor" tab. The 2nd check box, "Require Variable Declaration" should be checked. Now any NEW modules will have this added automatically. Any existing modules must have "Option Explicit" added manually.
-----------------------------------
You have declared "rstBrokers" but never used the record set.
You have declared "rstCust" (singular), but in the code you have "rstCusts" (plural).
You have declared "rstCustState", but in code you switched to "rstCusts", which is not declared.
----------------------------------
You are missing an "End If" statement for the line "If (Not g_blnTestMode) Or (intNumEmailsCreated <= 2) Then"
----------------------------------
You have two loops: the outer loop is "Do While Not .EOF" (rstCust) and the inner loop "Do While Not rstCustState.EOF". But you have the "MoveNext" statements crossed: (disreguard the misnamed recordsets)
Code:
With rstCust
' Loop through the Cust- creating an e-mail (with attached reports) for each cust.
Do While Not .EOF ' (rstCust)
Do While Not rstCustState.EOF
rstCusts.MoveNext
Loop
rstState.MoveNext
Loop
' Close the recordset.
rstCusts.Close
rstState.Close
End With
Should be: (disreguard the misnamed recordsets)
Code:
With rstCust
' Loop through the Cust- creating an e-mail (with attached reports) for each cust.
- Do While Not .EOF ' (rstCust)
|
| - Do While Not rstCustState.EOF
| |
| - rstState.MoveNext
| Loop
- rstCusts.MoveNext
Loop
' Close the recordset.
rstCusts.Close
rstState.Close
End With
----------------------------------
In this part of the code:
Code:
If rstCust.BOF And rstCust.EOF Then
MsgBox "No Records"
Exit Sub
Else
The Msgbox text has the wrong type of opening and closing quotes.
Delete or comment out the "Exit Sub" line because it is not needed.
The If() (above) will fall through to the label "Exit_cmdOK_Click:".
The recordset close statements and "Set .... Nothing" are in the wrong place. They should be just above the "Exit Sub" statement.
Code:
Exit_cmdOK_Click:
' Close the recordset.
rstCusts.Close
rstState.Close
Set rstCusts = Nothing
Set rstState = Nothing
Exit Sub
----------------------------------
My personal preference... I never use spaces when creating file names. Causes too many headaches.
Instead of this:
Code:
strPathAndFilename_Report1 = strPathToStatementFiles & " " & strCustID & " " & strScrubbedCustName & " " & strState & " " & varCheckDate & ".pdf"
I use the underscore to seperate parts of the file name:
Code:
strPathAndFilename_Report1 = strPathToStatementFiles & "_" & strCustID & "_" & strScrubbedCustName & "_" & strState & "_" & varCheckDate & ".pdf"
----------------------------------
Finally, it looks like there are approx 12 variables that have not been declared.
-----------------------------------
My apologies if this seems harsh. But you did ask
Windows XP 
Access 2007
Reply With Quote
I will look and make corrections and post back. thanks for your help!
