Passing Criteria to SQL Statement

M

Mallasch

I am trying to create some code that will look at a list of records in a
table, find any records dealing with specific customers, then sending an
email to those customers including an rtf file listing all records.

I believe that I got most of the code that I need but I am running into one
problem. I need for the tables that are created and attached to the emails
only to include the records for that customer. I created an SQL statement to
make a table of records but I am not sure how to pass the field [SAMMS] back
to the SQL statement so it only creates a table for that specific customer.

Any ideas?

Public Sub RunEmailDist()
Dim MyDB As Object
Dim MyRecs As Object
Dim MyName As String
Dim SQL As String
Set MyDB = CurrentDb()
Set MyRecs = MyDB.OpenRecordset("qryEmailDistroList")

SQL = "SELECT tblSAMMSTracking.SAMMS " & _
"INTO temp " & _
"FROM tblSAMMSTracking " & _
"WHERE tblSAMMSTracking.SAMMS = MyRecs!SAMMS"

DoCmd.SetWarnings False

DoCmd.OpenQuery "qryEmailDistroStep1", acViewNormal, acReadOnly
DoCmd.OpenQuery "qryEmailDistroStep2", acViewNormal, acReadOnly

MyRecs.MoveFirst

Do While Not MyRecs.EOF

DoCmd.RunSQL SQL

DoCmd.SendObject acSendTable, "temp", acFormatRTF, MyRecs!CompanyEmail, , ,
"Advanced Shipment Notification", _
"Please see the attached document showing all shipments made yesterday:", 0

MyRecs.MoveNext

Loop

MyRecs.Close

End Sub
 
O

Ofer

You can pass a variable to the sub
Public Sub RunEmailDist(MySAMMS as Long)

And Change the SQL to
SQL = "SELECT tblSAMMSTracking.SAMMS " & _
"INTO temp " & _
"FROM tblSAMMSTracking " & _
"WHERE tblSAMMSTracking.SAMMS = " & MySAMMS
==========================================
If the SAMMS type is string then
Public Sub RunEmailDist(MySAMMS as string)

And Change the SQL to
SQL = "SELECT tblSAMMSTracking.SAMMS " & _
"INTO temp " & _
"FROM tblSAMMSTracking " & _
"WHERE tblSAMMSTracking.SAMMS = '" & MySAMMS & "'"
==========================================
 
M

Mallasch

Below did not work for me. When I click run, a macros box pops up and the
module does not run. Keep in mind that I am basically trying to pass along a
single field down to the SQL statement for the current record that is
selected in the main module. My current code is:
************************************************************
Option Explicit
Sub RunEmailDist(SAMMS As String)
Dim MyDB As Object
Dim MyRecs As Object
Dim SQL As String
Dim SAMMS As String
Set MyDB = CurrentDb()
Set MyRecs = MyDB.OpenRecordset("qryEmailDistroList")

SQL = "SELECT tblSAMMSTracking.SAMMS " & _
"INTO temp " & _
"FROM tblSAMMSTracking " & _
"WHERE tblSAMMSTracking.SAMMS = '" & SAMMS & "'"

DoCmd.SetWarnings False

DoCmd.OpenQuery "qryEmailDistroStep1", acViewNormal, acReadOnly
DoCmd.OpenQuery "qryEmailDistroStep2", acViewNormal, acReadOnly

MyRecs.MoveFirst

Do While Not MyRecs.EOF


DoCmd.RunSQL SQL

DoCmd.SendObject acSendTable, "temp", acFormatRTF, MyRecs!CompanyEmail, , ,
"Advanced Shipment Notification", _
"Please see the attached document showing all shipments made yesterday:", 0

MyRecs.MoveNext

Loop

MyRecs.Close

End Sub

************************************************************
Any suggestions?

Ofer said:
You can pass a variable to the sub
Public Sub RunEmailDist(MySAMMS as Long)

And Change the SQL to
SQL = "SELECT tblSAMMSTracking.SAMMS " & _
"INTO temp " & _
"FROM tblSAMMSTracking " & _
"WHERE tblSAMMSTracking.SAMMS = " & MySAMMS
==========================================
If the SAMMS type is string then
Public Sub RunEmailDist(MySAMMS as string)

And Change the SQL to
SQL = "SELECT tblSAMMSTracking.SAMMS " & _
"INTO temp " & _
"FROM tblSAMMSTracking " & _
"WHERE tblSAMMSTracking.SAMMS = '" & MySAMMS & "'"
==========================================
Mallasch said:
I am trying to create some code that will look at a list of records in a
table, find any records dealing with specific customers, then sending an
email to those customers including an rtf file listing all records.

I believe that I got most of the code that I need but I am running into one
problem. I need for the tables that are created and attached to the emails
only to include the records for that customer. I created an SQL statement to
make a table of records but I am not sure how to pass the field [SAMMS] back
to the SQL statement so it only creates a table for that specific customer.

Any ideas?

Public Sub RunEmailDist()
Dim MyDB As Object
Dim MyRecs As Object
Dim MyName As String
Dim SQL As String
Set MyDB = CurrentDb()
Set MyRecs = MyDB.OpenRecordset("qryEmailDistroList")

SQL = "SELECT tblSAMMSTracking.SAMMS " & _
"INTO temp " & _
"FROM tblSAMMSTracking " & _
"WHERE tblSAMMSTracking.SAMMS = MyRecs!SAMMS"

