Passing Criteria to SQL Statement

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...
 
O

Ofer

Try this:

Sub RunEmailDist(SAMMS As String)
Dim MyDB As Object
Dim MyRecs As Recordset
Dim SQL As String

Set MyDB = CurrentDb()

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


Set MyRecs = MyDB.OpenRecordset("SELECT tblSAMMSTracking.SAMMS " & _
"INTO temp " & _
"FROM tblSAMMSTracking " & _
"WHERE tblSAMMSTracking.SAMMS = '" & SAMMS & "'")

If Not MyRecs.EOF Then
MyRecs.MoveFirst
While Not MyRecs.EOF
DoCmd.SendObject acSendTable, "temp", acFormatRTF,
MyRecs!CompanyEmail, , , "Advanced Shipment Notification", _
"Please see the attached document showing all shipments made
yesterday:", 0
MyRecs.MoveNext
Wend
End If
MyRecs.Close

DoCmd.SetWarnings True
End Sub
===============================
When you call this sub, you need to pass a SAMMS, other wise youll get a
message to enter a macro

Call RunEmailDist("enter here an SAMMS Value to filter by")

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 4
Create TBL SQL error 1
Using variables as table names in an append sql statemnt 1
how to close a form when using Access. 3
Help to update a value in an SQL 2000 table from Access code 0
pop behind 1
Export to Excel when password protected 3
SQL to VB 4

Top