RE: Passing Criteria to SQL Statement

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

> 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

  • Project Error
    ... Private Declare Sub Sleep Lib "Kernel32" ... Dim strDataSrc As String ...
    (microsoft.public.vb.bugs)
  • Re: Is there a way to prevent a RichTextBox from scrolling?
    ... Private _isRegex As Boolean ... Public Sub New(ByVal thispattern As String, ... Dim entry As tDict ...
    (microsoft.public.dotnet.framework.windowsforms.controls)
  • Excel Listing tool using VB
    ... Sub ListFiles2() ... Dim directories() As String, CurrentDirectory As String ... Dim dirtopaste, dirok ...
    (microsoft.public.vb.general.discussion)
  • Form Error
    ... SMSDS_CallerID As String ... Private Declare Sub Sleep Lib "kernel32" ... Dim ComString As String ... Dim AppPath As String, FreeFileNo% ...
    (microsoft.public.vb.bugs)
  • 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)