Sendobject attaches incorrect report to emails

[Elric]

This Sub emails accounting statements to all customers that are flagged for this delivery type. The function calling the Sub merely loops through the customer table and passes the variables.

The code works perfectly if I step through it or run it for a single customer.

When run for real it attaches the incorrect statement to 90%+ of emails.

By inserting a couple of 1 to 25m counters to slow everything down I can get
90-95% accuracy.

Any suggestions or comments would be welcome:


Sub subemail(numb, add, acname)

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("autostat")

strSQL = "SELECT main.Account, main.Name, cust.ac_add1, cust.ac_add2, cust.ac_add3, cust.ac_add4, RTrim([ac_town]) & ' ' & [ac_zip] AS town, cust.ac_country, cust.ac_zip, slbals.Date, slbals.Type, LTrim([ref]) AS Refe, slbals.Des, slbals.Original, IIf([original]>0,[original],'') AS dr, IIf([original]<0,[original],'') AS cr, slbals.Outstanding, slbals.age, main.[statement output]" & _
"FROM (main LEFT JOIN cust ON main.Account = cust.accno) LEFT JOIN slbals ON main.Account = slbals.Account " & _
"WHERE (((main.Account) = '" & numb & "') And ((slbals.Outstanding) <> 0))" & _
"ORDER BY slbals.Date;"

qdf.SQL = strSQL

cr = Chr(13)
mailmsg = numb & " " & acname & cr & cr & "Your statement of account is attached" & cr & cr & "Regards" & cr & cr & "Elric" & cr & "Company name"

DoCmd.SendObject acSendReport, "email_statement", acFormatRTF, add, , , "Statement of account", mailmsg, False

Set db = Nothing
Set qdf = Nothing
End Sub

[JConsulting]

This Sub emails accounting statements to all customers that are flagged for this delivery type. The function calling the Sub merely loops through the customer table and passes the variables.

The code works perfectly if I step through it or run it for a single customer.

When run for real it attaches the incorrect statement to 90%+ of emails.

By inserting a couple of 1 to 25m counters to slow everything down I can get
90-95% accuracy.

Any suggestions or comments would be welcome:


Sub subemail(numb, add, acname)

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("autostat")

strSQL = "SELECT main.Account, main.Name, cust.ac_add1, cust.ac_add2, cust.ac_add3, cust.ac_add4, RTrim([ac_town]) & ' ' & [ac_zip] AS town, cust.ac_country, cust.ac_zip, slbals.Date, slbals.Type, LTrim([ref]) AS Refe, slbals.Des, slbals.Original, IIf([original]>0,[original],'') AS dr, IIf([original]<0,[original],'') AS cr, slbals.Outstanding, slbals.age, main.[statement output]" & _
"FROM (main LEFT JOIN cust ON main.Account = cust.accno) LEFT JOIN slbals ON main.Account = slbals.Account " & _
"WHERE (((main.Account) = '" & numb & "') And ((slbals.Outstanding) <> 0))" & _
"ORDER BY slbals.Date;"

qdf.SQL = strSQL

cr = Chr(13)
mailmsg = numb & " " & acname & cr & cr & "Your statement of account is attached" & cr & cr & "Regards" & cr & cr & "Elric" & cr & "Company name"

DoCmd.SendObject acSendReport, "email_statement", acFormatRTF, add, , , "Statement of account", mailmsg, False

Set db = Nothing
Set qdf = Nothing
End Sub

the pause is necessary in order to give it time to create the report and attach it.


put this at the top of a code module just under the Option compare statement

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Dim lmilliseconds As Long


to use it,

Sleep(2000) <-- about two seconds

[puppydogbuddy]

You have "add" for the "To" Argument...Shouldn't the AccName go there?

DoCmd.SendObject acSendReport, "email_statement", acFormatRTF, add, , , "Statement of account", mailmsg, False

http://www.databasedev.co.uk/sendobject.html


Also:
have you tried a different sort order (AccNo for instance)? That may have some effect.

[Elric]

the pause is necessary in order to give it time to create the report and attach it.


put this at the top of a code module just under the Option compare statement

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Dim lmilliseconds As Long


to use it,

Sleep(2000) <-- about two seconds

Thanks for the reply. Unfortunately 2 seconds makes no difference although I could increase the value.

I need to know the delay period that will guarantee all reports will be attached to the correct email or alternative approaches to either the task itself or to giving Access the opportunity to get it consistently right. I've tried numerous methods and none give 100%.

My problem is that I don't understand the mechanisms involved which makes it difficult to provide a perfect solution.

[JConsulting]

Thanks for the reply. Unfortunately 2 seconds makes no difference although I could increase the value.

I need to know the delay period that will guarantee all reports will be attached to the correct email or alternative approaches to either the task itself or to giving Access the opportunity to get it consistently right. I've tried numerous methods and none give 100%.

My problem is that I don't understand the mechanisms involved which makes it difficult to provide a perfect solution.

we would all enjoy the perfect mechanism...unfortunately for us, we have no idea how large your reports are, how fast your machine is, or how quickly e-mails are created for you.

So for us...anything we tell you would be a wild guess.

The problem IS one of timing. your method is fine...your code is ok. you prove it works 90% of the time. So perhaps you can set the variable sleep based on a recordcount in the report you're outputting. But that's as far as the Access automation can go from a timing standpoint.

You still have environmental considerations outside of Access.

I would say give it your best estimate.
J

[Elric]

Bog standard Dell dimension E521(a couple of months old) , XP, Access 2003 and Thunderbird as emailer. Reports are mostly a single A4 page.

I figure that Access must write the report to a temporary file before attaching it and if I knew where/how then I could, at least, delete the file before creating the next one; this might still be problematic but customers will not get the wrong statement.

I wonder if my coding approach is not ideal, I cannot believe that I'm the only one to have tried producing invoice/statements (or anything else) this way and I can't find any complaints similar to mine.

[JConsulting]

Bog standard Dell dimension E521(a couple of months old) , XP, Access 2003 and Thunderbird as emailer. Reports are mostly a single A4 page.

I figure that Access must write the report to a temporary file before attaching it and if I knew where/how then I could, at least, delete the file before creating the next one; this might still be problematic but customers will not get the wrong statement.

I wonder if my coding approach is not ideal, I cannot believe that I'm the only one to have tried producing invoice/statements (or anything else) this way and I can't find any complaints similar to mine.

the only other thing I can suggest here is that you perform a Dcount on the QDF before you open the report with it. I of course can't see your data, so this may not be an issue, but if you're trying to send a blank report, there can be a problem with report generation.

A simple if statement would bypass the current loop to move to the next if the report is blank.

J