RE: Passing Criteria to SQL Statement

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" wrote:

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

Relevant Pages

  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: hyperlinks in a paragraph
    ... Public Sub Discover(ByVal MStr As String) ... Public Sub ProcPara ... Dim HL As Hyperlink ...
    (microsoft.public.word.vba.general)
  • Re: Performance von SQL-Abfragen auf Exceltabellen
    ... Nun habe ich es mit SQL auf Exceldaten versucht und konnte das ganze ... Performance konstant bei rund 3 Sekunden. ... Dim iColumnCount As Long ... Dim FirstColumnforSQLexists_jn As String 'j if first column for SQL ...
    (microsoft.public.de.excel)
  • Re: SqlDataAdapter.Update()
    ... string-chewing algorithm wouldn't work well in SQL. ... looping over words in the string and parsing them off, ... >> Dim cn As New SqlConnection ... >> understood that the data adapter did something like this inside the ...
    (microsoft.public.dotnet.framework.adonet)