troubleshooting Question

Access 2007, DoCmd.SendObject Loops

Avatar of Amour22015
Amour22015 asked on
Microsoft AccessVisual Basic ClassicVB Script
10 Comments1 Solution1022 ViewsLast Modified:
Thank you all!

I have a report (rptCSM) that loops though all records in the table and I only want the one that matches(where SCGNumber = '" & TxtSCGID &).  The report is bound to SCGTable.  

Here is my code:

Private Sub BtnSCGID_Click()
Dim rsSCGTable As New ADODB.Recordset
Dim rsCSM As New ADODB.Recordset
Dim sSQLSCGTable As String
Dim sSQLCSM As String
Dim sSQLDate As Date 'Get the current date
Dim strDEV As String
Dim sSQLDEV As String
    On Error GoTo BeforeExit

'Check if in the SCGTable
Set rsSCGTable = New ADODB.Recordset
sSQLSCGTable = "Select * from SCGTable where SCGNumber = '" & TxtSCGID & "'"
rsSCGTable.Open sSQLSCGTable, CurrentProject.Connection
If rsSCGTable.EOF Then
    MsgBox "This Number Has Not Been Approved By The DON RANKIN Program Manager", vbOKOnly, "Required Data"
        Me.TxtSCGID.SetFocus
        GoTo BeforeExit
    Else
       
               
        MsgBox "SCG has been released for Approval. On next message click ALLOW to send email to CSM."
   
        'Get Developers Emails
        SNDDEV = ""
        If Nz(rsSCGTable!PriPocEmail) <> "" Then
        SNDDEV = SNDDEV & rsSCGTable!PriPocEmail & ";"
        End If
        If Nz(rsSCGTable!Alt1POCEmail) <> "" Then
        SNDDEV = SNDDEV & rsSCGTable!Alt1POCEmail & ";"
        End If
        If Nz(rsSCGTable!Alt2POCEmail) <> "" Then
        SNDDEV = SNDDEV & rsSCGTable!Alt2POCEmail & ";"
        End If
        If Nz(rsSCGTable!Alt3POCEmail) <> "" Then
        SNDDEV = SNDDEV & rsSCGTable!Alt3POCEmail & ";"
        End If
           
    ' Email to CSM
        Set rsCSM = New ADODB.Recordset
        sSQLCSM = "Select * From CSMTable"
 
        rsCSM.Open sSQLCSM, CurrentProject.Connection
        If Not rsCSM.EOF Then
        SNDCSM = ""
        If Nz(rsCSM!DONEmail) <> "" Then
        SNDCSM = SNDCSM & rsCSM!DONEmail & ";"
        End If
        If Nz(rsCSM!OCAEmail) <> "" Then
        SNDCSM = SNDCSM & rsCSM!OCAEmail & ";"
        End If
        If Nz(rsCSM!CSMEmail) <> "" Then
        SNDCSM = SNDCSM & rsCSM!CSMEmail & ";"
        End If
        If Nz(rsCSM!RSMEmail) <> "" Then
        SNDCSM = SNDCSM & rsCSM!RSMEmail & ";"
        End If
        End If

DoCmd.SendObject acSendReport, "RptCSM", "Rich Text Format", SNDCSM, SNDDEV, , "Request CSM Approval", "The Attachment contains a request for CSM Approval. Please keep the attachment to this email for your records.  Respectfully, The SCG Developer. NOTE: Do Not Respond to this email, this mailbox does not receive emails", False

' Block the Template
        strDEV = "DEV"
        sSQLDEV = "UPDATE SCGTable SET SCGTable.releaseTemplate = '" & strDEV & "' where SCGNumber = '" & TxtSCGID & "'"
        ' execute SQL
        CurrentDb.Execute (sSQLDEV), dbFailOnError
        'ReleaseTemplate = "DEV"
        DEVDate = sSQLDate
        MsgBox "Email has been sent"
End If

'Open and close the proper Screens
DoCmd.Close acForm, "DEVSCGID", acSaveNo
DoCmd.OpenForm "DEVRelease"

BeforeExit:

End Sub


Please help and thanks!
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 10 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros