Solved

Syntax is wrong - Emailing object via Outlook.

Posted on 2006-11-13
47
310 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
ID: 17933315

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
ID: 17933374
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
ID: 17933380
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
ID: 17933389
ok what about passing the receipents names in the email export of the report.

k
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17933420
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
ID: 17933430
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
ID: 17933454
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
ID: 17933459
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
ID: 17933464
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
ID: 17933466
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
ID: 17933479
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
ID: 17933490


        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
ID: 17933498
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
ID: 17933507
should the nTo and nCC be set as strings or receipients?

k
0
 

Author Comment

by:Karen Schaefer
ID: 17933719
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
ID: 17933738
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
ID: 17933757
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
ID: 17933786
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
ID: 17933824
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
ID: 17933831
why a form when it is on close of the report?

Karen
0
 

Author Comment

by:Karen Schaefer
ID: 17933861
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
ID: 17933873
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
ID: 17933897
I was doing the same thing you were, unfortunately I still having issues with the recordset?

K
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17933899
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
ID: 17933908
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
ID: 17933950
I am getting Too few parameters on the openrecordset?

k
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17934005


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

Expert Comment

by:rockiroads
ID: 17934046
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
ID: 17934119
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
ID: 17934133
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
ID: 17934161
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
ID: 17934221
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
ID: 17934276
rocki, i already suggested that to K,  don't know if she did it or not.
0
 

Author Comment

by:Karen Schaefer
ID: 17934282
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
ID: 17934328
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
ID: 17934389

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
ID: 17934416
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
ID: 17934507
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
ID: 17934517
it is the sendreport portion that can not be execute while the report is still open.

k
0
 

Author Comment

by:Karen Schaefer
ID: 17934586
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
ID: 17934685
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
ID: 17934711
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
ID: 17934724
<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
ID: 17934732
I copied the names from Outlook to box.

k
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17934777
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
ID: 17934795
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
ID: 17936337
Hi Karen, after my last post, I went to bed.
Great stuff your sorted now done, :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

896 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

15 Experts available now in Live!

Get 1:1 Help Now