Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Add multiple iterations into subject line

    ALL,
    I have the following code for an email where I have selected single or multiple iterations of data

    This is the form where I select the data:

    Code:
    Private Sub SelectChanges_Click()
        Dim strWhere As String, ctl As Control, varItem As Variant
        Set ctl = Me.MyChanges
       
    If Me.MyChanges.ItemsSelected.Count = 0 Then
         MsgBox "Nothing was selected"
            Exit Sub
        End If
    
    For Each varItem In ctl.ItemsSelected
       strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
    
    Next varItem
       strWhere = Left(strWhere, Len(strWhere) - 1)
       DoCmd.OpenReport "rptSelectChanges", acViewReport, , "CRNumber IN(" & strWhere & ")"
       DoCmd.Close acForm, "frmSelectChanges"
    End Sub

    This is the email code (Don't worry about the Dim):

    Code:
    Public Sub SendSelectCRs_Click()
     
    
    Set objOutlook = CreateObject("Outlook.Application")
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    
    With objOutlookMsg
       .Subject = NIE & " Selected Changes - " & Tod
       .Body = SigBlock
        DoCmd.OutputTo 3, "rptSelectChanges", acFormatPDF, "C:\Temp\" & NIE & " Selected Changes - " & Tod & ".pdf", , 0
       .Attachments.Add ("C:\Temp\" & NIE & " Selected Changes - " & Tod & ".pdf")
       .Display
        Kill "C:\Temp\" & NIE & " Selected Changes - " & Tod & ".pdf"
    
      End With
      
          DoCmd.Close acReport, "rptSelectChanges"
          DoCmd.OpenForm "frmStart"
      
     Set objOutlookMsg = Nothing
     Set objOutlook = Nothing
     Set objOutlookAttach = Nothing
      Exit Sub
    End If
    End Sub
    I would like to put in the the CRNumber reflected in the selection and what is output with the PDF.

    Single is easy
    .Subject = NIE & " Selected Changes - " & CRNumber & " " & Tod

    How do I work it with multiple CR Numbers?

    Thanks


  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    We dont know what a CR Number is. If its just data, you should be able to put as much or as little as you want. In a similar post by you I explained how to add Paramters to your code so that It could be used as a function. That same post could be applied here and CRNumber used.

    I think theres a Concat Fields into one token post on the forums, that could help you. Search!

  3. #3
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    ALL,
    Lets try this again. It appears that I didn't clarify what I needed well enough.

    I have this code:

    .Subject = NIE & " Selected Changes - " & Tod


    I want to change " Selected Changes - " into a more specific identification of what the selected changes are.


    I get the selected changes from:

    Code:
    SELECT DISTINCT Format(([CRNo]+([SubNo]*0.01)),"Fixed") AS CRNumber, tblChangeRequest.CRID, qrySwitching.Units, qrySwitching.HBVers, qrySwitching.MTOEParas, qrySwitching.People, qrySwitching.DaysOpen, qrySwitching.DateIDs, tblChangeRequest.ChangeRequested, tblChangeRequest.Rationale, tblChangeRequest.NIE, tblChangeRequest.ChangeType, qrySwitching.Levelz, qrySwitching.Status, tblChangeRequest.NOTES, tblChangeRequest.ActionItems, tblChangeRequest.AOVote, tblChangeRequest.SubNo
    FROM tblChangeRequest INNER JOIN qrySwitching ON tblChangeRequest.CRID = qrySwitching.CRID
    WHERE (((tblChangeRequest.CRNo)<>0))
    GROUP BY Format(([CRNo]+([SubNo]*0.01)),"Fixed"), tblChangeRequest.CRID, qrySwitching.Units, qrySwitching.HBVers, qrySwitching.MTOEParas, qrySwitching.People, qrySwitching.DaysOpen, qrySwitching.DateIDs, tblChangeRequest.ChangeRequested, tblChangeRequest.Rationale, tblChangeRequest.NIE, tblChangeRequest.ChangeType, qrySwitching.Levelz, qrySwitching.Status, tblChangeRequest.NOTES, tblChangeRequest.ActionItems, tblChangeRequest.AOVote, tblChangeRequest.SubNo
    ORDER BY tblChangeRequest.CRID;
    Here I would like to use the output from CRNumber in the above code.

    Example: " Change Request(s) - " 1.00 1.01 2.20 3.66 - replaces " Selected Changes - "

    Is there a war to place a comma after each but not if there is only one CRNumber?

    Thus the subject line in the email is:

    AWA 17 Change Request(s) - 1.00, 1.01, 2.20, 3.66 - 6/10/2016

    Would I need to make this a string and use an array from the query CRNumber output?

    Perceptus,
    Your name belays the concept. I did search, otherwise I wouldn't ask. I am moderately new and self taught in the use of Access and/or VBA.

    Concatenate the same fields output?

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    How about this?
    This is air code!!! Plus, you didn't provide the complete subroutine code for "SendSelectCRs_Click", so I added what/where I could.

    (The BLUE lines I added/changed)
    Code:
    Public Sub SendSelectCRs_Click()
    
        Dim r As DAO.Recordset
        Dim sSQL As String
        Dim MsgChanges As String
    
    
        Set objOutlook = CreateObject("Outlook.Application")
        Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    
        sSQL = "SELECT DISTINCT Format(([CRNo]+([SubNo]*0.01)),'Fixed') AS CRNumber, tblChangeRequest.CRID,"
        sSQL = sSQL & " qrySwitching.Units, qrySwitching.HBVers, qrySwitching.MTOEParas, qrySwitching.People,"
        sSQL = sSQL & " qrySwitching.DaysOpen, qrySwitching.DateIDs, tblChangeRequest.ChangeRequested,"
        sSQL = sSQL & " tblChangeRequest.Rationale, tblChangeRequest.NIE, tblChangeRequest.ChangeType,"
        sSQL = sSQL & " qrySwitching.Levelz, qrySwitching.Status, tblChangeRequest.NOTES, tblChangeRequest.ActionItems, tblChangeRequest.AOVote, tblChangeRequest.SubNo"
        sSQL = sSQL & " FROM tblChangeRequest INNER JOIN qrySwitching ON tblChangeRequest.CRID = qrySwitching.CRID"
        sSQL = sSQL & " WHERE (((tblChangeRequest.CRNo) <> 0))"
        sSQL = sSQL & " GROUP BY Format(([CRNo]+([SubNo]*0.01)),'Fixed'), tblChangeRequest.CRID, qrySwitching.Units,"
        sSQL = sSQL & " qrySwitching.HBVers, qrySwitching.MTOEParas, qrySwitching.People, qrySwitching.DaysOpen,"
        sSQL = sSQL & " qrySwitching.DateIDs, tblChangeRequest.ChangeRequested, tblChangeRequest.Rationale,"
        sSQL = sSQL & " tblChangeRequest.NIE, tblChangeRequest.ChangeType, qrySwitching.Levelz, qrySwitching.Status,"
        sSQL = sSQL & " tblChangeRequest.NOTES, tblChangeRequest.ActionItems, tblChangeRequest.AOVote, tblChangeRequest.SubNo"
        sSQL = sSQL & " ORDER BY tblChangeRequest.CRID;"
    
        Set r = CurrentDb.OpenRecordset(sSQL)
    
        If Not (r.BOF And r.EOF) Then
            r.MoveLast
            r.MoveFirst
    
            MsgChanges = " Change Request(s) - "
            ' loop through record set to get CR number
            Do While Not r.EOF
                MsgChanges = MsgChanges & r!CRNumber & ", "
            Next
    
            If Len(MsgChanges) > 0 Then
                ' remove trailing comma
                MsgChanges = Left(MsgChanges, Len(MsgChanges) - 2)
            End If
    
    
            With objOutlookMsg
                .Subject = NIE & MsgChanges & " - " & Tod
                .Body = SigBlock
                DoCmd.OutputTo 3, "rptSelectChanges", acFormatPDF, "C:\Temp\" & NIE & " Selected Changes - " & Tod & ".pdf", , 0
                .Attachments.Add ("C:\Temp\" & NIE & " Selected Changes - " & Tod & ".pdf")
                .Display
                Kill "C:\Temp\" & NIE & " Selected Changes - " & Tod & ".pdf"
    
            End With
    
            DoCmd.Close acReport, "rptSelectChanges"
            DoCmd.OpenForm "frmStart"
    
        End If
    
        r.Close
        Set r = Nothing
    
        Set objOutlookMsg = Nothing
        Set objOutlook = Nothing
        Set objOutlookAttach = Nothing
        Exit Sub
    End Sub
    So, basicly, open a recordset, loop through the record set creating a string, remove the last two characters (comma and space - ", ").
    Use the string in the ".Subject".

  5. #5
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Quote Originally Posted by ssanfu View Post
    How about this?
    This is air code!!! Plus, you didn't provide the complete subroutine code for "SendSelectCRs_Click", so I added what/where I could.

    (The BLUE lines I added/changed)
    Code:
    Public Sub SendSelectCRs_Click()
    
        Dim r As DAO.Recordset
        Dim sSQL As String
        Dim MsgChanges As String
    
    
        Set objOutlook = CreateObject("Outlook.Application")
        Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    
        sSQL = "SELECT DISTINCT Format(([CRNo]+([SubNo]*0.01)),'Fixed') AS CRNumber, tblChangeRequest.CRID,"
        sSQL = sSQL & " qrySwitching.Units, qrySwitching.HBVers, qrySwitching.MTOEParas, qrySwitching.People,"
        sSQL = sSQL & " qrySwitching.DaysOpen, qrySwitching.DateIDs, tblChangeRequest.ChangeRequested,"
        sSQL = sSQL & " tblChangeRequest.Rationale, tblChangeRequest.NIE, tblChangeRequest.ChangeType,"
        sSQL = sSQL & " qrySwitching.Levelz, qrySwitching.Status, tblChangeRequest.NOTES, tblChangeRequest.ActionItems, tblChangeRequest.AOVote, tblChangeRequest.SubNo"
        sSQL = sSQL & " FROM tblChangeRequest INNER JOIN qrySwitching ON tblChangeRequest.CRID = qrySwitching.CRID"
        sSQL = sSQL & " WHERE (((tblChangeRequest.CRNo) <> 0))"
        sSQL = sSQL & " GROUP BY Format(([CRNo]+([SubNo]*0.01)),'Fixed'), tblChangeRequest.CRID, qrySwitching.Units,"
        sSQL = sSQL & " qrySwitching.HBVers, qrySwitching.MTOEParas, qrySwitching.People, qrySwitching.DaysOpen,"
        sSQL = sSQL & " qrySwitching.DateIDs, tblChangeRequest.ChangeRequested, tblChangeRequest.Rationale,"
        sSQL = sSQL & " tblChangeRequest.NIE, tblChangeRequest.ChangeType, qrySwitching.Levelz, qrySwitching.Status,"
        sSQL = sSQL & " tblChangeRequest.NOTES, tblChangeRequest.ActionItems, tblChangeRequest.AOVote, tblChangeRequest.SubNo"
        sSQL = sSQL & " ORDER BY tblChangeRequest.CRID;"
    
        Set r = CurrentDb.OpenRecordset(sSQL)
    
        If Not (r.BOF And r.EOF) Then
            r.MoveLast
            r.MoveFirst
    
            MsgChanges = " Change Request(s) - "
            ' loop through record set to get CR number
            Do While Not r.EOF
                MsgChanges = MsgChanges & r!CRNumber & ", "
            Next
    
            If Len(MsgChanges) > 0 Then
                ' remove trailing comma
                MsgChanges = Left(MsgChanges, Len(MsgChanges) - 2)
            End If
    
    
            With objOutlookMsg
                .Subject = NIE & MsgChanges & " - " & Tod
                .Body = SigBlock
                DoCmd.OutputTo 3, "rptSelectChanges", acFormatPDF, "C:\Temp\" & NIE & " Selected Changes - " & Tod & ".pdf", , 0
                .Attachments.Add ("C:\Temp\" & NIE & " Selected Changes - " & Tod & ".pdf")
                .Display
                Kill "C:\Temp\" & NIE & " Selected Changes - " & Tod & ".pdf"
    
            End With
    
            DoCmd.Close acReport, "rptSelectChanges"
            DoCmd.OpenForm "frmStart"
    
        End If
    
        r.Close
        Set r = Nothing
    
        Set objOutlookMsg = Nothing
        Set objOutlook = Nothing
        Set objOutlookAttach = Nothing
        Exit Sub
    End Sub
    So, basicly, open a recordset, loop through the record set creating a string, remove the last two characters (comma and space - ", ").
    Use the string in the ".Subject".

    Thanks Steve
    I think I see what you are doing with this. I actually comprehend the If Then. I got an error on the Do While - Next. I changed the Next to Loop, but I am endlessly looping (I think) With the sSQL I get all the information. I did trim it down to just the CRNumbers/CRID.

    Code:
        sSQL = "SELECT DISTINCT Format(([CRNo]+([SubNo]*0.01)),'Fixed') AS CRNumber, tblChangeRequest.CRID"
        sSQL = sSQL & " FROM tblChangeRequest"
        sSQL = sSQL & " WHERE (((tblChangeRequest.CRNo) <> 0))"
        sSQL = sSQL & " GROUP BY Format(([CRNo]+([SubNo]*0.01)),'Fixed'), tblChangeRequest.CRID"
        sSQL = sSQL & " HAVING (((tblChangeRequest.CRID) > 25))"
        sSQL = sSQL & " ORDER BY tblChangeRequest.CRID;"

    On the form with the select button I have the following code:

    Code:
    Private Sub SelectChanges_Click()
        Dim strWhere As String, ctl As Control, varItem As Variant
        Set ctl = Me.MyChanges
       
    If Me.MyChanges.ItemsSelected.Count = 0 Then
         MsgBox "Nothing was selected"
            Exit Sub
        End If
    
    For Each varItem In ctl.ItemsSelected
       strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
    
    Next varItem
       strWhere = Left(strWhere, Len(strWhere) - 1)
       DoCmd.OpenReport "rptSelectChanges", acViewReport, , "CRNumber IN(" & strWhere & ")"
       DoCmd.Close acForm, "frmSelectChanges"
    End Sub
    I do have all the email coding so everything works fine. I was only trying to do the addition to the subject line.

    Would I have to I use strWhere, delete all the sSQL?

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I got an error on the Do While - Next. I changed the Next to Loop, but I am endlessly looping (I think) With the sSQL I get all the information.
    My bad! I did say it was air code.
    It should be
    Code:
            Do While Not r.EOF
                MsgChanges = MsgChanges & r!CRNumber & ", "
                r.MoveNext    '<<-
            Loop

    I was only trying to do the addition to the subject line.
    All of this
    Code:
        'get CR numbers
        sSQL = "SELECT DISTINCT Format(([CRNo]+([SubNo]*0.01)),'Fixed') AS CRNumber, tblChangeRequest.CRID"
        sSQL = sSQL & " FROM tblChangeRequest"
        sSQL = sSQL & " WHERE (((tblChangeRequest.CRNo) <> 0))"
        sSQL = sSQL & " GROUP BY Format(([CRNo]+([SubNo]*0.01)),'Fixed'), tblChangeRequest.CRID"
        sSQL = sSQL & " HAVING (((tblChangeRequest.CRID) > 25))"
        sSQL = sSQL & " ORDER BY tblChangeRequest.CRID;"
    
        Set r = CurrentDb.OpenRecordset(sSQL)
    
        If Not (r.BOF And r.EOF) Then
            r.MoveLast
            r.MoveFirst
    
            MsgChanges = " Change Request(s) - "
            ' loop through record set to get CR number
            Do While Not r.EOF
                MsgChanges = MsgChanges & r!CRNumber & ", "
                r.MoveNext
            Loop
    
            If Len(MsgChanges) > 0 Then
                ' remove trailing comma
                MsgChanges = Left(MsgChanges, Len(MsgChanges) - 2)
            End If
    (plus the Dim statements and the clean up) was to create the subject line in the "Sub SendSelectCRs_Click" routine.



    Would I have to I use strWhere, delete all the sSQL?
    I don't understand the question.

  7. #7
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Quote Originally Posted by ssanfu View Post
    My bad! I did say it was air code.
    It should be
    Code:
            Do While Not r.EOF
                MsgChanges = MsgChanges & r!CRNumber & ", "
                r.MoveNext    '<<-
            Loop


    All of this
    Code:
        'get CR numbers
        sSQL = "SELECT DISTINCT Format(([CRNo]+([SubNo]*0.01)),'Fixed') AS CRNumber, tblChangeRequest.CRID"
        sSQL = sSQL & " FROM tblChangeRequest"
        sSQL = sSQL & " WHERE (((tblChangeRequest.CRNo) <> 0))"
        sSQL = sSQL & " GROUP BY Format(([CRNo]+([SubNo]*0.01)),'Fixed'), tblChangeRequest.CRID"
        sSQL = sSQL & " HAVING (((tblChangeRequest.CRID) > 25))"
        sSQL = sSQL & " ORDER BY tblChangeRequest.CRID;"
    
        Set r = CurrentDb.OpenRecordset(sSQL)
    
        If Not (r.BOF And r.EOF) Then
            r.MoveLast
            r.MoveFirst
    
            MsgChanges = " Change Request(s) - "
            ' loop through record set to get CR number
            Do While Not r.EOF
                MsgChanges = MsgChanges & r!CRNumber & ", "
                r.MoveNext
            Loop
    
            If Len(MsgChanges) > 0 Then
                ' remove trailing comma
                MsgChanges = Left(MsgChanges, Len(MsgChanges) - 2)
            End If
    (plus the Dim statements and the clean up) was to create the subject line in the "Sub SendSelectCRs_Click" routine.




    I don't understand the question.
    The data to run the rptSelectChanges comes from frmSelectChanges When I click on the select button it goes to the below VBA:

    Code:
    Private Sub SelectChanges_Click()
        Dim strWhere As String, ctl As Control, varItem As Variant
        Set ctl = Me.MyChanges
       
    If Me.MyChanges.ItemsSelected.Count = 0 Then
         MsgBox "Nothing was selected"
            Exit Sub
        End If
    
    For Each varItem In ctl.ItemsSelected
       strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
    
    Next varItem
       strWhere = Left(strWhere, Len(strWhere) - 1)
       DoCmd.OpenReport "rptSelectChanges", acViewReport, , "CRNumber IN(" & strWhere & ")"
       DoCmd.Close acForm, "frmSelectChanges"
    End Sub
    This is where I got the strWhere. I was thinking the sSQL= could be replaced with the strWhere.

  8. #8
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    If I use the following code I get all the CR Numbers added to the subject line.

    Code:
    Set rs = CurrentDb.OpenRecordset("Select CRNumber from[qrySelectChanges]order by CRID ASC")
    
        If Not (rs.BOF And rs.EOF) Then
            rs.MoveLast
            rs.MoveFirst
            MsgChanges = " Selected Changes(s) - " ' loop through record set to get CR number
            
            Do While Not rs.EOF
                MsgChanges = MsgChanges & rs!CRNumber & ", "
            rs.MoveNext
            
            Loop
    
            If Len(Mstrwhere) > 0 Then
                MsgChanges = Left(MsgChanges, Len(MsgChanges) - 2)
            End If
         End If
    My thought process was that upon selection of the CR Numbers from frmSelectChanges and clicking the Select button, it would populate qrySelectChanges with the required information. as below

    For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"

    Next varItem
    strWhere = Left(strWhere, Len(strWhere) - 1)
    DoCmd.OpenReport "rptSelectChanges", acViewReport, , "CRNumber IN(" & strWhere & ")"

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    This is where I got the strWhere. I was thinking the sSQL= could be replaced with the strWhere.
    If I understand the question correctly, you would not be able to use "strWhere" in place of the SQL because the scope of the variable "strWhere" in "Sub SelectChanges_Click()" is local to the sub. When "Sub SelectChanges_Click()" finishes executing, the variable "strWhere" is destroyed.

  10. #10
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Quote Originally Posted by ssanfu View Post
    If I understand the question correctly, you would not be able to use "strWhere" in place of the SQL because the scope of the variable "strWhere" in "Sub SelectChanges_Click()" is local to the sub. When "Sub SelectChanges_Click()" finishes executing, the variable "strWhere" is destroyed.

    How could I "store" the data in for retrieval?

    Set r = CurrentDb.OpenRecordset("Select CRNumber from[qrySelectChanges]order by CRID ASC")

    If Not (r.BOF And r.EOF) Then
    r.MoveLast
    r.MoveFirst
    MsgChanges = " Selected Changes(s) - " ' loop through record set to get CR number

    Do While Not r.EOF
    MsgChanges = MsgChanges & r!CRNumber & ", "
    r.MoveNext

    Loop

    If Len(Mstrwhere) > 0 Then
    MsgChanges = Left(MsgChanges, Len(MsgChanges) - 2)
    End If
    End If

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    How could I "store" the data in for retrieval?
    In a global variable
    In an unbound control on a form
    In a table.

    (maybe a TempVar)

  12. #12
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Quote Originally Posted by ssanfu View Post
    In a global variable
    In an unbound control on a form
    In a table.

    (maybe a TempVar)
    I do have an unbound field named "MyChanges" where I select the Changes - [frmSelectChanges]. In the Row Source I have:

    Code:
    SELECT DISTINCT Format(([tblChangeRequest].[CRNo]+([SubNo]*0.01)),"Fixed") AS CRNumber, tblChangeRequest.CRID
    FROM tblChangeRequest
    GROUP BY Format(([tblChangeRequest].[CRNo]+([SubNo]*0.01)),"Fixed"), tblChangeRequest.CRID
    HAVING (((tblChangeRequest.CRID)>25))
    ORDER BY tblChangeRequest.CRID;

    Record Source for the form itself is [tblChangeRequest]

    If I run the Row Source query I get the whole set of CRNumbers. This is where I figured I'd need to glean the data off this by making it public and somehow using ctl.ItemsSelected:

    Code:
    Public Sub SelectChanges_Click()     Dim strWhere As String, ctl As Control, varItem As Variant
        Set ctl = Me.MyChanges
       
    If Me.MyChanges.ItemsSelected.Count = 0 Then
         MsgBox "Nothing was selected"
            Exit Sub
        End If
    
    For Each varItem In ctl.ItemsSelected
       strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
    
    Next varItem
       strWhere = Left(strWhere, Len(strWhere) - 1)
       DoCmd.OpenReport "rptSelectChanges", acViewReport, , "CRNumber IN(" & strWhere & ")"
       DoCmd.Close acForm, "frmSelectChanges"
    End Sub
    I think then I may only need something like this:

    MsgChanges = " Selected Changes(s) - " & Form_frmSelectChanges.MyChanges

    It runs all the way through, but leaves a blank space where the CRNumbers would be (In the email Subject line). ""AWA 17 Selected Changes(s) - - 15 Jun 2016"

    MsgChanges = " Selected Changes(s) - " & Report_rptSelectChanges.CRNumber

    This gets me the first CRNumber of several selected CRNumbers. - "AWA 17 Selected Changes(s) - 2.00 - 15 Jun 2016" where I selected a couple more of higher value.

  13. #13
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I am getting more and more confused!

    First, terminology...
    I do have an unbound field named "MyChanges" where I select the Changes - [frmSelectChanges].
    You do not (cannot) have an "unbound field". Tables have fields, forms have controls. Forms can be bound or unbound (has a row source or not). If a form is bound, the form can have bound or unbound controls (has a control source or not).

    I think you mean unbound control named "MyChanges" and I am guessing the control is a multi-select list box.



    OK, moving on, this is what I think you are doing:

    1) You open a form, "frmSelectChanges", where you have a multi-select list box.
    2) You select the CR numbers
    3) You click a button, "SelectChanges", open a report and close the form "frmSelectChanges".
    4) You then use the menu to save the report that is open as a PDF.
    5) You open another form (or the form was open and in the background), where you click the button "SendSelectCRs" to send the PDF report in an email.

    At step 3, where you close the form "frmSelectChanges", the value in variable "strWhere" is gone/destroyed/unavailable because the subroutine has finished executing and the value of "strWhere" has not been saved anywhere.

    So, to use the value of "strWhere" in the email subject line, somehow the value that was in "strWhere" has to be recreated again OR the value of "strWhere" must be saved somehow/somewhere BEFORE the sub "SelectChanges" ends.

    That is why I suggested options:

    In a global variable
    In an unbound control on a form
    In a table.

    (maybe use a TempVar)




    Am I close????

  14. #14
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Pretty close.
    I am corrected on controls vs fields. I knew I would get bit on it when I selected submit.

    Actions taken
    1. Select Command Button "Select Changes" from frmStart.
    2. Auto closes frmStart, Opens frmSelectChanges
    3. Select the changes from an unbound control name Mychanges via multi-select list box.
    4. Select the Command Button "Select"
    5. Initiates the VBA:
    Code:
    Public Sub SelectChanges_Click()     
    Dim strWhere As String, ctl As Control, varItem As Variant
        Set ctl = Me.MyChanges
       
    If Me.MyChanges.ItemsSelected.Count = 0 Then
         MsgBox "Nothing was selected"
            Exit Sub
        End If
    
    For Each varItem In ctl.ItemsSelected
       strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
    
    Next varItem
       strWhere = Left(strWhere, Len(strWhere) - 1)
       DoCmd.OpenReport "rptSelectChanges", acViewReport, , "CRNumber IN(" & strWhere & ")"
       DoCmd.Close acForm, "frmSelectChanges"
    End Sub
    5. Auto Opens rptSelectChanges, does not close frmSelectChanges
    6. Select Command Button "Email" on rptSelectChanges, Does not close rptSelectChanges
    7. Email message opens via VBA
    8. Publishes and Adds output of rptSelectChanges to Email Msg.
    9. Select Command Button "Send" - email sent
    10. Kills the temporary saved attachemnt.
    11. Closes rptSelectChanges and frmSelectChanges
    12. Opens frmStart.

    Thus I thought you could use the data from when you selected the changes via frmSelectChanges. Is my reasoning incorrect? I am a point A to Point D type of person who isn't all that artistically bent. Perhaps my logic flow is absent.

    Cheers

  15. #15
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    For clarification:

    6. Select Command Button "Email" on rptSelectChanges, Does not close rptSelectChanges
    9. Select Command Button "Send" - email sent
    These 2 buttons are on the REPORT rptSelectChanges, not a form????

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Missing stuff in Email Subject line
    By Thompyt in forum Programming
    Replies: 3
    Last Post: 04-13-2016, 02:26 PM
  2. Replies: 5
    Last Post: 11-01-2014, 05:18 PM
  3. Multiple Data on one line?
    By alicias4 in forum Reports
    Replies: 2
    Last Post: 02-07-2013, 05:53 PM
  4. VBA check for email subject line
    By problem_addic in forum Access
    Replies: 4
    Last Post: 03-12-2010, 02:33 PM
  5. Multiple detail items per line
    By needafix in forum Reports
    Replies: 3
    Last Post: 10-22-2009, 11:04 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums