win2pdf mail helper loop

M

Maverick6197

I've posted this before, but haven't been able to get an answer.....

Here's what I'm trying to do.....

I have a form who's recordsource is a query that pulls records with a date of
30 days before today's date.

In the OnOpen() Event of the form, I have placed code that uses the
information from the form to build a report. The report is then e-mailed to
the e-mail address on the form using the win2pdf Mail Helper program. The
code then tells the form to go to the next record and repeat everything for
this record as well.

The problem is that the first report is e-mailed to the e-mail address twice
and I cannot figure out why. The report for the second record is only sent
once.

Here is my code........

---------Start of Code-------------------

Option Explicit

Private Const REG_DWORD As Long = 4
Private Const HKEY_CURRENT_USER = &H80000001
Private Const KEY_ALL_ACCESS = &H3F

Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long)
As Long

Private Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias
"RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal
ulOptions As Long, ByVal samDesired As Long, phkResult As Long) As Long

Private Declare Function RegSetValueExLong Lib "advapi32.dll" Alias
"RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal
Reserved As Long, ByVal dwType As Long, lpValue As Long, ByVal cbData As Long)

As Long

Private Sub SaveWin2PDFDword(sValueName As String, lValueSetting As Long)

Dim lRetVal As Long 'result of the SetValueExLong function

Dim hKey As Long 'handle of open key

lRetVal = RegOpenKeyEx(HKEY_CURRENT_USER, "Software\VB and VBA Program
Settings\Dane Prairie Systems\Win2PDF", 0, KEY_ALL_ACCESS, hKey)
lRetVal = RegSetValueExLong(hKey, sValueName, 0&, REG_DWORD, lValueSetting,
4)

RegCloseKey (hKey)

End Sub

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_cmdLoadReport_Click

Loop_cmdLoopReport_Click:

Dim ProtecCode As String

ProtecCode = Me.txtEmailContact

SaveWin2PDFDword "file options", &H421
SaveSetting "Dane Prairie Systems", "Win2PDF", "PDFMailFrom", "No-
(e-mail address removed)"
SaveSetting "Dane Prairie Systems", "Win2PDF", "PDFMailRecipients",
ProtecCode
SaveSetting "Dane Prairie Systems", "Win2PDF", "PDFMailSubject", "PROTEC -
NOTIFICATION OF WARRANTY STATUS"
SaveSetting "Dane Prairie Systems", "Win2PDF", "PDFMailNote", "Please see
the attached document concerning a unit registered with Astec Underground by
your dealership. DO NOT REPLY TO THIS E-MAIL."
SaveSetting "Dane Prairie Systems", "Win2PDF", "PDFFileName", "T:\
Customer Support\Protec\Protec Warranty Status Notifications\" & Me.
txtDealerNumber & " - " & Me.txtUnitSerialNumber & ".pdf"
DoCmd.OpenReport "rptProtec", acViewNormal
DoEvents
'Forms![frmProtec].SetFocus


DoCmd.GoToRecord , , acNext
DoEvents
'Forms![frmProtec].SetFocus

GoTo Loop_cmdLoopReport_Click

Exit_cmdLoadReport_Click:
Exit Sub

Err_cmdLoadReport_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdLoadReport_Click
End Sub

---------End Code----------

Any help would be greatly appreciated. Thanks in advance.....

Shannan
 
S

Stephen Lebans

Try placing your code in the form's Load event instead of the Open event.

Before invoking your looping construct you should Move to the first row of
the recordset.
Your looping construct does not test for EOF before processing the loop.
Here's some example code for itinerating through a recordset attached to a
from.

With Me.RecordsetClone
' Move to first record
.MoveFirst

' Loop until all records are processed
' This example uses a field named "Comment"
' Note this is the name of the FIELD not the
' name of the TextBox control bound to this field
Do While Not .EOF
.Edit
sText = IIf(IsNull(.Fields("Comment")), "", .Fields("Comment"))
' See if field is empty
If Len(sText & vbNullString) = 0 Then
sRTFdata = sHeader & "}"
Else
sRTFdata = sHeader & sText & "\par }"
End If


' Save our RTF encoded string back to Comment field
.Fields("Comment") = sRTFdata
.Update
' Move to next record
.MoveNext
Loop


End With



--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


Maverick6197 said:
I've posted this before, but haven't been able to get an answer.....

Here's what I'm trying to do.....

I have a form who's recordsource is a query that pulls records with a date
of
30 days before today's date.

In the OnOpen() Event of the form, I have placed code that uses the
information from the form to build a report. The report is then e-mailed
to
the e-mail address on the form using the win2pdf Mail Helper program. The
code then tells the form to go to the next record and repeat everything
for
this record as well.

The problem is that the first report is e-mailed to the e-mail address
twice
and I cannot figure out why. The report for the second record is only
sent
once.

Here is my code........

---------Start of Code-------------------

Option Explicit

Private Const REG_DWORD As Long = 4
Private Const HKEY_CURRENT_USER = &H80000001
Private Const KEY_ALL_ACCESS = &H3F

Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As
Long)
As Long

