Solved

Syntax is wrong - Emailing object via Outlook.

Posted on 2006-11-13
47
309 Views
Last Modified: 2010-05-18
I am trying to set the record source of a report and based on this value I want the option to email the report to the appropriate invidividuals.  what am I doing wrong?

code behind the button that activates the code:

Private Sub Command80_Click()
Dim strsql As String

     strsql = "SELECT DISTINCTROW TA_SR.MeasNo, TA_SR.FTEMNomenclature, TA_SR.NomenclatureModel, TA_SR.Equipment_ID, TA_SR.Multiple_ID, TA_SR.Job_Group," & _
                    " TA_SR.Project, TA_SR.Complete_By_Date, TA_SR.Requestor_ID, TA_SR.Service_Lab, TA_SR.Work_Code, TA_SR.Charge_Number, TA_SR.Disposition, TA_SR.RC1A," & _
                    " TA_SR.RC1B, TA_SR.RC2A, TA_SR.RC2B, TA_SR.Requestor_Comment_3, TA_SR.SRCtr, QS_TT_GeneralInfo.StableEmail, TA_SR.Requestor_Comment_1, TA_SR.Requestor_Comment_2, TA_SR.WSNo, TL_Labs.Contact" & _
                " FROM TL_Labs INNER JOIN (TA_SR INNER JOIN QS_TT_GeneralInfo ON" & _
                    " TA_SR.Requestor_ID = QS_TT_GeneralInfo.RequestorId) ON TL_Labs.LabCtr = TA_SR.Service_Lab" & _
                " WHERE (((TA_SR.Job_Group) = [Forms]![FS_SelectSr]![Job_Group]))" & _
                " ORDER BY TA_SR.Job_Group, TA_SR.SRCtr"
    DoCmd.OpenReport "RS_EmailSR", acViewPreview
        Me.RecordSource = strsql
End Sub


Private Sub Report_Close()
Dim strsql As String
Dim curdb As DAO.Database
Dim rs As Recordset
Dim nTo As String
Dim nCC As String

Select Case MsgBox("Do you wish to email this Service Request?", vbYesNo Or vbQuestion Or vbSystemModal Or vbDefaultButton1, "Sending Email")

    Case vbYes
        Set nTo = "Schaefer, Karen F.;" '"Smith, John, J.'" Green, Dave M; Moe, MaryE; Jones, Steven S;"
        Set rs.OpenRecordset = strsql
            Do Until rs.EOF = True
                nCC = rs.Fields("Service_Lab")
               DoCmd.SendObject acSendReport, "RS_EMailSR", acFormatRTF, _
                    nTo, nCC, , _
                    "Instrumentation Needs", "Please ship the following items to the appropriate labs."
             rs.MoveNext
           Loop
        rs.Close
    Case vbNo
    Exit Sub
        Set nTo = Nothing
        Set nCC = Nothing
        Set rs = Nothing
End Select
End Sub

Thanks,

Karen
0
Comment
Question by:Karen Schaefer
  • 23
  • 15
  • 9
47 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility

try

 " WHERE (((TA_SR.Job_Group) = " & [Forms]![FS_SelectSr]![Job_Group] ))" & _

if Job_Group is text


 " WHERE (((TA_SR.Job_Group) = '" & [Forms]![FS_SelectSr]![Job_Group] &"'"))" & _
0
 

Author Comment

by:Karen Schaefer
Comment Utility
thanks for the suggestion, however, the sql statement works fine - it dies when the nTo is trying to be set.

I tried the nTo as receipent and nCC as receipent and as strings - what should I be using to pass the names to those to email to?  The nto is fixed based on the list I provide where as nCC is based on the Service Lab field in the sql string.

K
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
if u want to set the recordsource of as report

then this line wont do it surely

Me.RecordSource = strsql

this is setting the recordsource of the form that u arein

try this

Reports("RS_EmailSR").Recordsource = strsql

0
 

Author Comment

by:Karen Schaefer
Comment Utility
ok what about passing the receipents names in the email export of the report.

k
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
another thing is to base your report on this sql

