Looping through a recordset and sending a report as an email

[meenderingm]

I have the following tables AuditTableList whose fiels are: ProviderID,
EmailAddress, DirEmail, OpsDirEmail, OtherEmail1 & OtherEmail2. I also have
a table called Audit Results this table has the data for the report
“LettersToBeSent†that I want to email out as an attached Snapshot Viewer
file. The fields that link these two tables together is called ProviderID.
I have the following code to loop through the recordsent of AuditTableList
and to send out the report to all of the providers in this list. The report
that is sent to each provider should be only for that provider.
******************************************************************************************************************

Option Compare Database
Option Explicit



Sub SeparateEmails()

On Error GoTo Err_SeparateEmails

Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim rsGLTable As Recordset
Dim rsCriteria As Recordset

Set db = CurrentDb
Set rsCriteria = db.OpenRecordset("AuditTableList", dbOpenSnapshot)

rsCriteria.MoveFirst

Do Until rsCriteria.EOF
strSQL = "SELECT * FROM Last4AuditResults-AuditedProviders WHERE "
strSQL = strSQL & "[ProviderID] = '" & rsCriteria![ProviderID] & "'"

db.QueryDefs.Delete "NewQuery"
Set qdf = db.CreateQueryDef("NewQuery", strSQL)

DoCmd.SendObject acReport, "Letters-AuditedProviders", "SnapshotFormat(*.
snp)", rsCriteria![EmailAddress], rsCriteria![MedDirEmail], "", "Electronic
Health Record Compliance Policy", "Please See Attached", False, ""
rsCriteria!Emailed = True
rsCriteria.MoveNext

Loop

rsCriteria.Close

Exit_SeparateEmails:
Exit Sub

Err_SeparateEmails:
If Err.Number = 3265 Or Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_SeparateEmails
End If

End Sub

 

[meenderingm]

I forgot to mention that when I try to run through this module and debug I
get an error stating - "Syntax error in FROM clause"

 

[Dirk Goldgar]

I have the following tables AuditTableList whose fiels are: ProviderID,
EmailAddress, DirEmail, OpsDirEmail, OtherEmail1 & OtherEmail2. I also
have
a table called Audit Results this table has the data for the report
“LettersToBeSent†that I want to email out as an attached Snapshot Viewer
file. The fields that link these two tables together is called
ProviderID.
I have the following code to loop through the recordsent of AuditTableList
and to send out the report to all of the providers in this list. The
report
that is sent to each provider should be only for that provider.
******************************************************************************************************************

Option Compare Database
Option Explicit



Sub SeparateEmails()

On Error GoTo Err_SeparateEmails

Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim rsGLTable As Recordset
Dim rsCriteria As Recordset

Set db = CurrentDb
Set rsCriteria = db.OpenRecordset("AuditTableList", dbOpenSnapshot)

rsCriteria.MoveFirst

Do Until rsCriteria.EOF
strSQL = "SELECT * FROM Last4AuditResults-AuditedProviders WHERE "
strSQL = strSQL & "[ProviderID] = '" & rsCriteria![ProviderID] & "'"

db.QueryDefs.Delete "NewQuery"
Set qdf = db.CreateQueryDef("NewQuery", strSQL)

DoCmd.SendObject acReport, "Letters-AuditedProviders",
"SnapshotFormat(*.
snp)", rsCriteria![EmailAddress], rsCriteria![MedDirEmail], "",
"Electronic
Health Record Compliance Policy", "Please See Attached", False, ""
rsCriteria!Emailed = True
rsCriteria.MoveNext

Loop

rsCriteria.Close

Exit_SeparateEmails:
Exit Sub

Err_SeparateEmails:
If Err.Number = 3265 Or Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_SeparateEmails
End If

End Sub

 

[Dirk Goldgar]

I forgot to mention that when I try to run through this module and debug I
get an error stating - "Syntax error in FROM clause"

Your SQL statement:

strSQL = "SELECT * FROM Last4AuditResults-AuditedProviders WHERE "
strSQL = strSQL & "[ProviderID] = '" & rsCriteria![ProviderID] & "'"