Private Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias
"RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal
ulOptions As Long, ByVal samDesired As Long, phkResult As Long) As Long

Private Declare Function RegSetValueExLong Lib "advapi32.dll" Alias
"RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal
Reserved As Long, ByVal dwType As Long, lpValue As Long, ByVal cbData As
Long)

As Long

Private Sub SaveWin2PDFDword(sValueName As String, lValueSetting As Long)

Dim lRetVal As Long 'result of the SetValueExLong function

Dim hKey As Long 'handle of open key

lRetVal = RegOpenKeyEx(HKEY_CURRENT_USER, "Software\VB and VBA Program
Settings\Dane Prairie Systems\Win2PDF", 0, KEY_ALL_ACCESS, hKey)
lRetVal = RegSetValueExLong(hKey, sValueName, 0&, REG_DWORD,
lValueSetting,
4)

RegCloseKey (hKey)

End Sub

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_cmdLoadReport_Click

Loop_cmdLoopReport_Click:

Dim ProtecCode As String

ProtecCode = Me.txtEmailContact

SaveWin2PDFDword "file options", &H421
SaveSetting "Dane Prairie Systems", "Win2PDF", "PDFMailFrom", "No-
(e-mail address removed)"
SaveSetting "Dane Prairie Systems", "Win2PDF", "PDFMailRecipients",
ProtecCode
SaveSetting "Dane Prairie Systems", "Win2PDF", "PDFMailSubject",
"PROTEC -
NOTIFICATION OF WARRANTY STATUS"
SaveSetting "Dane Prairie Systems", "Win2PDF", "PDFMailNote", "Please
see
the attached document concerning a unit registered with Astec Underground
by
your dealership. DO NOT REPLY TO THIS E-MAIL."
SaveSetting "Dane Prairie Systems", "Win2PDF", "PDFFileName", "T:\
Customer Support\Protec\Protec Warranty Status Notifications\" & Me.
txtDealerNumber & " - " & Me.txtUnitSerialNumber & ".pdf"
DoCmd.OpenReport "rptProtec", acViewNormal
DoEvents
'Forms![frmProtec].SetFocus


DoCmd.GoToRecord , , acNext
DoEvents
'Forms![frmProtec].SetFocus

GoTo Loop_cmdLoopReport_Click

Exit_cmdLoadReport_Click:
Exit Sub

Err_cmdLoadReport_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdLoadReport_Click
End Sub

---------End Code----------

Any help would be greatly appreciated. Thanks in advance.....

Shannan
Click to expand...
 
M

Maverick6197 via AccessMonster.com

Forgive my ignorance, but what is "EOF?"

Also, with the exception of the comment field everything else would be
verbetum, correct? Thanks for your help.

Shannan

Stephen said:
Try placing your code in the form's Load event instead of the Open event.

Before invoking your looping construct you should Move to the first row of
the recordset.
Your looping construct does not test for EOF before processing the loop.
Here's some example code for itinerating through a recordset attached to a
from.

With Me.RecordsetClone
' Move to first record
.MoveFirst

' Loop until all records are processed
' This example uses a field named "Comment"
' Note this is the name of the FIELD not the
' name of the TextBox control bound to this field
Do While Not .EOF
.Edit
sText = IIf(IsNull(.Fields("Comment")), "", .Fields("Comment"))
' See if field is empty
If Len(sText & vbNullString) = 0 Then
sRTFdata = sHeader & "}"
Else
sRTFdata = sHeader & sText & "\par }"
End If

' Save our RTF encoded string back to Comment field
.Fields("Comment") = sRTFdata
.Update
' Move to next record
.MoveNext
Loop

End With
I've posted this before, but haven't been able to get an answer.....
Click to expand...
[quoted text clipped - 107 lines]
Shannan
Click to expand...
Click to expand...
 
M

Maverick6197 via AccessMonster.com

Nevermind, I finally got it to work correctly. Here is my revised code....

--------start of code----------

Option Explicit

Private Const REG_DWORD As Long = 4
Private Const HKEY_CURRENT_USER = &H80000001
Private Const KEY_ALL_ACCESS = &H3F

Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long)
As Long

Private Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias
"RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal
ulOptions As Long, ByVal samDesired As Long, phkResult As Long) As Long

Private Declare Function RegSetValueExLong Lib "advapi32.dll" Alias
"RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal
Reserved As Long, ByVal dwType As Long, lpValue As Long, ByVal cbData As Long)
As Long

Private Sub SaveWin2PDFDword(sValueName As String, lValueSetting As Long)

Dim lRetVal As Long 'result of the SetValueExLong function

Dim hKey As Long 'handle of open key

