Syntax is wrong - Emailing object via Outlook.

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
Karen SchaeferBI ANALYSTAsked:
Who is Participating?
 
rockiroadsConnect With a Mentor Commented:
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
 
Rey Obrero (Capricorn1)Commented:

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
 
Karen SchaeferBI ANALYSTAuthor Commented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
rockiroadsCommented:
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
 
Karen SchaeferBI ANALYSTAuthor Commented:
ok what about passing the receipents names in the email export of the report.

k
0
 
rockiroadsCommented:
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
 
rockiroadsCommented:
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
 
Karen SchaeferBI ANALYSTAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
rockiroadsCommented:
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
 
Karen SchaeferBI ANALYSTAuthor Commented:
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
 
Karen SchaeferBI ANALYSTAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:


        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
 
Rey Obrero (Capricorn1)Commented:
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
 
Karen SchaeferBI ANALYSTAuthor Commented:
should the nTo and nCC be set as strings or receipients?

k
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
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
 
rockiroadsCommented:
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
 
Karen SchaeferBI ANALYSTAuthor Commented:
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
 
Karen SchaeferBI ANALYSTAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Karen SchaeferBI ANALYSTAuthor Commented:
why a form when it is on close of the report?

Karen
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Karen SchaeferBI ANALYSTAuthor Commented:
I was doing the same thing you were, unfortunately I still having issues with the recordset?

K
0
 
Rey Obrero (Capricorn1)Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Karen SchaeferBI ANALYSTAuthor Commented:
I am getting Too few parameters on the openrecordset?

k
0
 
Rey Obrero (Capricorn1)Commented:


try the where clause i posted above.
is the form FS_SelectSr still open?
0
 
rockiroadsCommented:
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
 
Karen SchaeferBI ANALYSTAuthor Commented:
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
 
Karen SchaeferBI ANALYSTAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
rockiroadsCommented:
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
 
Rey Obrero (Capricorn1)Commented:
rocki, i already suggested that to K,  don't know if she did it or not.
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:

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
 
Karen SchaeferBI ANALYSTAuthor Commented:
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
 
Karen SchaeferBI ANALYSTAuthor Commented:
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
 
Karen SchaeferBI ANALYSTAuthor Commented:
it is the sendreport portion that can not be execute while the report is still open.

k
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
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
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
didn't i tell you to move the codes  to the form module.
create another button for emailing the report
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
<It wants to put the ";" in the wrong places - what do you recommend? >

where is the data coming from?
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
I copied the names from Outlook to box.

k
0
 
Rey Obrero (Capricorn1)Commented:
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
 
Karen SchaeferBI ANALYSTAuthor Commented:
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
 
rockiroadsCommented:
Hi Karen, after my last post, I went to bed.
Great stuff your sorted now done, :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.