.... needs to have square brackets ([]) around the name
"Last4AuditResults-AuditedProviders", because it contains the minus sign.
Try this instead:

strSQL = "SELECT * FROM [Last4AuditResults-AuditedProviders] WHERE "
strSQL = strSQL & "[ProviderID] = '" & rsCriteria![ProviderID] & "'"

It's not a good idea to include characters other than letters, numbers, and
the underscore (_) in your object names, because they are misinterpreted.

 

[Dirk Goldgar]

(blank reply)

Sorry about the blank reply.

 

[meenderingm]

Ok, I made the change and now I get a different error message stating "An
expression you enetered is the wrong data type for one of the arguments." I
have taken and verified that both of my ProviderID fields are formatted the
same in both tables. I don't know if it is because the report that I am
trying to email out has 2 sub-reports built into the main report or if it is
something else.

I forgot to mention that when I try to run through this module and debug I
get an error stating - "Syntax error in FROM clause"

Your SQL statement:

strSQL = "SELECT * FROM Last4AuditResults-AuditedProviders WHERE "
strSQL = strSQL & "[ProviderID] = '" & rsCriteria![ProviderID] & "'"

... needs to have square brackets ([]) around the name
"Last4AuditResults-AuditedProviders", because it contains the minus sign.
Try this instead:

strSQL = "SELECT * FROM [Last4AuditResults-AuditedProviders] WHERE "
strSQL = strSQL & "[ProviderID] = '" & rsCriteria![ProviderID] & "'"

It's not a good idea to include characters other than letters, numbers, and
the underscore (_) in your object names, because they are misinterpreted.

 

[Dirk Goldgar]

Ok, I made the change and now I get a different error message stating "An
expression you enetered is the wrong data type for one of the arguments."
I
have taken and verified that both of my ProviderID fields are formatted
the
same in both tables. I don't know if it is because the report that I am
trying to email out has 2 sub-reports built into the main report or if it
is
something else.

It would help if you would indicate which statement was raising the error.
Since you have error-handling in place, you'd need to either temporarily
comment out the "On Error" statement, or else set a breakpoint and step
through the code line by line until it breaks.

As it is, the only obvious errors I can see offhand are these:

1. You are opening the recordset rsCriteria as a snapshot-type recordset.
That means it will not be updatable -- but then later you are trying to
update it.

2. When you do try to update it, you don't use the .Edit and .Update
methods. If you had opened an updatable recordset, you would still have to
call the .Edit method before updating any field in the current record, and
then the .Update method to save the updated record before moving to the next
record.

I'm not sure that either of those is the source of the particular error you
report, but I suggest you fix them, then try the code again. If the error
reappears, ascertain and report the specific line of code that is raising
the error, and if possible also report the values of all variables and
fields that are involved in that statement.

 

[meenderingm via AccessMonster.com]

Ok, I have all of the error messages fixed, however something still is not
write as the code does loop through the recordset however it is not limiting
my report that is emailed out to whichever ID the loop is currently on. I
think the issue has to do with this part of my code:

Do Until rsCriteria.EOF
strSQL = "SELECT * FROM Last4AuditResults_AuditedProviders WHERE "
strSQL = strSQL & "[ProviderID] = '" & rsCriteria![ProviderID] & "'"
db.QueryDefs.Delete "NewQuery"
Set qdf = db.CreateQueryDef("NewQuery", strSQL)

But I am not what the issue may be. Below is the full code:
*******************************************************************************************************************
Option Compare Database
Option Explicit

Sub SeparateEmails()

On Error GoTo Err_SeparateEmails

Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim rsGLTable As Recordset
Dim rsCriteria As Recordset

Set db = CurrentDb
Set rsCriteria = db.OpenRecordset("AuditTableList", dbOpenDynaset)

rsCriteria.MoveFirst

Do Until rsCriteria.EOF
strSQL = "SELECT * FROM Last4AuditResults_AuditedProviders WHERE "
strSQL = strSQL & "[ProviderID] = '" & rsCriteria![ProviderID] & "'"
db.QueryDefs.Delete "NewQuery"
Set qdf = db.CreateQueryDef("NewQuery", strSQL)

