VBA Loop One Report - output and save many pdf's

pbaldy said:

No, if you follow one down:

http://www.granite.ab.ca/access/email/recordsetloop.htm

You use the loop in combination with the other code to send an individual report to each person. The key would be if you send within the loop, you send to that person. You could build a string in the loop then send after the loop, which would send one email out. The OP's need is slightly different, but they have already stated they know how to save the PDF files.

Click to expand...

wstandis said:

On Local Error GoTo Some_Err

Dim MyDB As Database, RS As Recordset
Dim strBody As String, lngCount As Long, lngRSCount As Long

DoCmd.RunCommand acCmdSaveRecord
Set MyDB = DBEngine.Workspaces(0).Databases(0)

Me!txtProgress = Null
The above all seems like generic text setting the stage for what follows? Pretty much, yes, and you probably don't care about the last line

Set RS = MyDB.OpenRecordset _ ("Email - Outstanding Promos")
Would this be the name of the report that I am opening? In my case “Store invoices” or the query the report is based on? “Commissions” Either the query the report is based on, or since that query probably returns multiple lines for each store, an SQL statement like "SELECT DISTINCT StoreID FROM QueryName" so you only get one instance of each store

lngRSCount = RS.RecordCount
If lngRSCount = 0 Then
MsgBox "No promo email messages to send.", vbInformation

Else
RS.MoveLast
RS.MoveFirst
Do Until RS.EOF
lngCount = lngCount + 1
lblStatus.Caption = "Writing Message " & CStr(lngCount) _ & " of " & CStr(lngRSCount) & "..."

The above seems like a status which I can add after i figure out the other functionality? The section below seems specific to the email functionality Do I need this part? It looks like it is pulling information from a query as well as updating information? My only criticism of this code is that it contains some extra stuff that makes it more confusing for a beginner. Yes, some of it is either updating the screen to keep the user updated or updating the table to record the fact that the email was sent, both of which can be dropped. You don't need those last 2 lines

strTo = RS!cEmailAddress Is this the report name or underlying query name? That is a field in the query to get the email address - you don't need

intMessageID = Year(Now) & Month(Now) & Day(Now) & Fix(Timer) & "_MabryMail" is this a field in the query? They're building a string that they never use - you don't need

' Send the email using some technique or other this is where I would input my code to create a pdf??? Yes, exactly. Right before this line, you would want to populate the form control above to the current store, like:

Forms![Select Load List]![LoadID] = rs!StoreID

RS.Edit RS("cpeDateTimeEmailed") = Now() looks like it is updating a field in the query? Yes, you don't need

RS.Update RS.MoveNext Loop End If RS.Close MyDB.Close Set RS = Nothing Set MyDB = Nothing Close

The section below seems like it is just to make a fancy interface to let you know status? Not sure if this is needed? Yes, exactly, and you don't need the first line or either of the lblStatus lines

Me!txtProgress = "Sent " & CStr(lngRSCount) & " emails."
lblStatus.Caption = "Email disconnected"
MsgBox "Done sending Promo email. ", vbInformation, "Done" lblStatus.Caption = "Idle..."
Exit Sub

Some_Err:
'MousePointer = 0
MsgBox "Error (" & CStr(Err.Number) & ") " & Err.Description, _
vbExclamation, "Error!"
lblStatus.Caption = "Email disconnected"

Click to expand...

pbaldy said:

In answer to the first question: yes, ShipmentID would be the primary key, and it sounds like you'd replace that with StoreID.

For the rest, somehow the code got all jammed together, making it difficult to follow. Can you repost that so it keeps the formatting? You probably want the code tags (

Code:

your code here[/ code], without the space).[/quote]
 

Recap I have one table with 6 fields[LIST=1]
[*]ID
[*]Store_ID
[*]Store Name
[*]Item
[*]Amt
[*]Month
[/LIST]A query built on this table called test - with all fields
A report built on thsi query Called [FONT=Tms Rmn]Commissions_Rpt[/FONT]
A form built on this query called [FONT=Tms Rmn]Commissions_Frm[/FONT]
 
 
Thanks - so just to clarify on my report on event command i would use:
 