lRetVal = RegOpenKeyEx(HKEY_CURRENT_USER, "Software\VB and VBA Program
Settings\Dane Prairie Systems\Win2PDF", 0, KEY_ALL_ACCESS, hKey)
lRetVal = RegSetValueExLong(hKey, sValueName, 0&, REG_DWORD, lValueSetting,
4)

RegCloseKey (hKey)

End Sub

Private Sub Form_Load()
On Error GoTo Err_cmdLoadReport_Click

With Me.RecordsetClone
.MoveFirst


Do While Not .EOF


Dim ProtecCode As String

ProtecCode = Me.txtEmailContact

SaveWin2PDFDword "file options", &H421
SaveSetting "Dane Prairie Systems", "Win2PDF", "PDFMailFrom", "No-
(e-mail address removed)"
SaveSetting "Dane Prairie Systems", "Win2PDF", "PDFMailRecipients",
ProtecCode
SaveSetting "Dane Prairie Systems", "Win2PDF", "PDFMailSubject", "PROTEC -
NOTIFICATION OF WARRANTY STATUS"
SaveSetting "Dane Prairie Systems", "Win2PDF", "PDFMailNote", "Please see
the attached document concerning a unit registered with Astec Underground by
your dealership. DO NOT REPLY TO THIS E-MAIL."
SaveSetting "Dane Prairie Systems", "Win2PDF", "PDFFileName", "T:\
Customer Support\Protec\Protec Warranty Status Notifications\" & Me.
txtDealerNumber & " - " & Me.txtUnitSerialNumber & ".pdf"
DoCmd.OpenReport "rptProtec", acViewNormal

Forms![frmProtec].SetFocus
DoCmd.GoToRecord , , acNext


Loop
End With




Exit_cmdLoadReport_Click:
Exit Sub

Err_cmdLoadReport_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdLoadReport_Click
End Sub

-------------------end of code-----------------------------
Forgive my ignorance, but what is "EOF?"

Also, with the exception of the comment field everything else would be
verbetum, correct? Thanks for your help.

Shannan
Try placing your code in the form's Load event instead of the Open event.
Click to expand...
[quoted text clipped - 36 lines]
Click to expand...
 
M

Maverick6197 via AccessMonster.com

Does anyone know how I could make this run once everyday, including on the
weekends when no one is here? I'm using a query to pull anything with a date
exactly 90 days past, but this doesn't account for the days missed during the
weekend. How could I make up for this?

Thanks in advance,

Shannan
Nevermind, I finally got it to work correctly. Here is my revised code....

--------start of code----------

Option Explicit

Private Const REG_DWORD As Long = 4
Private Const HKEY_CURRENT_USER = &H80000001
Private Const KEY_ALL_ACCESS = &H3F

Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long)
As Long

Private Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias
"RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal
ulOptions As Long, ByVal samDesired As Long, phkResult As Long) As Long

Private Declare Function RegSetValueExLong Lib "advapi32.dll" Alias
"RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal
Reserved As Long, ByVal dwType As Long, lpValue As Long, ByVal cbData As Long)
As Long

Private Sub SaveWin2PDFDword(sValueName As String, lValueSetting As Long)

Dim lRetVal As Long 'result of the SetValueExLong function

Dim hKey As Long 'handle of open key

lRetVal = RegOpenKeyEx(HKEY_CURRENT_USER, "Software\VB and VBA Program
Settings\Dane Prairie Systems\Win2PDF", 0, KEY_ALL_ACCESS, hKey)
lRetVal = RegSetValueExLong(hKey, sValueName, 0&, REG_DWORD, lValueSetting,
4)

RegCloseKey (hKey)

End Sub

Private Sub Form_Load()
On Error GoTo Err_cmdLoadReport_Click

With Me.RecordsetClone
.MoveFirst

Do While Not .EOF


Dim ProtecCode As String

ProtecCode = Me.txtEmailContact

SaveWin2PDFDword "file options", &H421
SaveSetting "Dane Prairie Systems", "Win2PDF", "PDFMailFrom", "No-
(e-mail address removed)"
SaveSetting "Dane Prairie Systems", "Win2PDF", "PDFMailRecipients",
ProtecCode
SaveSetting "Dane Prairie Systems", "Win2PDF", "PDFMailSubject", "PROTEC -
NOTIFICATION OF WARRANTY STATUS"
SaveSetting "Dane Prairie Systems", "Win2PDF", "PDFMailNote", "Please see
the attached document concerning a unit registered with Astec Underground by
your dealership. DO NOT REPLY TO THIS E-MAIL."
SaveSetting "Dane Prairie Systems", "Win2PDF", "PDFFileName", "T:\
Customer Support\Protec\Protec Warranty Status Notifications\" & Me.
txtDealerNumber & " - " & Me.txtUnitSerialNumber & ".pdf"
DoCmd.OpenReport "rptProtec", acViewNormal

Forms![frmProtec].SetFocus
DoCmd.GoToRecord , , acNext


Loop
End With



Exit_cmdLoadReport_Click:
Exit Sub

Err_cmdLoadReport_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdLoadReport_Click
End Sub

-------------------end of code-----------------------------
Forgive my ignorance, but what is "EOF?"
Click to expand...
[quoted text clipped - 8 lines]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

win2pdf mail helper loop problem 0
RunReportAsPDF 7
Read Windows Product ID in Vista from Access 0
Report loop doesn't work 9
API code to obtain Acrobat Reader Shell Path from the registry 5
Print WordPerfect Doc 0
Code Problem 1
Code Problem 0

Top