DoCmd.SendObject acSendReport, "Letters_AuditedProviders", acFormatSNP,
rsCriteria![EmailAddress], "", "", "Electronic Health Record Compliance
Policy", "Please See Attached", True, ""

rsCriteria.MoveNext

Loop

rsCriteria.Close

Exit_SeparateEmails:
Exit Sub

Err_SeparateEmails:
If Err.Number = 3265 Or Err.Number = 2501 Then
Resume Next

Else

MsgBox Err.Description
Resume Exit_SeparateEmails
End If

End Sub

*******************************************************************************************************************

Ok, I made the change and now I get a different error message stating "An
expression you enetered is the wrong data type for one of the arguments."

[quoted text clipped - 5 lines]

is
something else.

It would help if you would indicate which statement was raising the error.
Since you have error-handling in place, you'd need to either temporarily
comment out the "On Error" statement, or else set a breakpoint and step
through the code line by line until it breaks.

As it is, the only obvious errors I can see offhand are these:

1. You are opening the recordset rsCriteria as a snapshot-type recordset.
That means it will not be updatable -- but then later you are trying to
update it.

2. When you do try to update it, you don't use the .Edit and .Update
methods. If you had opened an updatable recordset, you would still have to
call the .Edit method before updating any field in the current record, and
then the .Update method to save the updated record before moving to the next
record.

I'm not sure that either of those is the source of the particular error you
report, but I suggest you fix them, then try the code again. If the error
reappears, ascertain and report the specific line of code that is raising
the error, and if possible also report the values of all variables and
fields that are involved in that statement.

 

[Dirk Goldgar]

Ok, I have all of the error messages fixed, however something still is not
write as the code does loop through the recordset however it is not
limiting
my report that is emailed out to whichever ID the loop is currently on. I
think the issue has to do with this part of my code:

Do Until rsCriteria.EOF
strSQL = "SELECT * FROM Last4AuditResults_AuditedProviders WHERE "
strSQL = strSQL & "[ProviderID] = '" & rsCriteria![ProviderID] & "'"
db.QueryDefs.Delete "NewQuery"
Set qdf = db.CreateQueryDef("NewQuery", strSQL)

But I am not what the issue may be. Below is the full code:
*******************************************************************************************************************
Option Compare Database
Option Explicit

Sub SeparateEmails()

On Error GoTo Err_SeparateEmails

Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim rsGLTable As Recordset
Dim rsCriteria As Recordset

Set db = CurrentDb
Set rsCriteria = db.OpenRecordset("AuditTableList", dbOpenDynaset)

rsCriteria.MoveFirst

Do Until rsCriteria.EOF
strSQL = "SELECT * FROM Last4AuditResults_AuditedProviders WHERE "
strSQL = strSQL & "[ProviderID] = '" & rsCriteria![ProviderID] & "'"
db.QueryDefs.Delete "NewQuery"
Set qdf = db.CreateQueryDef("NewQuery", strSQL)

DoCmd.SendObject acSendReport, "Letters_AuditedProviders", acFormatSNP,
rsCriteria![EmailAddress], "", "", "Electronic Health Record Compliance
Policy", "Please See Attached", True, ""

rsCriteria.MoveNext

Loop

rsCriteria.Close

Exit_SeparateEmails:
Exit Sub

Err_SeparateEmails:
If Err.Number = 3265 Or Err.Number = 2501 Then
Resume Next

Else

MsgBox Err.Description
Resume Exit_SeparateEmails
End If

End Sub

I see that you've removed the code to update the Emailed field in
AuditTableList.

What is the RecordSource property of the report, "Letters_AuditedProviders"?
Your code is deleting and recreating the stored query "NewQuery", so I would
expect that query to be the RecordSource of the report.

Incidentally, it would be more efficient to leave the query in existence and
just update its .SQL property on each pass through the loop. But I don't
want to confuse matters now. You can modify that later, if you like.