[FONT=Courier New]Me.Filter = "ShipmentsID=" & Forms![[FONT=Tms Rmn]Commissions_Frm[/FONT]]![Store_ID]
Me.FilterOn = True[/FONT]

[FONT=Courier New][COLOR=red][B]what is the Shipments ID?[/B][/COLOR][/FONT]


[FONT=Courier New]To consolidate the code based on your last suggestion: I now have:[/FONT]

On Local Error GoTo Some_Err
 
Dim MyDB As Database, RS As Recordset
Dim strBody As String, lngCount As Long, lngRSCount As Long
DoCmd.RunCommand acCmdSaveRecord
Set MyDB = DBEngine.Workspaces(0).Databases(0)
 
 
Set RS = MyDB.OpenRecordset _
("SELECT DISTINCT Store_ID FROM test")
 
 
lngRSCount = RS.RecordCount
If lngRSCount = 0 Then
MsgBox "No Comission Schedules to Print", vbInformation
Else
RS.MoveLast
RS.MoveFirst
Do Until RS.EOF
 
Forms![[FONT=Tms Rmn]Commissions_Frm[/FONT]]![Store_ID] = RS!Store_ID
Beep 
' Want to get the rest right before I start messing with the output.
 
 
RS.Edit
RS.Update
RS.MoveNext
Loop
 
End If
RS.Close
MyDB.Close
Set RS = Nothing
Set MyDB = Nothing
Close
 
 
MsgBox "Done sending Promo email. ", vbInformation, "Done"
 
Exit Sub
 
Some_Err:
MsgBox "Error (" & CStr(Err.Number) & ") " & Err.Description, _
vbExclamation, "Error!"
lblStatus.Caption = "Email disconnected"
End Sub

Click to expand...

wstandis said:

Thanks - so just to clarify on my report on event command i would use:

Me.Filter = "ShipmentsID=" & Forms![Commissions_Frm]![Store_ID]
Me.FilterOn = True

what is the Shipments ID? As noted previously, you would replace that with Store_ID


To consolidate the code based on your last suggestion: I now have:

On Local Error GoTo Some_Err

Dim MyDB As DAO.Database, RS As DAO.Recordset
Dim strBody As String, lngCount As Long, lngRSCount As Long
DoCmd.RunCommand acCmdSaveRecord
Set MyDB = DBEngine.Workspaces(0).Databases(0)


Set RS = MyDB.OpenRecordset _
("SELECT DISTINCT Store_ID FROM test")


lngRSCount = RS.RecordCount
If lngRSCount = 0 Then
MsgBox "No Comission Schedules to Print", vbInformation
Else
RS.MoveLast
RS.MoveFirst
Do Until RS.EOF

Forms![Commissions_Frm]![Store_ID] = RS!Store_ID
Beep
' Want to get the rest right before I start messing with the output.
Debug.Print RS!Store_ID 'this will put each store ID in the VBA immediate window, so you can see that it spun through them. Only needed for testing

RS.Edit Delete
RS.Update Delete
RS.MoveNext
Loop

End If
RS.Close
MyDB.Close
Set RS = Nothing
Set MyDB = Nothing
Close


MsgBox "Done sending Promo email. ", vbInformation, "Done"

Exit Sub

Some_Err:
MsgBox "Error (" & CStr(Err.Number) & ") " & Err.Description, _
vbExclamation, "Error!"
lblStatus.Caption = "Email disconnected" Delete
End Sub

Click to expand...

pbaldy said:

First make sure in Tools/References that the Microsoft DAO 3.6 library is checked. Also, the form should probably not be bound to the table or query, or at least make sure the textbox you use for the report filter is not bound.

Click to expand...