DoCmd.SetWarnings False

DoCmd.OpenQuery "qryEmailDistroStep1", acViewNormal, acReadOnly
DoCmd.OpenQuery "qryEmailDistroStep2", acViewNormal, acReadOnly

MyRecs.MoveFirst

Do While Not MyRecs.EOF

DoCmd.RunSQL SQL

DoCmd.SendObject acSendTable, "temp", acFormatRTF, MyRecs!CompanyEmail, , ,
"Advanced Shipment Notification", _
"Please see the attached document showing all shipments made yesterday:", 0

MyRecs.MoveNext

Loop

MyRecs.Close

End Sub
Click to expand...
Click to expand...
 
J

Jon Ley

I think you need to set your SQL within the Do loop

Do While Not MyRecs.EOF

SQL = "SELECT tblSAMMSTracking.SAMMS " & _
"INTO temp " & _
"FROM tblSAMMSTracking " & _
"WHERE tblSAMMSTracking.SAMMS = '" & MyRecs!SAMMS & "'"

DoCmd.RunSQL SQL

DoCmd.SendObject acSendTable, "temp", acFormatRTF, MyRecs!CompanyEmail, , ,
"Advanced Shipment Notification", _
"Please see the attached document showing all shipments made yesterday:", 0

MyRecs.MoveNext

Loop

Hope this helps,

Jon.
 
O

Ofer

As i said in the previous post, you have to pass a value to this sub, you
can't just run it, this why it give you that macros box that pops up.
To run this sub pass it a value. Open the Immidiate widows and type

Run RunEmailDist("Put here an SAMMS value")

After you put an SAMMS value press enter to run the sub.
=========================================

Mallasch said:
Below did not work for me. When I click run, a macros box pops up and the
module does not run. Keep in mind that I am basically trying to pass along a
single field down to the SQL statement for the current record that is
selected in the main module. My current code is:
************************************************************
Option Explicit
Sub RunEmailDist(SAMMS As String)
Dim MyDB As Object
Dim MyRecs As Object
Dim SQL As String
Dim SAMMS As String
Set MyDB = CurrentDb()
Set MyRecs = MyDB.OpenRecordset("qryEmailDistroList")

SQL = "SELECT tblSAMMSTracking.SAMMS " & _
"INTO temp " & _
"FROM tblSAMMSTracking " & _
"WHERE tblSAMMSTracking.SAMMS = '" & SAMMS & "'"

DoCmd.SetWarnings False

DoCmd.OpenQuery "qryEmailDistroStep1", acViewNormal, acReadOnly
DoCmd.OpenQuery "qryEmailDistroStep2", acViewNormal, acReadOnly

MyRecs.MoveFirst

Do While Not MyRecs.EOF


DoCmd.RunSQL SQL

DoCmd.SendObject acSendTable, "temp", acFormatRTF, MyRecs!CompanyEmail, , ,
"Advanced Shipment Notification", _
"Please see the attached document showing all shipments made yesterday:", 0

MyRecs.MoveNext

Loop

MyRecs.Close

End Sub

************************************************************
Any suggestions?

Ofer said:
You can pass a variable to the sub
Public Sub RunEmailDist(MySAMMS as Long)

And Change the SQL to
SQL = "SELECT tblSAMMSTracking.SAMMS " & _
"INTO temp " & _
"FROM tblSAMMSTracking " & _
"WHERE tblSAMMSTracking.SAMMS = " & MySAMMS
==========================================
If the SAMMS type is string then
Public Sub RunEmailDist(MySAMMS as string)

And Change the SQL to
SQL = "SELECT tblSAMMSTracking.SAMMS " & _
"INTO temp " & _
"FROM tblSAMMSTracking " & _
"WHERE tblSAMMSTracking.SAMMS = '" & MySAMMS & "'"
==========================================
Mallasch said:
I am trying to create some code that will look at a list of records in a
table, find any records dealing with specific customers, then sending an
email to those customers including an rtf file listing all records.

I believe that I got most of the code that I need but I am running into one
problem. I need for the tables that are created and attached to the emails
only to include the records for that customer. I created an SQL statement to
make a table of records but I am not sure how to pass the field [SAMMS] back
to the SQL statement so it only creates a table for that specific customer.

Any ideas?

Public Sub RunEmailDist()
Dim MyDB As Object
Dim MyRecs As Object
Dim MyName As String
Dim SQL As String
Set MyDB = CurrentDb()
Set MyRecs = MyDB.OpenRecordset("qryEmailDistroList")

SQL = "SELECT tblSAMMSTracking.SAMMS " & _
"INTO temp " & _
"FROM tblSAMMSTracking " & _
"WHERE tblSAMMSTracking.SAMMS = MyRecs!SAMMS"

DoCmd.SetWarnings False

DoCmd.OpenQuery "qryEmailDistroStep1", acViewNormal, acReadOnly
DoCmd.OpenQuery "qryEmailDistroStep2", acViewNormal, acReadOnly

MyRecs.MoveFirst

Do While Not MyRecs.EOF

DoCmd.RunSQL SQL

DoCmd.SendObject acSendTable, "temp", acFormatRTF, MyRecs!CompanyEmail, , ,
"Advanced Shipment Notification", _
"Please see the attached document showing all shipments made yesterday:", 0

MyRecs.MoveNext

Loop

MyRecs.Close

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Passing Criteria to SQL Statement 1
Passing Criteria to webbot Statement 0

Top