Page 3 of 3 FirstFirst 123
Results 31 to 45 of 45
  1. #31
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Gents,


    This is what I have now:

    Code:
    Public Sub SendOOB_Click()
      On Error GoTo ErrorMsgs
    
    Set rs = CurrentDb.OpenRecordset("SELECT OOBNumber, Priority FROM qRecSourceOOBChanges Order by [Level], CRID ASC")
    Set objOutlook = CreateObject("Outlook.Application")
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    
    strHdrMail = "This is a follow-on action from the AORB/CCB/TEWG discussion on CR" & OOBNumber & ". If needed, please back-brief your O6 for SA, " _
                 & "and let us know if there are any issues or concerns. The Change Request priority is " & Priority & " with " & Hr & "hours until" _
                 & "CR" & OOBNumber & " is automatically approved (GO OOB Excepeted). Please provide your votes NLT " & DTG & "." & vbCrLf & vbCrLf
    
    'While Not rs.EOF
    
    'strBdyMail = "Date Issue Identified: " & Chr(9) & Chr(9) & !Dates & vbCrLf & vbCrLf" _
    '             & "Priority: " & Chr(9) & Chr(9) & Chr(9) & !Priority & vbCrLf _
    '             & "CR Number: " & Chr(9) & Chr(9) & Chr(9) & !OOBNumber & vbCrLf _
    '             & "AO Recommendation: " & Chr(9) & Chr(9) & !AOVote & vbCrLf & vbCrLf _
    '             & "Change Requested: " & Chr(9) & Chr(9) & ![ChangeRequested] & vbCrLf & vbCrLf _
    '             & "Unit & Section: " & Chr(9) & Chr(9) & !Units & vbCrLf _
    '             & "MTOE Para & Bumper Number: " & Chr(9) & ![MTOEParas] & vbCrLf & vbCrLf _
    '             & "Rationale: " & Chr(9) & Chr(9) & !Rationale & vbCrLf & vbCrLf _
    '             & "Notes: " & Chr(9) & Chr(9) & Chr(9) & !NOTES & vbCrLf _
    '             & "Action Items: " & Chr(9) & Chr(9) & !ActionItems & vbCrLf & vbCrLf
     
    ' rs.MoveNext
    'Wend
    ' rs.Close
    
    With objOutlookMsg
      .Subject = NIE & " - " & Label & " - " & Tod
      .Body = strHdrMail & strBdyMail & SigBlock
       DoCmd.OutputTo 3, "rptOOB", acFormatPDF, "C:\Temp\" & NIE & " - " & Label & " - " & Tod & ".pdf", , 0
      .Attachments.Add ("C:\Temp\" & NIE & " - " & Label & " - " & Tod & ".pdf")
      .To = ""
      .Display
      Kill "C:\Temp\" & NIE & " - " & Label & " - " & Tod & ".pdf"
      
      DoCmd.Close acForm, "frmOOBChangeSelect"
      DoCmd.Close acReport, "rptOOB"
      DoCmd.OpenForm "frmStart"
     
      End With
      
     Set objOutlookMsg = Nothing
     Set objOutlook = Nothing
     Set objOutlookAttach = Nothing
    
      Exit Sub
    
    ErrorMsgs:
     If Err.Number = "287" Then
      MsgBox "You selected No to the Outlook security warning. Rerun the procedure and click Yes to access e-mail addresses to send your message."
     Else
     MsgBox Err.Number & " " & Err.Description
    End If
    End Sub
    I now get an error 2465 Can't finf the field 'Priority' referred to in your expression. Ihave tried Priority1: IIf(IsNull([Priority]),'Medium',[Priority]) in case. Still the same error. Can't find it. I have looked at the differences in the control spelling. All appear to be the same.

    Code:
    Sub subCreateQuery(arg)
        Dim sSQL As String
        Select Case arg
            Case 1
                sSQL = "SELECT [CRNo]+([SubNo]*0.01) AS OOBNumber, Priority, IIf(IsNull([Priority]),'Medium',[Priority]) AS Priority1, CRID, [Level], NIE, ChangeType, DateID, " _
                & "ChangeRequested, Rationale, NOTES, ActionItems, AOVote, O6Vote, Hr, " _
                & "Format([DateID],'dddd'', ''mmm d yyyy') AS Dates, " _
                & "Format(Now()+([Hr]/24),'hhnn dddd'', ''mmm d yyyy') AS [Time], " _
                & "Format(Now()+([Hr]/24),'hhnn dddd'', ''mmm d yyyy') AS DTG, [Unit] & Chr(13) & Chr(10) & " _
                & "[Section] AS Units, [HBVersion] & Chr(13) & Chr(10) & [ApproxPage] AS HBVers, " _
                & "[MTOEPara] & Chr(13) & Chr(10) & [BumperNum] AS MTOEParas, " _
                & "[Requestor] & Chr(13) & Chr(10) & [Sponsor] AS People, " _
                & "DateDiff('d',[DateID],[DateClosed]) AS DaysOpen, [Priority] & ' ' & [Level] & ' OOB Change Request' AS Label " _
                & "FROM TblChangeRequest " _
                & "WHERE (([ActionComplete])=False) AND (([AOVote])" & TempVars!AOSelects & " AND (O6Vote) " & TempVars!O6Selects & " and(([CRNo])<>0) AND (([HR])=0));"
                          
                Call fcnCustomizeSQL("qRecSourceOOBChanges", sSQL)
            
            Case 2
            Case 3
        End Select
    End Sub

  2. #32
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Simplify: Try the abbreviated query and get that worked out, then remove some comment quotes and carry on. Also note first Priority in brackets...

    Code:
    Sub subCreateQuery(arg)
        Dim sSQL As String
        Select Case arg
            Case 1
                sSQL = "SELECT [CRNo]+([SubNo]*0.01) AS OOBNumber, [Priority], IIf(IsNull([Priority]),'Medium',[Priority]) AS Priority1, CRID, [Level], NIE, ChangeType, DateID FROM tblChangeRequest;"
            '    & "ChangeRequested, Rationale, NOTES, ActionItems, AOVote, O6Vote, Hr, " _
            '    & "Format([DateID],'dddd'', ''mmm d yyyy') AS Dates, " _
            '    & "Format(Now()+([Hr]/24),'hhnn dddd'', ''mmm d yyyy') AS [Time], " _
            '    & "Format(Now()+([Hr]/24),'hhnn dddd'', ''mmm d yyyy') AS DTG, [Unit] & Chr(13) & Chr(10) & " _
            '    & "[Section] AS Units, [HBVersion] & Chr(13) & Chr(10) & [ApproxPage] AS HBVers, " _
            '    & "[MTOEPara] & Chr(13) & Chr(10) & [BumperNum] AS MTOEParas, " _
            '    & "[Requestor] & Chr(13) & Chr(10) & [Sponsor] AS People, " _
            '    & "DateDiff('d',[DateID],[DateClosed]) AS DaysOpen, [Priority] & ' ' & [Level] & ' OOB Change Request' AS Label " _
            '    & "FROM TblChangeRequest " _
            '    & "WHERE (([ActionComplete])=False) AND (([AOVote])" & TempVars!AOSelects & " AND (O6Vote) " & TempVars!O6Selects & " and(([CRNo])<>0) AND (([HR])=0));"
                          
                Call fcnCustomizeSQL("qRecSourceOOBChanges", sSQL)
            
            Case 2
            Case 3
        End Select
    End Sub
    Last edited by davegri; 08-08-2016 at 03:58 PM. Reason: syntax

  3. #33
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Dave,
    I'll give it a shot. The first [Priority] was for the report. This will come up later as a need for the text version. I was thinking the bracketed Priority was coming up null or blank and killing the code, thus the Priority1 add.

  4. #34
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    I was thinking the bracketed Priority was coming up null or blank and killing the code, thus the Priority1 add.
    If Priority is null, try NZ([Priority],""). That will change it to a zero length string. NZ([Priority],"Anything") will work too.

  5. #35
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    All,
    I figured the issue out.....I think. I had to ensure the labels on the form rptOOB were the same as the controls I wanted in text. The next issue to concur

    It doesn't run the second selected CR from the form.

    Code:
    While Not rs.EOF
    strBdyMail = "Date Issue Identified: " & Chr(9) & Chr(9) & Dates & vbCrLf & vbCrLf _
                 & "Priority: " & Chr(9) & Chr(9) & Chr(9) & Priority & vbCrLf _
                 & "CR Number: " & Chr(9) & Chr(9) & Chr(9) & OOBNumber & vbCrLf & vbCrLf _
                 & "AO Recommendation: " & Chr(9) & Chr(9) & AOVote & vbCrLf _
                 & "O6 Recommendation: " & Chr(9) & Chr(9) & O6Vote & vbCrLf & vbCrLf _
                 & "Change Requested: " & Chr(9) & Chr(9) & [ChangeRequested] & vbCrLf & vbCrLf _
                 & "Unit & Section: " & Chr(9) & Chr(9) & Unitss & vbCrLf _
                 & "MTOE Para & Bumper Number: " & Chr(9) & [MTOEParass] & vbCrLf & vbCrLf _
                 & "Rationale: " & Chr(9) & Chr(9) & Rationale & vbCrLf & vbCrLf _
                 & "Notes: " & Chr(9) & Chr(9) & Chr(9) & NOTES & vbCrLf _
                 & "Action Items: " & Chr(9) & Chr(9) & ActionItem & vbCrLf & vbCrLf
      rs.MoveNext
    Wend
     rs.Close
    I m surmising that the While next is not working.
    Last edited by Thompyt; 08-10-2016 at 01:18 PM.

  6. #36
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    All, I figured it out. I'm a bit astounded I did.

    I needed to add strBdyMail & to: strBdyMail = strBdyMail & to "........

    Thanks Dave and ssanfu!

  7. #37
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Congratulations on figuring it out!
    And happy to help.. (Dave did most of the helping... )

  8. #38
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    ALLCON,
    Looks like I lied. I didn't get it corrected. If I have 3 choices on my select OOBNumber and I choose one, I will get a repeat of the selected OOBNumber duplicated 3 times. The attached pdf looks fine and will only output the select OOB.

    I am using the following to get the selection to the report:

    Code:
    Option Compare Database
      Option Explicit
      Dim MsgChanges As String, strWhere As String, strWhere2 As String, ctl As Control, varItem As Variant
    
    Private Sub AOBNumber_AfterUpdate()
        Me.Filter = "OOBNumbers=" & Me.OOBNumber
        Me.FilterOn = True
    End Sub
    
    Private Sub CancelChanges_Click()
    
        TempVars.RemoveAll
        Call subCreateQuery(1)
        DoCmd.Close acForm, "frmOOBChangeSelect"
        DoCmd.OpenForm "frmStart"
        
    End Sub
    
    Public Sub SelectOOBChanges_Click()
    
        Set ctl = Me.OOBNumber
        
    If Me.OOBNumber.ItemsSelected.Count = 0 Then
         MsgBox "Nothing was selected"
            Exit Sub
        End If
    
    For Each varItem In ctl.ItemsSelected
       strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
       strWhere2 = strWhere2 & " " & ctl.ItemData(varItem) & ","
    
    Next varItem
       strWhere = Left(strWhere, Len(strWhere) - 1)
       strWhere2 = Left(strWhere2, Len(strWhere2) - 1)
       
       Me.OOBChanges = strWhere2
     
     DoCmd.OpenReport "rptOOB", acViewReport, , "OOBNumber IN(" & strWhere & ")"
    
    End Sub

  9. #39
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe try this:
    Code:
    Public Sub SelectOOBChanges_Click()
        Dim ctl As Control
    
        strWhere = Empty    'clears module level variable
        strWhere2 = Empty'clears module level variable
    
        Set ctl = Me.OOBNumber
    
        If ctl.ItemsSelected.Count = 0 Then
        '     If Me.OOBNumber.ItemsSelected.Count = 0 Then
            MsgBox "Nothing was selected"
            Set ctl = Nothing
            Exit Sub
        End If
    
        For Each varItem In ctl.ItemsSelected
            strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
            strWhere2 = strWhere2 & " " & ctl.ItemData(varItem) & ","
    
        Next varItem
    
    
        strWhere = Left(strWhere, Len(strWhere) - 1)
        strWhere2 = Left(strWhere2, Len(strWhere2) - 1)
    
        Me.OOBChanges = strWhere2
    
        '   MsgBox strWhere
        '   MsgBox strWhere2
    
        DoCmd.OpenReport "rptOOB", acViewReport, , "OOBNumber IN(" & strWhere & ")"
    
        Set ctl = Nothing
    
    End Sub
    If I select 4 and 15, this code opens a report with two records. (I am using an early dB version...)
    Not sure why "strWhere" & "strWhere2",,,, only "strWhere" is used ..........

  10. #40
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    strwhere & strwhere2 was due to me changing the output for the filename & subject line. I never deleted one. The code as original worked as needed for the report. It is the text output of the email that I am having an issue with. I did get an error on *ctl*. part so I removed the * . It works fine that way. BUT I do get the same text output as before. if I have 3 lines to select from, I can select 2 and the report shows both selected lines as required. When I select the email button I get the report pdf attached with the correct selected lines, and the text shows the first line repeatedly for 3 times. The same amount of the possible amount of selections.

    Example:
    Using the frmOOBChangeSelect I have 3 possibilities:
    13
    13.01
    14

    I select 13 & 13.01
    The report and the email pdf attachment show 13 & 13.01

    The text shows:

    Date Issue Identified: 17 May 2016 DaysOpen: 79
    Priority: Medium
    CR Number: 13
    AO Recommendation: Approve
    O6 Recommendation: Approve
    Change Requested: AddVROD for demonstration during AWA 17.
    Unit & Section: 1-6 CAB Many
    MTOE Para & Bumper: Many Many
    Rationale:
    Notes: Working 1094 for Electronic Attack,Not connected to any network. Caveat - Spectrum must be approved forusage
    Action Items: ATEC/C3T/BMC - Approved
    DA G3/5/7 - SOSE&I VROD Only
    __________________________________________________ ________________________
    Date Issue Identified: 17 May 2016 DaysOpen: 79
    Priority: Medium
    CR Number: 13
    AO Recommendation: Approve
    O6 Recommendation: Approve
    Change Requested: AddVROD for demonstration during AWA 17.
    Unit & Section: 1-6 CAB Many
    MTOE Para & Bumper: Many Many
    Rationale:
    Notes: Working 1094 for Electronic Attack, Not connected to any network. Caveat- Spectrum must be approved for usage
    Action Items: ATEC/C3T/BMC - Approved
    DA G3/5/7 - SOSE&I VROD Only
    __________________________________________________ ________________________
    Date Issue Identified: 17 May 2016 DaysOpen: 79
    Priority: Medium
    CR Number: 13
    AO Recommendation: Approve
    O6 Recommendation: Approve
    Change Requested: AddVROD for demonstration during AWA 17.
    Unit & Section: 1-6 CAB Many
    MTOE Para & Bumper: Many Many
    Rationale:
    Notes: Working 1094 for Electronic Attack,Not connected to any network. Caveat - Spectrum must be approved forusage
    Action Items: ATEC/C3T/BMC - Approved
    DA G3/5/7 - SOSE&I VROD Only
    __________________________________________________ ________________________

  11. #41
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I select 13 & 13.01
    The report and the email pdf attachment show 13 & 13.01

    But the email text shows the same message repeated.
    OK, it looks like the report is grouped on "Level". This take care of duplicate records in the report record source - kind if like using the "DISTINCT" keyword for a query.

    So try this: pick the same options as above. Open the IDE and set a break point on the line "Public Sub SendOOB_Click()".
    Go to the report and click EMAIL.
    Now create a new query. In SQL View, paste in
    Code:
    SELECT OOBNumber, Priority FROM qRecSourceOOBChanges Order by [Level], CRID ASC
    (this is the "Set rs..." line) and execute the query. How many/what records records are returned?

    In another new query, you could try
    Code:
    SELECT DISTINCT OOBNumber, Priority FROM qRecSourceOOBChanges Order by [Level], CRID ASC
    to see if the number of records are 2.


    This is the only thing I can come up with for the difference between the report/PDF and the message body.

    Single step through the "SendOOB_Click" code to see what the strBdyMail variable looks like - maybe use "msgbox" or "Debug.Print" just before the line "With objOutlookMsg".

  12. #42
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I had to add these controls as hidden to the rptOOB Report:
    Priority, HR, Time, O6Vote, MTOEParass, HBVerss, Unitss, Dates, ActionItem, DTG, DaysOpen.

    I think it is coming from the rptOOB for the info and counting the amounts on the query. In other words I am looking at the report to fill the email instead of the query. This may be the whole issue.


    SELECT DISTINCT OOBNumber, Priority FROM qRecSourceOOBChanges Order by [Level], CRID ASC : : : Distinct conflicts with [Level]


    SELECT DISTINCT OOBNumber, Priority FROM qRecSourceOOBChanges - Same text output 3X Duplicate.

  13. #43
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    So it looks like you have two different queries - 1 for the report and a different query for the email body.


    You might try modifying the sub SendOOB_Click() like this
    Code:
    Public Sub SendOOB_Click()
        On Error GoTo ErrorMsgs
        Dim strWhere As String
        Dim ctl As Control
        Dim sSQL As String
        Dim varItem As Variant
        Dim rs As DAO.Recordset
        Dim strHdrMail As String
    
        strWhere = Empty
        'get the selections 
        Set ctl = Forms!frmOOBChangeSelect.OOBNumber
    
        For Each varItem In ctl.ItemsSelected
            strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
        Next varItem
        strWhere = Left(strWhere, Len(strWhere) - 1)
    
       'create the query for the mail body
        sSQL = "SELECT Dates, Priority, OOBNumber, AOVote, ChangeRequested, Units, MTOEParas, Rationale, NOTES, ActionItems, [LEVEL]"
        sSQL = sSQL & " FROM qryOOB"
        sSQL = sSQL & " WHERE OOBNumber IN(" & strWhere & ")"
        sSQL = sSQL & " Order by [Level], OOBNumber ASC"  
        Debug.Print sSQL   ' for debugging. 
        Set rs = CurrentDb.OpenRecordset(sSQL)
    .
    .
    . 'rest of your code
    .
    .
    Don't forget
    Code:
    .
    .
        rs.MoveNext
        Wend
         rs.Close
         Set rs = Nothing  '<<== add this line
    .
    .

  14. #44
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Steve,
    I tried it, Same outcome. I removed the control P1 from the form as it isn't needed on the form. The code will then state it can't find the control when I select to email. It looks like the code is referencing rptOOB instead of qRecSourceOOBChanges using this code, or your sSQL.

    Set rs = CurrentDb.OpenRecordset("SELECT OOBNumber, Priority FROM qRecSourceOOBChanges Order by [Level], CRID ASC")

  15. #45
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Thompyt View Post
    I tried it, Same outcome. I removed the control P1 from the form as it isn't needed on the form.
    I have no idea what you mean by "Control P1". Remember, all I have to look at is a cut down version from Post #22 (6/22/2016)


    If I understand correctly, the problem is that the email body text is different that the report text. But you are using different queries to generate each.
    The report used "qryOOB" that is filtered when opened and the email body uses "qRecSourceOOBChanges" that has not filtering at all.

    The report data and the email body text must be from the same query results. They could be from two different named queries, but the SQL must be the same.
    That is what I did in Post #43. I created a query in code that is the same as the filtered report record source.




    In the attached dB, try this.
    Click on the button "AORB OOB"
    Select 32, 32.01 & 32.02 in the list box.
    Click on "Select"
    Look at the report records.......
    Click on the "Email" button.
    Look at the message box and compare to the report records.



    I am totally lost on what you need help with.

    Good luck with your project........




    BTW, this line won't open the record set "rs" because the query "qRecSourceOOBChanges" does not have a field "CRID".
    Set rs = CurrentDb.OpenRecordset("SELECT OOBNumber, Priority FROM qRecSourceOOBChanges Order by [Level], CRID ASC")
    Attached Files Attached Files

Page 3 of 3 FirstFirst 123
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 22
    Last Post: 09-08-2015, 04:43 PM
  2. Command buttons & images displayed on a form
    By crowegreg in forum Programming
    Replies: 2
    Last Post: 10-15-2013, 09:34 AM
  3. Command Buttons Stopped Working on Form
    By genest11 in forum Reports
    Replies: 12
    Last Post: 02-06-2013, 06:59 AM
  4. Form Command buttons
    By chazcoral2 in forum Forms
    Replies: 6
    Last Post: 05-01-2012, 08:10 AM
  5. Command Buttons on form
    By bespra in forum Forms
    Replies: 10
    Last Post: 11-12-2011, 01:50 PM

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