SELECT DISTINCTROW TA_SR.MeasNo, TA_SR.FTEMNomenclature, TA_SR.NomenclatureModel, TA_SR.Equipment_ID, TA_SR.Multiple_ID, TA_SR.Job_Group, TA_SR.Project, TA_SR.Complete_By_Date, TA_SR.Requestor_ID, TA_SR.Service_Lab, TA_SR.Work_Code, TA_SR.Charge_Number, TA_SR.Disposition, TA_SR.RC1A, TA_SR.RC1B, TA_SR.RC2A, TA_SR.RC2B, TA_SR.Requestor_Comment_3, TA_SR.SRCtr, QS_TT_GeneralInfo.StableEmail, TA_SR.Requestor_Comment_1, TA_SR.Requestor_Comment_2, TA_SR.WSNo, TL_Labs.Contact
FROM TL_Labs INNER JOIN (TA_SR INNER JOIN QS_TT_GeneralInfo ON TA_SR.Requestor_ID = QS_TT_GeneralInfo.RequestorId) ON TL_Labs.LabCtr = TA_SR.Service_Lab
ORDER BY TA_SR.Job_Group, TA_SR.SRCtr

So its basically your query without the filters

then u can open the report and set the filter
e.g.

    Dim sReport as String

    sReport = "RS_EmailSR"

    DoCmd.OpenReport sReport, acViewDesign
    Reports(sReport).Filter = "TA_SR.Job_Group = " & Forms]![FS_SelectSr]![Job_Group]
    Reports(sReport).FilterOn = True
    DoCmd.Close acReport, sReport, acSaveYes
    DoCmd.OpenReport sReport, acViewPreview

