Send Object VBA Question

[Guest]

I finally got that to work correctly but am now experiencing another issue.
In the query that I want to loop through each Expr1 and send a separate
report for each, the attachments being sent out are all "510". I need to
send one out for each [Expr1]. Apparently the loop isn't working correctly.
I am at a loss since this code isn't clicking on how it works. Again I
really appreciate all of your help. I am posting the updated code below so
you can see what changed. Thanks.

Private Sub cmdEmailReports_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("qryAGHSvcCode")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)

qdf.SQL = strSQL
DoCmd.SendObject acSendReport, "rptAGHLateCharges",
"SnapshotFormat(*.snp)", !Recipient, , , "YourSubject", "YourMessage", False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing

End Sub

Since Expr1 is a computed field in the query, you can't use it in the WHERE
clause like that.

Instead, you need

And Left([SVCCODE], 3) = '510'

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

strBaseSQL reads the following:

SELECT [tblImportAGH].[SVCCODE], Left([SVCCODE],3) AS Expr1,
[tblImportAGH].[ACCT NUMBER], [tblImportAGH].[PT NAME], [tblImportAGH].[DOS],
[tblImportAGH].[DTL CR DATE], [tblImportAGH].[POST DATE],
[tblImportAGH].[AMOUNT], [tblImportAGH].[FILE DATE], [tblImportAGH].[InOut],
[POST DATE]-[DOS] AS Days
FROM tblImportAGH
WHERE ((([tblImportAGH].[FILE DATE])=#4/30/2005#)) And [Expr1]='510';

I did figure out the last part because I looked at the syntax and the last
line of the SQL code was reading:
WHERE ((([tblImportAGH].[FILE DATE])=#4/30/2005#)) WHERE [Expr1]='510';

I changed it to input the AND on [Expr1] instead of the second WHERE. I
hope that answers everything you needed. Thanks.

What does strBaseSQL look like?

I don't understand, though, how you could have gotten past the other
problem. This problem would occur before the other one!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I actually got the code to get past this error. I am now getting a new
error
of Error 3061 Too few parameters. I do have a file date on a form that
the
specified query uses. The form is open and populated when I run the
macro.
The code errors on the following line:
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
I think my head is going to explode from all of this code.
Thank you all so much for your help. All of you are extremely helpful.

:

It sounds to me the constructed SQL String is invalid.

Add the statement:

Debug.Print strSQL

just before the statement

qdf.SQL = strSQL

Run the code and you should see the constructed SQL String in the
Immediate
window. Copy and post it to the newsgroup.

I agreed with Douglas about removing the last 3 characters from the
source
SQL. IIRC, I think you need to remove the last character ( only, not
3.

--
HTH
Van T. Dinh
MVP (Access)




I have the following code which is for looping through each department
(Approximately 200) and sending a separate e-mail to the recipient
listed
in
a report. I am getting an error 3075( Missing operator) on line
qdf.SQL =
strSQL and can't figure out the issue. VBA newbie still trying to
figure
out
how to create bug free code. Thanks in advance for any assistance you
can
offer.

Private Sub cmdEmailReports_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("qryAGHSvcCode")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [Expr1]='" & ![Expr1] & " '"
qdf.SQL = strSQL
DoCmd.SendObject acSendReport, "rptAGHLateCharges",
"SnapshotFormat(*.snp)", !Recipient, , , "YourSubject", "YourMessage",
False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing

End Sub

 

[Pieter Wijnen]

The trick is to use a Forms Control to store the criteria & base the Reports
query on that
ie
Report QDef SQL:
PARAMETERS Forms!Criteria!SVCCODE Text;
SELECT * FROM Whatever
WHERE LEFT(SVCCODE,3) = Forms!Criteria!SVCCode

Then if you update the Forms!Criteria!SVCCODE control between each printout
you should be home free ...

HTH

Pieter

I finally got that to work correctly but am now experiencing another issue.
In the query that I want to loop through each Expr1 and send a separate
report for each, the attachments being sent out are all "510". I need to
send one out for each [Expr1]. Apparently the loop isn't working
correctly.
I am at a loss since this code isn't clicking on how it works. Again I
really appreciate all of your help. I am posting the updated code below
so
you can see what changed. Thanks.

Private Sub cmdEmailReports_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("qryAGHSvcCode")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)

qdf.SQL = strSQL
DoCmd.SendObject acSendReport, "rptAGHLateCharges",
"SnapshotFormat(*.snp)", !Recipient, , , "YourSubject", "YourMessage",
False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing

End Sub

Since Expr1 is a computed field in the query, you can't use it in the
WHERE
clause like that.

Instead, you need

And Left([SVCCODE], 3) = '510'

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

strBaseSQL reads the following:

SELECT [tblImportAGH].[SVCCODE], Left([SVCCODE],3) AS Expr1,
[tblImportAGH].[ACCT NUMBER], [tblImportAGH].[PT NAME], [tblImportAGH].[DOS],
[tblImportAGH].[DTL CR DATE], [tblImportAGH].[POST DATE],
[tblImportAGH].[AMOUNT], [tblImportAGH].[FILE DATE], [tblImportAGH].[InOut],
[POST DATE]-[DOS] AS Days
FROM tblImportAGH
WHERE ((([tblImportAGH].[FILE DATE])=#4/30/2005#)) And [Expr1]='510';

I did figure out the last part because I looked at the syntax and the
last
line of the SQL code was reading:
WHERE ((([tblImportAGH].[FILE DATE])=#4/30/2005#)) WHERE [Expr1]='510';

I changed it to input the AND on [Expr1] instead of the second WHERE.
I
hope that answers everything you needed. Thanks.

:

What does strBaseSQL look like?

I don't understand, though, how you could have gotten past the other
problem. This problem would occur before the other one!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I actually got the code to get past this error. I am now getting a new
error
of Error 3061 Too few parameters. I do have a file date on a form that
the
specified query uses. The form is open and populated when I run
the
macro.
The code errors on the following line:
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
I think my head is going to explode from all of this code.
Thank you all so much for your help. All of you are extremely helpful.

:

It sounds to me the constructed SQL String is invalid.

Add the statement:

Debug.Print strSQL

just before the statement

qdf.SQL = strSQL

Run the code and you should see the constructed SQL String in the
Immediate
window. Copy and post it to the newsgroup.

I agreed with Douglas about removing the last 3 characters from
the
source
SQL. IIRC, I think you need to remove the last character (
only, not
3.

--
HTH
Van T. Dinh
MVP (Access)




I have the following code which is for looping through each department
(Approximately 200) and sending a separate e-mail to the
recipient
listed
in
a report. I am getting an error 3075( Missing operator) on
line
qdf.SQL =
strSQL and can't figure out the issue. VBA newbie still trying
to
figure
out
how to create bug free code. Thanks in advance for any
assistance you
can
offer.

Private Sub cmdEmailReports_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("qryAGHSvcCode")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [Expr1]='" & ![Expr1] & " '"
qdf.SQL = strSQL
DoCmd.SendObject acSendReport, "rptAGHLateCharges",
"SnapshotFormat(*.snp)", !Recipient, , , "YourSubject", "YourMessage",
False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing

End Sub

 

[Guest]

Thank you all for you help with this. Unfortunately, every time I fixed
something I came across another problem like the loop not working or the
module not functioning. I went searching for some other code and found
something that is working perfectly. Below is the code that I am using in
case anyone is interested. I use Groupwise as my e-mail system so it is
compatible. Thanks again and I appreciate everyone taking the time to
respond to me.

Option Compare Database
Option Explicit
'**********************************
'Created by Roger Carlson *
'(e-mail address removed)*
'(e-mail address removed) *
'**********************************


Function SeparateEmails()
'*** error trapping - execution goes to bottom on error
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("tblAGHDepartments", dbOpenDynaset)

'*** the first record in the Criteria table ***
rsCriteria.MoveFirst

'*** loop to move through the records in Criteria table
Do Until rsCriteria.EOF
'*** create the Select query based on
' the first record in the Criteria table
strSQL = "SELECT* FROM tblImportAGH WHERE "
strSQL = strSQL & "[Expr1] = '" & rsCriteria![Department] & "' AND [File
Date]=#" & [Forms]![frmImport]![FileDate] & "#"


'MsgBox strSQL
'*** delete the previous query
db.QueryDefs.Delete "qryAGHSvcCode"
Set qdf = db.CreateQueryDef("qryAGHSvcCode", strSQL)

DoCmd.SendObject acReport, "rptAGHLateCharges", "RichTextFormat(*.rtf)",
rsCriteria![Recipient], "", "", "This is a test", "I am testing a new idea
for reports", False, ""


'*** goto the next record in Criteria table
ContinueToNext:
rsCriteria.MoveNext

Loop

rsCriteria.Close

Exit_SeparateEmails:
Exit Function

Err_SeparateEmails: '*** if there is an error, execution goes here
'*** if the error is the table or query missing (3265)
' then skip the delete line and resume on the next line
' Error 2501 notifies you that the SendObject action
' has been cancelled. See the OnNoData Event of the report.
If Err.Number = 3265 Or Err.Number = 2501 Then
Resume ContinueToNext
Else
'*** write out the error and exit the sub
MsgBox Err.Description
Resume Exit_SeparateEmails
End If

End Function

The trick is to use a Forms Control to store the criteria & base the Reports
query on that
ie
Report QDef SQL:
PARAMETERS Forms!Criteria!SVCCODE Text;
SELECT * FROM Whatever
WHERE LEFT(SVCCODE,3) = Forms!Criteria!SVCCode

Then if you update the Forms!Criteria!SVCCODE control between each printout
you should be home free ...

HTH

Pieter

I finally got that to work correctly but am now experiencing another issue.
In the query that I want to loop through each Expr1 and send a separate
report for each, the attachments being sent out are all "510". I need to
send one out for each [Expr1]. Apparently the loop isn't working
correctly.
I am at a loss since this code isn't clicking on how it works. Again I
really appreciate all of your help. I am posting the updated code below
so
you can see what changed. Thanks.

Private Sub cmdEmailReports_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("qryAGHSvcCode")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)

qdf.SQL = strSQL
DoCmd.SendObject acSendReport, "rptAGHLateCharges",
"SnapshotFormat(*.snp)", !Recipient, , , "YourSubject", "YourMessage",
False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing

End Sub

Since Expr1 is a computed field in the query, you can't use it in the
WHERE
clause like that.

Instead, you need

And Left([SVCCODE], 3) = '510'

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


strBaseSQL reads the following:

SELECT [tblImportAGH].[SVCCODE], Left([SVCCODE],3) AS Expr1,
[tblImportAGH].[ACCT NUMBER], [tblImportAGH].[PT NAME],
[tblImportAGH].[DOS],
[tblImportAGH].[DTL CR DATE], [tblImportAGH].[POST DATE],
[tblImportAGH].[AMOUNT], [tblImportAGH].[FILE DATE],
[tblImportAGH].[InOut],
[POST DATE]-[DOS] AS Days
FROM tblImportAGH
WHERE ((([tblImportAGH].[FILE DATE])=#4/30/2005#)) And [Expr1]='510';

I did figure out the last part because I looked at the syntax and the
last
line of the SQL code was reading:
WHERE ((([tblImportAGH].[FILE DATE])=#4/30/2005#)) WHERE [Expr1]='510';

I changed it to input the AND on [Expr1] instead of the second WHERE.
I
hope that answers everything you needed. Thanks.

:

What does strBaseSQL look like?

I don't understand, though, how you could have gotten past the other
problem. This problem would occur before the other one!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I actually got the code to get past this error. I am now getting a
new
error
of Error 3061 Too few parameters. I do have a file date on a form
that
the
specified query uses. The form is open and populated when I run
the
macro.
The code errors on the following line:
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
I think my head is going to explode from all of this code.
Thank you all so much for your help. All of you are extremely
helpful.

:

It sounds to me the constructed SQL String is invalid.

Add the statement:

Debug.Print strSQL

just before the statement

qdf.SQL = strSQL

Run the code and you should see the constructed SQL String in the
Immediate
window. Copy and post it to the newsgroup.

I agreed with Douglas about removing the last 3 characters from
the
source
SQL. IIRC, I think you need to remove the last character (
only,
not
3.

--
HTH
Van T. Dinh
MVP (Access)




I have the following code which is for looping through each
department
(Approximately 200) and sending a separate e-mail to the
recipient
listed
in
a report. I am getting an error 3075( Missing operator) on
line
qdf.SQL =
strSQL and can't figure out the issue. VBA newbie still trying
to
figure
out
how to create bug free code. Thanks in advance for any
assistance
you
can
offer.

Private Sub cmdEmailReports_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("qryAGHSvcCode")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [Expr1]='" & ![Expr1] & " '"
qdf.SQL = strSQL
DoCmd.SendObject acSendReport, "rptAGHLateCharges",
"SnapshotFormat(*.snp)", !Recipient, , , "YourSubject",
"YourMessage",
False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing

End Sub