A
A Lesner
I want to send a report to email addesses listed in a table.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
S
Steve Schapel
A Lesner,
Well, you could try using the DoCmd.SendObject method. Open a recordset
based on the table and loop through it to build a string of all the
email addresses, and then use this string variable in the To argument of
the code.
Well, you could try using the DoCmd.SendObject method. Open a recordset
based on the table and loop through it to build a string of all the
email addresses, and then use this string variable in the To argument of
the code.
A
A Lesner
Steve, I am confused on how to use the doCmd.SendObject. I do not use
visual basic, I use MS access. I was trying to use a macro to send a report.
The report data changes for each email address. Each email address also has
an id with data attached, see table below. How can I send report A to
only include data with [id]. For example,
send report A to x@com only include data with id A1
send report A to b@com only include data with id B2
email id data fields
[email protected] A1 20, 50, 30
[email protected] B2 20, 80, 60
[email protected] x2 40, 60. 100
visual basic, I use MS access. I was trying to use a macro to send a report.
The report data changes for each email address. Each email address also has
an id with data attached, see table below. How can I send report A to
only include data with [id]. For example,
send report A to x@com only include data with id A1
send report A to b@com only include data with id B2
email id data fields
[email protected] A1 20, 50, 30
[email protected] B2 20, 80, 60
[email protected] x2 40, 60. 100
S
Steve Schapel
A Lesner,
VBA is the programming language that is part of MS Access, it's not a
separate thing. Access macros are very cool, and it would be possible
to do what you're asking with a SendObject macro. But for this type of
thing, where you are repeating a process for each record in a data set,
using VBA is a lot easier, as it provides for looping code, where macros
do not. Still, there would be a learning curve involved. And I did
previously misunderstand you, as I thought you wanted the same report to
each person.
VBA is the programming language that is part of MS Access, it's not a
separate thing. Access macros are very cool, and it would be possible
to do what you're asking with a SendObject macro. But for this type of
thing, where you are repeating a process for each record in a data set,
using VBA is a lot easier, as it provides for looping code, where macros
do not. Still, there would be a learning curve involved. And I did
previously misunderstand you, as I thought you wanted the same report to
each person.
A
A Lesner
Steve,
I know how to do a send object, but that requires me to type each email
address. I have 8000 email addresses. Can access pick email addresses out
of a table if I tell it to pick the address where the id = A1? If so, can
you give me an idea of what the VBA would look like and where I put the VBA?
Angie 313-667-0646.
1) What would VBA look like, please fix example below:
DoCMd.OPenReport "A", [id]='A1', send to email listed??
2) Where do I put the VBA?
I know how to do a send object, but that requires me to type each email
address. I have 8000 email addresses. Can access pick email addresses out
of a table if I tell it to pick the address where the id = A1? If so, can
you give me an idea of what the VBA would look like and where I put the VBA?
Angie 313-667-0646.
1) What would VBA look like, please fix example below:
DoCMd.OPenReport "A", [id]='A1', send to email listed??
2) Where do I put the VBA?
Steve Schapel said:A Lesner,
VBA is the programming language that is part of MS Access, it's not a
separate thing. Access macros are very cool, and it would be possible
to do what you're asking with a SendObject macro. But for this type of
thing, where you are repeating a process for each record in a data set,
using VBA is a lot easier, as it provides for looping code, where macros
do not. Still, there would be a learning curve involved. And I did
previously misunderstand you, as I thought you wanted the same report to
each person.
--
Steve Schapel, Microsoft Access MVP
A said:Steve, I am confused on how to use the doCmd.SendObject. I do not use
visual basic, I use MS access. I was trying to use a macro to send a report.
The report data changes for each email address. Each email address also has
an id with data attached, see table below. How can I send report A to
only include data with [id]. For example,
send report A to x@com only include data with id A1
send report A to b@com only include data with id B2
email id data fields
[email protected] A1 20, 50, 30
[email protected] B2 20, 80, 60
[email protected] x2 40, 60. 100
[/QUOTE]
[/QUOTE]
A
A Lesner
How can I email a report to email addresses listed in a table? 3rd Request
email id data field1, data field2, data field3
[email protected] A1 20, 50, 30
[email protected] B2 20, 80, 60
[email protected] x2 40, 60. 100
email id data field1, data field2, data field3
[email protected] A1 20, 50, 30
[email protected] B2 20, 80, 60
[email protected] x2 40, 60. 100
A Lesner said:Steve,
I know how to do a send object, but that requires me to type each email
address. I have 8000 email addresses. Can access pick email addresses out
of a table if I tell it to pick the address where the id = A1? If so, can
you give me an idea of what the VBA would look like and where I put the VBA?
Angie 313-667-0646.
1) What would VBA look like, please fix example below:
DoCMd.OPenReport "A", [id]='A1', send to email listed??
2) Where do I put the VBA?
Steve Schapel said:A Lesner,
VBA is the programming language that is part of MS Access, it's not a
separate thing. Access macros are very cool, and it would be possible
to do what you're asking with a SendObject macro. But for this type of
thing, where you are repeating a process for each record in a data set,
using VBA is a lot easier, as it provides for looping code, where macros
do not. Still, there would be a learning curve involved. And I did
previously misunderstand you, as I thought you wanted the same report to
each person.
--
Steve Schapel, Microsoft Access MVP
A said:Steve, I am confused on how to use the doCmd.SendObject. I do not use
visual basic, I use MS access. I was trying to use a macro to send a report.
The report data changes for each email address. Each email address also has
an id with data attached, see table below. How can I send report A to
only include data with [id]. For example,
send report A to x@com only include data with id A1
send report A to b@com only include data with id B2
email id data fields
[email protected] A1 20, 50, 30
[email protected] B2 20, 80, 60
[email protected] x2 40, 60. 100
[/QUOTE]
[/QUOTE][/QUOTE]
S
Steve Schapel
Angie,
You would not use OpenReport method. This is for printing or previewing
the report. If you want to email it, there are a number of approaches.
The simplest to implement is to use SendObject. I have never used
SendObject to send more than a few emails at a time, so I would be
interested to see how it handles 8000. In the query that your report is
based on (called TheQuery in the example below), do not use any criteria
relating to the id field. Then, you will need code something like this
(caution: untested "air code")...
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("TheQuery")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [id]='" & ![id] & "'"
qdf.SQL = strSQL
DoCmd.SendObject acSendReport, "YourReport",
"SnapshotFormat(*.snp)", !Email, , , "YourSubject", "YourMessage", False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
SET qdf = Nothing
SET rst = Nothing
Another approach would be to use Office Automation to compose the email
in Outlook. There is some good information on this at
http://www.granite.ab.ca/access/email/outlook.htm and at
http://support.microsoft.com/?kbid=161088
As for where to put the VBA, you need to decide when you want this to
happen. Most likely you do it on the Click event of a Command Button of
a form? In the design view of the form, find the On Click event
property of the button, enter [Event Procedure], click the ellipsis
button (...) to the right of the property line, end you are taken to the
VB Editor window.
You would not use OpenReport method. This is for printing or previewing
the report. If you want to email it, there are a number of approaches.
The simplest to implement is to use SendObject. I have never used
SendObject to send more than a few emails at a time, so I would be
interested to see how it handles 8000. In the query that your report is
based on (called TheQuery in the example below), do not use any criteria
relating to the id field. Then, you will need code something like this
(caution: untested "air code")...
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("TheQuery")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [id]='" & ![id] & "'"
qdf.SQL = strSQL
DoCmd.SendObject acSendReport, "YourReport",
"SnapshotFormat(*.snp)", !Email, , , "YourSubject", "YourMessage", False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
SET qdf = Nothing
SET rst = Nothing
Another approach would be to use Office Automation to compose the email
in Outlook. There is some good information on this at
http://www.granite.ab.ca/access/email/outlook.htm and at
http://support.microsoft.com/?kbid=161088
As for where to put the VBA, you need to decide when you want this to
happen. Most likely you do it on the Click event of a Command Button of
a form? In the design view of the form, find the On Click event
property of the button, enter [Event Procedure], click the ellipsis
button (...) to the right of the property line, end you are taken to the
VB Editor window.
A
A Lesner
Steve,
I really appreciate your help!! It seems that this shouldn't be that hard.
Yet, no matter what I do, I can't seem to get this to work.
When I run the code you sent, it stops at
Set ...at set qdf
with compile error "invalid outside procedure"
I don't see anything in the help section regarding this.
I really appreciate your help!! It seems that this shouldn't be that hard.
Yet, no matter what I do, I can't seem to get this to work.
When I run the code you sent, it stops at
Set ...at set qdf
with compile error "invalid outside procedure"
I don't see anything in the help section regarding this.
Steve Schapel said:Angie,
You would not use OpenReport method. This is for printing or previewing
the report. If you want to email it, there are a number of approaches.
The simplest to implement is to use SendObject. I have never used
SendObject to send more than a few emails at a time, so I would be
interested to see how it handles 8000. In the query that your report is
based on (called TheQuery in the example below), do not use any criteria
relating to the id field. Then, you will need code something like this
(caution: untested "air code")...
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("TheQuery")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [id]='" & ![id] & "'"
qdf.SQL = strSQL
DoCmd.SendObject acSendReport, "YourReport",
"SnapshotFormat(*.snp)", !Email, , , "YourSubject", "YourMessage", False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
SET qdf = Nothing
SET rst = Nothing
Another approach would be to use Office Automation to compose the email
in Outlook. There is some good information on this at
http://www.granite.ab.ca/access/email/outlook.htm and at
http://support.microsoft.com/?kbid=161088
As for where to put the VBA, you need to decide when you want this to
happen. Most likely you do it on the Click event of a Command Button of
a form? In the design view of the form, find the On Click event
property of the button, enter [Event Procedure], click the ellipsis
button (...) to the right of the property line, end you are taken to the
VB Editor window.
--
Steve Schapel, Microsoft Access MVP
A said:Steve,
I know how to do a send object, but that requires me to type each email
address. I have 8000 email addresses. Can access pick email addresses out
of a table if I tell it to pick the address where the id = A1? If so, can
you give me an idea of what the VBA would look like and where I put the VBA?
Angie 313-667-0646.
1) What would VBA look like, please fix example below:
DoCMd.OPenReport "A", [id]='A1', send to email listed??
2) Where do I put the VBA?
A
A Lesner
Is it possible to direct the "To" of the SendObject macro to a table?
Steve Schapel said:Angie,
You would not use OpenReport method. This is for printing or previewing
the report. If you want to email it, there are a number of approaches.
The simplest to implement is to use SendObject. I have never used
SendObject to send more than a few emails at a time, so I would be
interested to see how it handles 8000. In the query that your report is
based on (called TheQuery in the example below), do not use any criteria
relating to the id field. Then, you will need code something like this
(caution: untested "air code")...
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("TheQuery")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [id]='" & ![id] & "'"
qdf.SQL = strSQL
DoCmd.SendObject acSendReport, "YourReport",
"SnapshotFormat(*.snp)", !Email, , , "YourSubject", "YourMessage", False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
SET qdf = Nothing
SET rst = Nothing
Another approach would be to use Office Automation to compose the email
in Outlook. There is some good information on this at
http://www.granite.ab.ca/access/email/outlook.htm and at
http://support.microsoft.com/?kbid=161088
As for where to put the VBA, you need to decide when you want this to
happen. Most likely you do it on the Click event of a Command Button of
a form? In the design view of the form, find the On Click event
property of the button, enter [Event Procedure], click the ellipsis
button (...) to the right of the property line, end you are taken to the
VB Editor window.
--
Steve Schapel, Microsoft Access MVP
A said:Steve,
I know how to do a send object, but that requires me to type each email
address. I have 8000 email addresses. Can access pick email addresses out
of a table if I tell it to pick the address where the id = A1? If so, can
you give me an idea of what the VBA would look like and where I put the VBA?
Angie 313-667-0646.
1) What would VBA look like, please fix example below:
DoCMd.OPenReport "A", [id]='A1', send to email listed??
2) Where do I put the VBA?
S
Steve Schapel
Angie,
When you are in the VB Editor window, have a look in the
Tools|References menu, and see if there is a tick next to Microsoft DAO
3.6 Object Library. What version of Access are you using? Can you
copy/paste the code you are trying? Did you put in the name of your own
query?
When you are in the VB Editor window, have a look in the
Tools|References menu, and see if there is a tick next to Microsoft DAO
3.6 Object Library. What version of Access are you using? Can you
copy/paste the code you are trying? Did you put in the name of your own
query?
S
Steve Schapel
Angie,
No. You could use a continuous view form, and refer to the email
address of the current record, and then a GoToRecord/Next action in the
macro and repeat the macro for each record on the form.
No. You could use a continuous view form, and refer to the email
address of the current record, and then a GoToRecord/Next action in the
macro and repeat the macro for each record on the form.
A
A Lesner
Steve,
Maybe I am doing this wrong. I do not use VBA. I placed the code listed
below in a new module which access also made me save as a macro. There is a
tick next to Microsoft DAo 3.6 Object Library. Please note, I am trying to
send a report, not send a form. Should I place this somewhere else on the
actual report?
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("TheQuery")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [id]='" & ![id] & "'"
qdf.SQL = strSQL
DoCmd.sendobject acSendReport, "TrialRpt", "Rich Text
Format(*.rtf)", !Email, , , "YourSubject", "YourMessage", False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing
Sub x()
End Sub
Maybe I am doing this wrong. I do not use VBA. I placed the code listed
below in a new module which access also made me save as a macro. There is a
tick next to Microsoft DAo 3.6 Object Library. Please note, I am trying to
send a report, not send a form. Should I place this somewhere else on the
actual report?
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("TheQuery")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [id]='" & ![id] & "'"
qdf.SQL = strSQL
DoCmd.sendobject acSendReport, "TrialRpt", "Rich Text
Format(*.rtf)", !Email, , , "YourSubject", "YourMessage", False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing
Sub x()
End Sub
S
Steve Schapel
Angie,
As mentioned in my other post, this code should be assigned in the event
property of where you want to trigger this emailing from. It will have
to be an event on the form, and I guess the standard would be the Click
event of a Command Button on the form. The code is not associated with
the report. It is code to cause the report to be outputted to an emal
attachment, and refers to field values on the form.
I do not know where the Sub(x) at the end of the code came from, but I
don't think it should be there.
I do not understand what you mean "access also made me save as a macro".
This is not how it works.
Do you actually have a query called "TheQuery"? If so, is this the
query that the report is based on? If not, you need to replace the
"TheQuery" in the code with the actual name of your actual query.
Hope that helps. We will get it working eventually! Please understand
that what you are trying to do is not a simple or trivial matter.
As mentioned in my other post, this code should be assigned in the event
property of where you want to trigger this emailing from. It will have
to be an event on the form, and I guess the standard would be the Click
event of a Command Button on the form. The code is not associated with
the report. It is code to cause the report to be outputted to an emal
attachment, and refers to field values on the form.
I do not know where the Sub(x) at the end of the code came from, but I
don't think it should be there.
I do not understand what you mean "access also made me save as a macro".
This is not how it works.
Do you actually have a query called "TheQuery"? If so, is this the
query that the report is based on? If not, you need to replace the
"TheQuery" in the code with the actual name of your actual query.
Hope that helps. We will get it working eventually! Please understand
that what you are trying to do is not a simple or trivial matter.
A
A Lesner
Steve,
In form design mode, I made a command button went to properties went
to event tab went to "On Click" it made me choose expression builder, macro
builder or code builder. I picked code builder and pasted the code in the
declarations window under option compare database
When I pressed my button
I received an error which highlighted the word "Left" in the line of code
below. The error said Compile error, can't find project or library
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
This is what the entire code looked like:
Option Compare Database
Private Sub Command0_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("TheQuery")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [id]='" & ![id] & "'"
qdf.SQL = strSQL
DoCmd.sendobject acSendReport, "TrialRpt", "Rich Text
Format(*.rtf)", !Email, , , "YourSubject", "YourMessage", False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing
End Sub
In form design mode, I made a command button went to properties went
to event tab went to "On Click" it made me choose expression builder, macro
builder or code builder. I picked code builder and pasted the code in the
declarations window under option compare database
When I pressed my button
I received an error which highlighted the word "Left" in the line of code
below. The error said Compile error, can't find project or library
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
This is what the entire code looked like:
Option Compare Database
Private Sub Command0_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("TheQuery")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [id]='" & ![id] & "'"
qdf.SQL = strSQL
DoCmd.sendobject acSendReport, "TrialRpt", "Rich Text
Format(*.rtf)", !Email, , , "YourSubject", "YourMessage", False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing
End Sub
Steve Schapel said:Angie,
As mentioned in my other post, this code should be assigned in the event
property of where you want to trigger this emailing from. It will have
to be an event on the form, and I guess the standard would be the Click
event of a Command Button on the form. The code is not associated with
the report. It is code to cause the report to be outputted to an emal
attachment, and refers to field values on the form.
I do not know where the Sub(x) at the end of the code came from, but I
don't think it should be there.
I do not understand what you mean "access also made me save as a macro".
This is not how it works.
Do you actually have a query called "TheQuery"? If so, is this the
query that the report is based on? If not, you need to replace the
"TheQuery" in the code with the actual name of your actual query.
Hope that helps. We will get it working eventually! Please understand
that what you are trying to do is not a simple or trivial matter.
--
Steve Schapel, Microsoft Access MVP
A said:Steve,
Maybe I am doing this wrong. I do not use VBA. I placed the code listed
below in a new module which access also made me save as a macro. There is a
tick next to Microsoft DAo 3.6 Object Library. Please note, I am trying to
send a report, not send a form. Should I place this somewhere else on the
actual report?
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("TheQuery")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [id]='" & ![id] & "'"
qdf.SQL = strSQL
DoCmd.sendobject acSendReport, "TrialRpt", "Rich Text
Format(*.rtf)", !Email, , , "YourSubject", "YourMessage", False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing
Sub x()
End Sub
S
Steve Schapel
Angie,
As I mentioned in my earlier post, do you actually have a query called
"TheQuery"? If so, is this the query that the report is based on? If
not, you need to replace the "TheQuery" in the code with the actual name
of your actual query.
After you have taken care of that, go to the VB Editor again, and under
the line at the top of the module where it says Option Compare Database,
add another line that says...
Option Explicit
Then, go to the Debug menu, and click the Compile menu item at the top.
Let us know if any error messages are thrown up.
As I mentioned in my earlier post, do you actually have a query called
"TheQuery"? If so, is this the query that the report is based on? If
not, you need to replace the "TheQuery" in the code with the actual name
of your actual query.
After you have taken care of that, go to the VB Editor again, and under
the line at the top of the module where it says Option Compare Database,
add another line that says...
Option Explicit
Then, go to the Debug menu, and click the Compile menu item at the top.
Let us know if any error messages are thrown up.
A
A Lesner
Steve,
My report is based on a query called "TheQuery". I did what you said and
pressed the compile button. I continue to receive an error which highlighted
the word "Left" in the line of code below. The error said Compile error,
can't find project or library
at line: strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
My code now looks like this:
Option Compare Database
Option Explicit
Private Sub Command0_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("TheQuery")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [id]='" & ![id] & "'"
qdf.SQL = strSQL
DoCmd.sendobject acSendReport, "TrialRpt", "Rich Text
Format(*.rtf)", !Email, , , "YourSubject", "YourMessage", False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing
End Sub
Option Compare Database
Option Explicit
Private Sub Command0_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("TheQuery")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [id]='" & ![id] & "'"
qdf.SQL = strSQL
DoCmd.sendobject acSendReport, "TrialRpt", "Rich Text
Format(*.rtf)", !Email, , , "YourSubject", "YourMessage", False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing
End Sub
My report is based on a query called "TheQuery". I did what you said and
pressed the compile button. I continue to receive an error which highlighted
the word "Left" in the line of code below. The error said Compile error,
can't find project or library
at line: strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
My code now looks like this:
Option Compare Database
Option Explicit
Private Sub Command0_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("TheQuery")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [id]='" & ![id] & "'"
qdf.SQL = strSQL
DoCmd.sendobject acSendReport, "TrialRpt", "Rich Text
Format(*.rtf)", !Email, , , "YourSubject", "YourMessage", False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing
End Sub
Option Compare Database
Option Explicit
Private Sub Command0_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("TheQuery")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [id]='" & ![id] & "'"
qdf.SQL = strSQL
DoCmd.sendobject acSendReport, "TrialRpt", "Rich Text
Format(*.rtf)", !Email, , , "YourSubject", "YourMessage", False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing
End Sub
Steve Schapel said:Angie,
As I mentioned in my earlier post, do you actually have a query called
"TheQuery"? If so, is this the query that the report is based on? If
not, you need to replace the "TheQuery" in the code with the actual name
of your actual query.
After you have taken care of that, go to the VB Editor again, and under
the line at the top of the module where it says Option Compare Database,
add another line that says...
Option Explicit
Then, go to the Debug menu, and click the Compile menu item at the top.
Let us know if any error messages are thrown up.
--
Steve Schapel, Microsoft Access MVP
A said:Steve,
In form design mode, I made a command button went to properties went
to event tab went to "On Click" it made me choose expression builder, macro
builder or code builder. I picked code builder and pasted the code in the
declarations window under option compare database
When I pressed my button
I received an error which highlighted the word "Left" in the line of code
below. The error said Compile error, can't find project or library
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
This is what the entire code looked like:
Option Compare Database
Private Sub Command0_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("TheQuery")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [id]='" & ![id] & "'"
qdf.SQL = strSQL
DoCmd.sendobject acSendReport, "TrialRpt", "Rich Text
Format(*.rtf)", !Email, , , "YourSubject", "YourMessage", False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing
End Sub
S
Steve Schapel
Angie,
Sorry, this seems to be more complicated than it should be. The first
thing I would suspect is a References problem. In the VB Editor window,
please go to the Tools|References menu, and see if any of the items
listed there says "MISSING:..." If so, please refer to
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html
Sorry, this seems to be more complicated than it should be. The first
thing I would suspect is a References problem. In the VB Editor window,
please go to the Tools|References menu, and see if any of the items
listed there says "MISSING:..." If so, please refer to
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html
A
A Lesner
Steve,
I went to the VB Editor - Tools|References. I do not have an item that says
"missing". In fact, multiple items are checked, the only items that are not
checked are:
Indeo video custom control type library
Microsoft Active X data Objects 2.0 Library
Microsoft Active X data Objects 2.5 Library
Microsoft Active X Data Objects Recordset 2.5 Library
Microsoft DirectX Transform 1.0 Type Library
Microsoft Disk Quota 1.0
Microsoft Office Temlate and Media Control 1.0 Type
Microsoft Office Web Discussions Client Type Library
Microsoft Visual Basic for Applications Extensibilityv5.3
Microsoft XML, v5.0
Microsoft XML, version 2.0
MSPMSP 1.0 Type Library
NodeMgr 1.0 Type Library
PDCube 2.0 Type Library
Visual Basic for Applications
Visual Basic for Applications
Visual Basic for Applications
Visual Basic for Applications
Visual Basic for Applications
Wfxrstrz 1.0 Type Library
Those items are not checked, are not able to be checked due to conflictions
with other options already checked, etc.
Your web address
"http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html" is no
longer in service. I receive and error: Web sites previously located at
http://members.rogers.com/ are no longer accessible at this location. Rogers
Yahoo! Hi-Speed Internet members now have the ability to host their Members
sites on the new Rogers Yahoo! GeoCities service.
I went to the VB Editor - Tools|References. I do not have an item that says
"missing". In fact, multiple items are checked, the only items that are not
checked are:
Indeo video custom control type library
Microsoft Active X data Objects 2.0 Library
Microsoft Active X data Objects 2.5 Library
Microsoft Active X Data Objects Recordset 2.5 Library
Microsoft DirectX Transform 1.0 Type Library
Microsoft Disk Quota 1.0
Microsoft Office Temlate and Media Control 1.0 Type
Microsoft Office Web Discussions Client Type Library
Microsoft Visual Basic for Applications Extensibilityv5.3
Microsoft XML, v5.0
Microsoft XML, version 2.0
MSPMSP 1.0 Type Library
NodeMgr 1.0 Type Library
PDCube 2.0 Type Library
Visual Basic for Applications
Visual Basic for Applications
Visual Basic for Applications
Visual Basic for Applications
Visual Basic for Applications
Wfxrstrz 1.0 Type Library
Those items are not checked, are not able to be checked due to conflictions
with other options already checked, etc.
Your web address
"http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html" is no
longer in service. I receive and error: Web sites previously located at
http://members.rogers.com/ are no longer accessible at this location. Rogers
Yahoo! Hi-Speed Internet members now have the ability to host their Members
sites on the new Rogers Yahoo! GeoCities service.
Angie,
Sorry, this seems to be more complicated than it should be. The first
thing I would suspect is a References problem. In the VB Editor window,
please go to the Tools|References menu, and see if any of the items
listed there says "MISSING:..." If so, please refer to
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html
--
Steve Schapel, Microsoft Access MVP
A said:Steve,
My report is based on a query called "TheQuery". I did what you said and
pressed the compile button. I continue to receive an error which highlighted
the word "Left" in the line of code below. The error said Compile error,
can't find project or library
at line: strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
My code now looks like this:
Option Compare Database
Option Explicit
Private Sub Command0_Click()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBaseSQL As String
Set qdf = CurrentDb.QueryDefs("TheQuery")
strBaseSQL = qdf.SQL
Set rst = CurrentDb.OpenRecordset(strBaseSQL)
With rst
Do Until .EOF
strSQL = Left(strBaseSQL, Len(strBaseSQL) - 3)
strSQL = strSQL & " WHERE [id]='" & ![id] & "'"
qdf.SQL = strSQL
DoCmd.sendobject acSendReport, "TrialRpt", "Rich Text
Format(*.rtf)", !Email, , , "YourSubject", "YourMessage", False
.MoveNext
Loop
.Close
End With
qdf.SQL = strBaseSQL
Set qdf = Nothing
Set rst = Nothing
End Sub