0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
urm., forgot opening [ in Forms

note if this code running on form FS_SelectSr then u can just do

 Reports(sReport).Filter = "TA_SR.Job_Group = " & Me.Job_Group


0
 

Author Comment

by:Karen Schaefer
Comment Utility
I do not having any issues with the recordsource of the report - my currrent issue is setting the email values for the receipients of the email and the proper syntax for  passing these values.

As i stated that the nTo = a fixed set of names, whereas the nCC = the varialbe from the query results hence the rs.fields("Service lab")

What am I doing wrong here my current code to stopping of the object variable for the nTo?

Karen
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
you don't use set to assign a string to variable string


        nTo = "Schaefer, Karen F.;Smith, John, J.; Green, Dave M; Moe, MaryE; Jones, Steven S;"
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
ok, I see

well for a start, u define strsql

and then u do this inside Report_Close

Set rs.OpenRecordset = strsql


but what is strsql set to ?
0
 

Author Comment

by:Karen Schaefer
Comment Utility
I need to be able to send the report as an email and the cc receipents will be varied based on the record select and the value in the service lab field.

K
0
 

Author Comment

by:Karen Schaefer
Comment Utility
the value of the cc recepient = the service lab field and the strsql was previously set to the recordsource of the report where I do not close the strsql.

K
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility


        Set rs.OpenRecordset = strsql
            Do Until rs.EOF = True
                if nCC="" then
                       nCC = rs.Fields("Service_Lab")
                 else
                   nCC = nCC & ";" & rs.Fields("Service_Lab")
                end if
             rs.MoveNext
           Loop
                          DoCmd.SendObject acSendReport, "RS_EMailSR", acFormatRTF, _
                    nTo, nCC, , _
                    "Instrumentation Needs", "Please ship the following items to the appropriate labs."
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
summarizing

       nTo = "Schaefer, Karen F.;Smith, John, J.; Green, Dave M; Moe, MaryE; Jones, Steven S;"

       Set rs.OpenRecordset = strsql
            Do Until rs.EOF = True
                if nCC="" then
                       nCC = rs.Fields("Service_Lab")
                 else
                   nCC = nCC & ";" & rs.Fields("Service_Lab")
                end if
             rs.MoveNext
           Loop
                          DoCmd.SendObject acSendReport, "RS_EMailSR", acFormatRTF, _
                    nTo, nCC, , _
                    "Instrumentation Needs", "Please ship the following items to the appropriate labs."
0
 

Author Comment

by:Karen Schaefer
Comment Utility
should the nTo and nCC be set as strings or receipients?

k
0
 

Author Comment

by:Karen Schaefer
Comment Utility
I am still having issues with the setting of variables on the nTo and nCC.  

and

I am also having issues with the revised open report code.

    Reports("RS_EmailSR").RecordSource = strsql
    DoCmd.OpenReport "RS_EmailSR", acViewPreview

It does not recognize the report name even though i verified teh report name.

K.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
karen, looking at your code, I cant see where u initialise strsql

u define it

dim strsql as string

then next time I see it in use its

 Set rs.OpenRecordset = strsql


so I dont understand when u say u havent closed it
0
 

Author Comment

by:Karen Schaefer
Comment Utility
see my initial question above for my orginal code this is where I set the sql is on opening of the report and I do not close the sql out until I close the report.

Karen
0
 

Author Comment

by:Karen Schaefer
Comment Utility
Maybe i am approaching this wong -

I need to create the recordsource for a report, be able to preview the report and then on close of this report I need to give the use the option to email the report to a certain group of individuals based on the Service lab listed in the report.

the email will always be addressed to a the same people and the cc will always change.

I need code that will allow the cc to be fleixible.

Karen

0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
you need to move these codes to the form module

Dim strsql As String
Dim curdb As DAO.Database
Dim rs As Recordset
Dim nTo As String
Dim nCC As String

Select Case MsgBox("Do you wish to email this Service Request?", vbYesNo Or vbQuestion Or vbSystemModal Or vbDefaultButton1, "Sending Email")

    Case vbYes
        nTo = "Schaefer, Karen F.;Smith, John, J.; Green, Dave M; Moe, MaryE; Jones, Steven S;"

       Set rs.OpenRecordset = strsql
            Do Until rs.EOF = True
                if nCC="" then
                       nCC = rs.Fields("Service_Lab")
                 else
                   nCC = nCC & ";" & rs.Fields("Service_Lab")
                end if
             rs.MoveNext
           Loop
                          DoCmd.SendObject acSendReport, "RS_EMailSR", acFormatRTF, _
                    nTo, nCC, , _
                    "Instrumentation Needs", "Please ship the following items to the appropriate labs."
    Case vbNo
    Exit Sub
        Set nTo = Nothing
        Set nCC = Nothing
        Set rs = Nothing
End Select
0
 

Author Comment

by:Karen Schaefer
Comment Utility
why a form when it is on close of the report?

Karen
0
 

Author Comment

by:Karen Schaefer
Comment Utility
I revert back to a actual query as the recordsoure for the on open of the report.

and

I set the code to on close of the report.

Private Sub Report_Close()
Dim strsql As String
Dim curdb As DAO.Database
Dim rs As Recordset
'Dim nTo As String
Dim nCC As String

Select Case MsgBox("Do you wish to email this Service Request?", vbYesNo Or vbQuestion Or vbSystemModal Or vbDefaultButton1, "Sending Email")

    Case vbYes
       strsql = "SELECT DISTINCTROW TA_SR.MeasNo, TA_SR.FTEMNomenclature, TA_SR.NomenclatureModel, TA_SR.Equipment_ID, TA_SR.Multiple_ID, TA_SR.Job_Group," & _
                    " TA_SR.Project, TA_SR.Complete_By_Date, TA_SR.Requestor_ID, TA_SR.Service_Lab, TA_SR.Work_Code, TA_SR.Charge_Number, TA_SR.Disposition, TA_SR.RC1A," & _
                    " TA_SR.RC1B, TA_SR.RC2A, TA_SR.RC2B, TA_SR.Requestor_Comment_3, TA_SR.SRCtr, QS_TT_GeneralInfo.StableEmail, TA_SR.Requestor_Comment_1, TA_SR.Requestor_Comment_2, TA_SR.WSNo, TL_Labs.Contact" & _
                " FROM TL_Labs INNER JOIN (TA_SR INNER JOIN QS_TT_GeneralInfo ON" & _
                    " TA_SR.Requestor_ID = QS_TT_GeneralInfo.RequestorId) ON TL_Labs.LabCtr = TA_SR.Service_Lab" & _
                " WHERE (((TA_SR.Job_Group) = [Forms]![FS_SelectSr]![Job_Group]))" & _
                " ORDER BY TA_SR.Job_Group, TA_SR.SRCtr"
      ' Set nTo = "EXT-Schaefer, Karen F.;" '"Brenner, Clayton J; Green, Dave M; Moe, Valerie E; Broadhurst, Steven S;"
       Set rs.OpenRecordset = strsql
            Do Until rs.EOF = True
                If nCC = "" Then
                       nCC = rs.Fields("TL_Labs.Contact")
                 Else
                   nCC = nCC & ";" & rs.Fields("TL_Labs.Contact")
                End If
             rs.MoveNext
           Loop
                DoCmd.SendObject acSendReport, "RS_EMailSR", acFormatRTF, _
                    "Brenner, Clayton J; Green, Dave M; Moe, Valerie E; Broadhurst, Steven S;", nCC, , _
                    "Instrumentation Needs", "Please ship the following items to the appropriate labs."
        rs.Close
    Case vbNo
    Exit Sub
        'Set nTo = Nothing
        Set nCC = Nothing
        Set rs = Nothing
End Select
End Sub

Still does not list the rs.openrecordset?

k
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
if you want it on the close event of the report you have to repeat the strSql. or you need to create a new one with the list of  Service Labs

Dim strsql As String
Dim curdb As DAO.Database
Dim rs As Recordset
Dim nTo As String
Dim nCC As String

    strsql = "SELECT DISTINCTROW TA_SR.MeasNo, TA_SR.FTEMNomenclature, TA_SR.NomenclatureModel, TA_SR.Equipment_ID, TA_SR.Multiple_ID, TA_SR.Job_Group," & _
                    " TA_SR.Project, TA_SR.Complete_By_Date, TA_SR.Requestor_ID, TA_SR.Service_Lab, TA_SR.Work_Code, TA_SR.Charge_Number, TA_SR.Disposition, TA_SR.RC1A," & _
                    " TA_SR.RC1B, TA_SR.RC2A, TA_SR.RC2B, TA_SR.Requestor_Comment_3, TA_SR.SRCtr, QS_TT_GeneralInfo.StableEmail, TA_SR.Requestor_Comment_1, TA_SR.Requestor_Comment_2, TA_SR.WSNo, TL_Labs.Contact" & _
                " FROM TL_Labs INNER JOIN (TA_SR INNER JOIN QS_TT_GeneralInfo ON" & _
                    " TA_SR.Requestor_ID = QS_TT_GeneralInfo.RequestorId) ON TL_Labs.LabCtr = TA_SR.Service_Lab" & _
                " WHERE (((TA_SR.Job_Group) = [Forms]![FS_SelectSr]![Job_Group]))" & _
                " ORDER BY TA_SR.Job_Group, TA_SR.SRCtr"



Select Case MsgBox("Do you wish to email this Service Request?", vbYesNo Or vbQuestion Or vbSystemModal Or vbDefaultButton1, "Sending Email")

    Case vbYes
        nTo = "Schaefer, Karen F.;Smith, John, J.; Green, Dave M; Moe, MaryE; Jones, Steven S;"

       Set rs.OpenRecordset = strsql
            Do Until rs.EOF = True
                if nCC="" then
                       nCC = rs.Fields("Service_Lab")
                 else
                   nCC = nCC & ";" & rs.Fields("Service_Lab")
                end if
             rs.MoveNext
           Loop
                          DoCmd.SendObject acSendReport, "RS_EMailSR", acFormatRTF, _
                    nTo, nCC, , _
                    "Instrumentation Needs", "Please ship the following items to the appropriate labs."
    Case vbNo
    Exit Sub
        Set nTo = Nothing
        Set nCC = Nothing
        Set rs = Nothing
End Select
0
 

Author Comment

by:Karen Schaefer
Comment Utility
I was doing the same thing you were, unfortunately I still having issues with the recordset?

K
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
ok try this

       Set rs.OpenRecordset = strsql
            if rs.eof then
                 msgbox "No Records"
                 exit sub
            end if
            Do Until rs.EOF = True
                If nCC = "" Then
                       nCC = rs("Contact")
                 Else
                   nCC = nCC & ";" & rs("Contact")
                End If
             rs.MoveNext
           Loop
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
sorry, use this instead

    Set rs=currentdb.OpenRecordset(strsql)
            if rs.eof then
                 msgbox "No Records"
                 exit sub
            end if
            Do Until rs.EOF = True
                If nCC = "" Then
                       nCC = rs("Contact")
                 Else
                   nCC = nCC & ";" & rs("Contact")
                End If
             rs.MoveNext
           Loop
0
 

Author Comment

by:Karen Schaefer
Comment Utility
I am getting Too few parameters on the openrecordset?

k
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility


try the where clause i posted above.
is the form FS_SelectSr still open?
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Karen, the report's recordsource has been successfully set, correct?

so why not do this

Set rs = CurrentDB.OpenRecordset (Me.RecordSource)

then loop thru. u need to build nCC as cap as said

Also I defined Recordset as DAO

here is your modified code



Private Sub Report_Close()
   
    Dim rs As DAO.Recordset
    Dim nTo As String
    Dim nCC As String
    Dim sSubject As String
    Dim sMsg As String
   
    Select Case MsgBox("Do you wish to email this Service Request?", vbYesNo Or vbQuestion Or vbSystemModal Or vbDefaultButton1, "Sending Email")
   
        Case vbYes
       
            'Set variables here
            nTo = "EXT-Schaefer, Karen F.;Brenner, Clayton J; Green, Dave M; Moe, Valerie E; Broadhurst, Steven S;"
            sSubject = "Instrumentation Needs"
            sMsg = "Please ship the following items to the appropriate labs."
           
            'Loop thru and build CC list based on report's current recordsource
            Set rs = CurrentDb.OpenRecordset(Me.RecordSource)
            nCC = ""
            Do While rs.EOF = False
                nCC = nCC & ";" & rs!Contact   'or is it rs!Service_Lab
                rs.MoveNext
            Loop
            If Len(nCC) > 0 Then nCC = Mid$(nCC, 2)
            DoCmd.SendObject acSendReport, "RS_EMailSR", acFormatRTF, nTo, nCC, , sSubject, sMsg
            rs.Close
            Set rs = Nothing
       
        Case Else
    End Select
End Sub
   


What I recommend though is once u get this working, is to store email address (for nTo) in a table and never do any kind of hardcoding
0
 

Author Comment

by:Karen Schaefer
Comment Utility
I tried your example, unforunately I am still getting the Too Few parameters on the openrecordset.

I even tried moving the code on open of the report instead of onclose.

what am I missing.  do I need to seed a global variable or public varialbe that will hold the service lab info.

K
0
 

Author Comment

by:Karen Schaefer
Comment Utility
Private Sub Report_close(Cancel As Integer)
    Dim rs As DAO.Recordset
    Dim nTo As String
    Dim nCC As String
    Dim sSubject As String
    Dim sMsg As String
   
    Select Case MsgBox("Do you wish to email this Service Request?", vbYesNo Or vbQuestion Or vbSystemModal Or vbDefaultButton1, "Sending Email")
   
        Case vbYes
       
            'Set variables here
            nTo = "EXT-Schaefer, Karen F.;" 'Brenner, Clayton J; Green, Dave M; Moe, Valerie E; Broadhurst, Steven S;"
            sSubject = "Instrumentation Needs"
            sMsg = "Please ship the following items to the appropriate labs."
           
            'Loop thru and build CC list based on report's current recordsource
            Set rs = CurrentDb.OpenRecordset(Me.RecordSource)'---------------------------It dies here!!!!!
            nCC = ""
            Do While rs.EOF = False
                nCC = nCC & ";" & rs!Service_Lab
                rs.MoveNext
            Loop
            If Len(nCC) > 0 Then nCC = Mid$(nCC, 2)
            DoCmd.SendObject acSendReport, "RS_EMailSR", acFormatRTF, nTo, nCC, , sSubject, sMsg
            rs.Close
            Set rs = Nothing
       
        Case Else
    End Select

End Sub
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
K,  try this first

Private Sub Command80_Click()
Dim strsql As String, rs as DAO.recordset

     strsql = "SELECT DISTINCTROW TA_SR.MeasNo, TA_SR.FTEMNomenclature, TA_SR.NomenclatureModel, TA_SR.Equipment_ID, TA_SR.Multiple_ID, TA_SR.Job_Group," & _
                    " TA_SR.Project, TA_SR.Complete_By_Date, TA_SR.Requestor_ID, TA_SR.Service_Lab, TA_SR.Work_Code, TA_SR.Charge_Number, TA_SR.Disposition, TA_SR.RC1A," & _
                    " TA_SR.RC1B, TA_SR.RC2A, TA_SR.RC2B, TA_SR.Requestor_Comment_3, TA_SR.SRCtr, QS_TT_GeneralInfo.StableEmail, TA_SR.Requestor_Comment_1, TA_SR.Requestor_Comment_2, TA_SR.WSNo, TL_Labs.Contact" & _
                " FROM TL_Labs INNER JOIN (TA_SR INNER JOIN QS_TT_GeneralInfo ON" & _
                    " TA_SR.Requestor_ID = QS_TT_GeneralInfo.RequestorId) ON TL_Labs.LabCtr = TA_SR.Service_Lab" & _
                " WHERE (((TA_SR.Job_Group) = [Forms]![FS_SelectSr]![Job_Group]))" & _
                " ORDER BY TA_SR.Job_Group, TA_SR.SRCtr"
'    DoCmd.OpenReport "RS_EmailSR", acViewPreview
'        Me.RecordSource = strsql

set rs=currentdb.openrecordset(strsql)

if rs.eof then
   msgbox "No records'
   exit sub
end if



End Sub


see if you can open the recordset without error




0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
ok, then the SQL u have defined in elsewhere, why cant u just use that and use it there


strsql = "SELECT DISTINCTROW TA_SR.MeasNo, TA_SR.FTEMNomenclature, TA_SR.NomenclatureModel, TA_SR.Equipment_ID, TA_SR.Multiple_ID, TA_SR.Job_Group," & _
                    " TA_SR.Project, TA_SR.Complete_By_Date, TA_SR.Requestor_ID, TA_SR.Service_Lab, TA_SR.Work_Code, TA_SR.Charge_Number, TA_SR.Disposition, TA_SR.RC1A," & _
                    " TA_SR.RC1B, TA_SR.RC2A, TA_SR.RC2B, TA_SR.Requestor_Comment_3, TA_SR.SRCtr, QS_TT_GeneralInfo.StableEmail, TA_SR.Requestor_Comment_1, TA_SR.Requestor_Comment_2, TA_SR.WSNo, TL_Labs.Contact" & _
                " FROM TL_Labs INNER JOIN (TA_SR INNER JOIN QS_TT_GeneralInfo ON" & _
                    " TA_SR.Requestor_ID = QS_TT_GeneralInfo.RequestorId) ON TL_Labs.LabCtr = TA_SR.Service_Lab" & _
                " WHERE TA_SR.Job_Group = " & [Forms]![FS_SelectSr]![Job_Group] " & _
                " ORDER BY TA_SR.Job_Group, TA_SR.SRCtr"

Set rs = CurrentDb.OpenRecordset(strsql)



0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
rocki, i already suggested that to K,  don't know if she did it or not.
0
 

Author Comment

by:Karen Schaefer
Comment Utility
I did change the recordsource for the report back to the original qry and add the SQL string to the code on the onclose of the report - unforunately I am still getting a too few parameter issue I am still trying to work out.

K
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 250 total points
Comment Utility
Cap, just a suttle difference

I placed [Forms]![FS_SelectSr]![Job_Group]  outside quotes


correction to be made, got rid of rogue " on 2nd last line. Here it is corrected



so its now


    Dim rs As DAO.Recordset
    Dim nTo As String
    Dim nCC As String
    Dim sSubject As String
    Dim sMsg As String
    Dim strSql as String
   
    Select Case MsgBox("Do you wish to email this Service Request?", vbYesNo Or vbQuestion Or vbSystemModal Or vbDefaultButton1, "Sending Email")
   
        Case vbYes
       
            'Set variables here
            nTo = "EXT-Schaefer, Karen F.;" 'Brenner, Clayton J; Green, Dave M; Moe, Valerie E; Broadhurst, Steven S;"
            sSubject = "Instrumentation Needs"
            sMsg = "Please ship the following items to the appropriate labs."
           
            strSql = "SELECT DISTINCTROW TA_SR.MeasNo, TA_SR.FTEMNomenclature, TA_SR.NomenclatureModel, TA_SR.Equipment_ID, TA_SR.Multiple_ID, TA_SR.Job_Group," & _
                    " TA_SR.Project, TA_SR.Complete_By_Date, TA_SR.Requestor_ID, TA_SR.Service_Lab, TA_SR.Work_Code, TA_SR.Charge_Number, TA_SR.Disposition, TA_SR.RC1A," & _
                    " TA_SR.RC1B, TA_SR.RC2A, TA_SR.RC2B, TA_SR.Requestor_Comment_3, TA_SR.SRCtr, QS_TT_GeneralInfo.StableEmail, TA_SR.Requestor_Comment_1, TA_SR.Requestor_Comment_2, TA_SR.WSNo, TL_Labs.Contact" & _
                " FROM TL_Labs INNER JOIN (TA_SR INNER JOIN QS_TT_GeneralInfo ON" & _
                    " TA_SR.Requestor_ID = QS_TT_GeneralInfo.RequestorId) ON TL_Labs.LabCtr = TA_SR.Service_Lab" & _
                " WHERE TA_SR.Job_Group = " & [Forms]![FS_SelectSr]![Job_Group] & _
                " ORDER BY TA_SR.Job_Group, TA_SR.SRCtr"

            'Loop thru and build CC list based on report's current recordsource
            Set rs = CurrentDb.OpenRecordset(strSql)
            nCC = ""
            Do While rs.EOF = False
                nCC = nCC & ";" & rs!Service_Lab
                rs.MoveNext
            Loop
            If Len(nCC) > 0 Then nCC = Mid$(nCC, 2)
            DoCmd.SendObject acSendReport, "RS_EMailSR", acFormatRTF, nTo, nCC, , sSubject, sMsg
            rs.Close
            Set rs = Nothing
       
        Case Else
    End Select


0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility

K,
did you try this


Private Sub Command80_Click()
Dim strsql As String, rs as DAO.recordset

     strsql = "SELECT DISTINCTROW TA_SR.MeasNo, TA_SR.FTEMNomenclature, TA_SR.NomenclatureModel, TA_SR.Equipment_ID, TA_SR.Multiple_ID, TA_SR.Job_Group," & _
                    " TA_SR.Project, TA_SR.Complete_By_Date, TA_SR.Requestor_ID, TA_SR.Service_Lab, TA_SR.Work_Code, TA_SR.Charge_Number, TA_SR.Disposition, TA_SR.RC1A," & _
                    " TA_SR.RC1B, TA_SR.RC2A, TA_SR.RC2B, TA_SR.Requestor_Comment_3, TA_SR.SRCtr, QS_TT_GeneralInfo.StableEmail, TA_SR.Requestor_Comment_1, TA_SR.Requestor_Comment_2, TA_SR.WSNo, TL_Labs.Contact" & _
                " FROM TL_Labs INNER JOIN (TA_SR INNER JOIN QS_TT_GeneralInfo ON" & _
                    " TA_SR.Requestor_ID = QS_TT_GeneralInfo.RequestorId) ON TL_Labs.LabCtr = TA_SR.Service_Lab" & _
                " WHERE (((TA_SR.Job_Group) = [Forms]![FS_SelectSr]![Job_Group]))" & _
                " ORDER BY TA_SR.Job_Group, TA_SR.SRCtr"
'    DoCmd.OpenReport "RS_EmailSR", acViewPreview
'        Me.RecordSource = strsql

set rs=currentdb.openrecordset(strsql)

if rs.eof then
   msgbox "No records'
   exit sub
end if



End Sub


see if you can open the recordset without error



0
 

Author Comment

by:Karen Schaefer
Comment Utility
Rock,

I tried your revision - it seems to get me almost there - need to modiy the where statement to force it to read as a number.

               " WHERE (((TA_SR.Job_Group) = [Forms]![FS_SelectSr]![Job_Group]))"  Help with the proper syntax - to include the single quote around the variable, please.

k
0
 

Author Comment

by:Karen Schaefer
Comment Utility
nevermind I got the correct syntax.

thanks it is almost there - unforunately I can not launch this portion of the code from Onclose of the report - so I will have to rethink how to handle this issue.

I would like to be able to preview the report, then close the report then fire off this code to email the report.  what would you recommend - two seperate buttons or is there a way to use the same button and code it that on close of the report it executes the second half of the code?

K
0
 

Author Comment

by:Karen Schaefer
Comment Utility
it is the sendreport portion that can not be execute while the report is still open.

k
0
 

Author Comment

by:Karen Schaefer
Comment Utility
How do I modify the send report to automatically send the email without actual open Outlook and the usiing selecting send within outlook?

K
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points
Comment Utility
didn't i tell you to move the codes  to the form module.
create another button for emailing the report
0
 

Author Comment

by:Karen Schaefer
Comment Utility
I did move it to a separate button on the main form - I was trying to simplify the steps the user takes to sending the email -

I am still having a minor issue with the passing of the TO names -

It wants to put the ";" in the wrong places - what do you recommend?

Karen
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
<It wants to put the ";" in the wrong places - what do you recommend? >

where is the data coming from?
0
 

Author Comment

by:Karen Schaefer
Comment Utility
I copied the names from Outlook to box.

k
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
will the TO names be changing from time to time?
if it is, create a table of names, then open it as recordset like what we did for nCC
0
 

Author Comment

by:Karen Schaefer
Comment Utility
thanks I was thinking the same thing thanks to both of you for your assistance.

As usual you come to my rescue.

Thanks,

Karen
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Hi Karen, after my last post, I went to bed.
Great stuff your sorted now done, :)
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now