Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Subform Email not working correctly 1

Status
Not open for further replies.
niteraven

niteraven

Technical User
Oct 26, 2006
92
US
Hello
I have a main form (frmECNOE) that has one subform (subfrmECNDE). I am trying to send two different emails, based on whether ImpactYN field is true (This field is in the subform) First the code is sending to all, instead of testing for True value in field ImpactYN. Second when first record in the subform is ImpactYN = false, then all also receive and email, instead of only if ImpactYN = false getting the email.

I am placing both mainform and subform information in the body of email. I have the emails sending, just to everyone instead of delineating. I have tried to move a few statements around with no luck.

Any help is appreciated.
Raven

Code: 
 Dim StrEmailName As String
Dim StrSubEmail As String
Dim Elook As New Outlook.Application
Dim EnewLook As Outlook.MailItem
Dim ADL As String
Dim rcst As DAO.Recordset
Dim SfrmEmail As String
            
    Set EnewLook = Elook.CreateItem(olMailItem)
           
    Set rcst = Me!subfrmECNDE.Form.RecordsetClone
                   
    SfrmEmail = Me!subfrmECNDE.Form!Combo33

    ADL = vbNewLine & vbNewLine


If Me.DraftStatus.Value = -1 Then
    
    With EnewLook

    With rcst


        If rcst!ImpactYN = True Then
                    
            StrSubEmail = "ECN  " & Me.ecnnumber & " , Notify Only  THIS IS A TEST!!! " & Me.[Customer Name]
            
                
                    If (rcst.RecordCount) Then
                        rcst.MoveFirst
                        StrEmailName = ""
                        Do Until rcst.EOF
                            
                            StrEmailName = StrEmailName + ";" + Nz(rcst!DeptEmail, "")
                            
                            rcst.MoveNext
                        Loop
                    Else
                        
                        msg = "THERE ARE NO RECORDS WITH NOTIFY ONLY TO EMAIL"
                               
                        Style = vbInformation + vbOKOnly
                        Title = "NO RECORDS TO SEND EMAILS TO"
                        MsgBox msg, Style, Title
                    End If
         
            
            EnewLook.To = StrEmailName
            EnewLook.Subject = StrSubEmail
            EnewLook.Body = "On ECN  " & Me.ecnnumber & " , Customer Name: " & Me.[Customer Name] & ADL & _
                    "Originator:  " & Me.ORIGINATOR & "  With an Impact Assessment of :  " & Me.ECNIMPACT & ADL & _
                    "ISSUE:  " & Me.ECNISSUE & ADL & _
                    "CHANGE:  " & Me.ECNCHANGE
            
            
            EnewLook.Send
            rcst.Close
            
            Set rcst = Nothing
            
         Else
              
            Dim AItemN As String  
            StrSubEmail = "ECN  " & Me.ecnnumber & " , APPROVAL NEEDED - THIS IS A TEST!  " & Me.[Customer Name]
            ADL = vbNewLine & vbNewLine
            'AItemN = Me!subfrmECNDE.Form!ActionItem  
                    If (rcst.RecordCount) Then
                        rcst.MoveFirst
                        StrEmailName = ""
                        AItemN = ""
                        Do Until rcst.EOF
                            
                            StrEmailName = StrEmailName + ";" + Nz(rcst!DeptEmail, "")
                            AItemN = AItem & ADL + Nz(rcst!ActionItem, "")
                            
                            rcst.MoveNext
                        Loop
                    Else
                        
                        msg = "THERE ARE NO RECORDS WITH NOTIFY ONLY TO EMAIL"
                               
                        Style = vbInformation + vbOKOnly
                        Title = "NO RECORDS TO SEND EMAILS TO"
                        MsgBox msg, Style, Title
                    End If
               
            
            EnewLook.To = StrEmailName
            EnewLook.Subject = StrSubEmail
            EnewLook.Body = "On ECN  " & Me.ecnnumber & " , Customer Name: " & Me.[Customer Name] & ADL & _
                    "Originator:  " & Me.ORIGINATOR & "  With an Impact Assessment of :  " & Me.ECNIMPACT & ADL & _
                    "ISSUE:  " & Me.ECNISSUE & ADL & _
                    "CHANGE:  " & Me.ECNCHANGE & ADL & _
                    "THE ACTION ITEMS REQUESTED IS AS FOLLOWS  " & ADL & _
                        AItemN
            EnewLook.Send
            rcst.Close
            
            Set rcst = Nothing
              
        End If
       End With
     End With
    End If

This didn't work either


Code: 
Dim StrEmailName As String
Dim StrSubEmail As String
Dim Elook As New Outlook.Application
Dim EnewLook As Outlook.MailItem
Dim ADL As String
Dim rcst As DAO.Recordset
Dim SfrmEmail As String
            
    Set EnewLook = Elook.CreateItem(olMailItem)
           
    Set rcst = Me!subfrmECNDE.Form.RecordsetClone
                   
    SfrmEmail = Nz(Me!subfrmECNDE.Form!Combo33, "")
    

    ADL = vbNewLine & vbNewLine


If Me.DraftStatus.Value = -1 Then
    
    With EnewLook

    With rcst


        If rcst!ImpactYN = True Then
                    
            StrSubEmail = "ECN  " & Me.ecnnumber & " , Notify Only  THIS IS A TEST!!! " & Me.[Customer Name]
            
                
                    If (rcst.RecordCount) Then
                        rcst.MoveFirst
                        StrEmailName = ""
                        Do Until rcst.EOF
                            
                            StrEmailName = StrEmailName + ";" + Nz(rcst!DeptEmail, "")
                            
                            rcst.MoveNext
                        Loop
                    Else
                        
                        msg = "THERE ARE NO RECORDS WITH NOTIFY ONLY TO EMAIL"
                               
                        Style = vbInformation + vbOKOnly
                        Title = "NO RECORDS TO SEND EMAILS TO"
                        MsgBox msg, Style, Title
                    End If
         
            
            EnewLook.To = StrEmailName
            EnewLook.Subject = StrSubEmail
            EnewLook.Body = "On ECN  " & Me.ecnnumber & " , Customer Name: " & Me.[Customer Name] & ADL & _
                    "Originator:  " & Me.ORIGINATOR & "  With an Impact Assessment of :  " & Me.ECNIMPACT & ADL & _
                    "ISSUE:  " & Me.ECNISSUE & ADL & _
                    "CHANGE:  " & Me.ECNCHANGE
            
            
            EnewLook.Send
            rcst.Close
            
            Set rcst = Nothing
            
         Else
              
            Dim AItemN As String
            
              
            StrSubEmail = "ECN  " & Me.ecnnumber & " , APPROVAL NEEDED - THIS IS A TEST!  " & Me.[Customer Name]
            ADL = vbNewLine & vbNewLine
            AItemN = rcst!ActionItem
            
            
                    If (rcst.RecordCount) Then
                        rcst.MoveFirst
                        StrEmailName = ""
                        AItemN = ""
                        Do Until rcst.EOF
                            
                            StrEmailName = StrEmailName + ";" + Nz(rcst!DeptEmail, "")
                            AItemN = AItem & ADL + Nz(rcst!ActionItem, "")
                            
                            rcst.MoveNext
                        Loop
                    Else
                        
                        msg = "THERE ARE NO RECORDS WITH NOTIFY ONLY TO EMAIL"
                               
                        Style = vbInformation + vbOKOnly
                        Title = "NO RECORDS TO SEND EMAILS TO"
                        MsgBox msg, Style, Title
                    End If
               
            
            EnewLook.To = StrEmailName
            EnewLook.Subject = StrSubEmail
            EnewLook.Body = "On ECN  " & Me.ecnnumber & " , Customer Name: " & Me.[Customer Name] & ADL & _
                    "Originator:  " & Me.ORIGINATOR & "  With an Impact Assessment of :  " & Me.ECNIMPACT & ADL & _
                    "ISSUE:  " & Me.ECNISSUE & ADL & _
                    "CHANGE:  " & Me.ECNCHANGE & ADL & _
                    "THE ACTION ITEMS REQUESTED IS AS FOLLOWS  " & ADL & _
                        AItemN
                               
            EnewLook.Send
            rcst.Close
            
            Set rcst = Nothing
              
        End If
       End With
     End With
    End If
 
Sort by date Sort by votes
Seems to me the first thing to debug would be to check your logic
Code: 
 With rcst
    msgbox "Is the variable type boolean? " & VarType(rcst!ImpactYN) = vbBoolean 
    msgbox "ImpactyYN = " & nz(rcst!ImpactYN, "Null")
    If rcst!ImpactYN = True Then
 
Upvote 0 Downvote
So I did as you suggested. My logic is off. I need to test each record in the subform recordsetclone for ImpactYN = True. But when I did that it crashed the database. I am thinking it was an infinite loop. I will work on it more. Thanks!
 
Upvote 0 Downvote
Yes your code seems to have some structure problems. As you said you would loop the records in the subform and check if they need to be sent.
You might be able to simplify the code and logic a little bit. I could actually create 2 recordsets using using two different SQL. I do not know how your main form is linked to the subform, but lets say it is by fieldX (could be any field)

Dim RSHasImpact as dao.recordset
Dim RSNoImpact as dao.recordset
dim strSql as string
dim valX as variant

valX = me.fieldX
strSql = "Select * from SomeTable where fieldX = " & valX & " AND ImpactYN = True"
RSHasImpact = currentdb.openrecordset(strSql, dbopnendynaset)
strSql = "Select * from SomeTable where fieldX = " & valX & " AND ImpactYN = False"
RSNoImpact = currentdb.openrecordset(strSql, dbopnendynaset)

so now you can loop without any checks first the records with ImpactYN = true and send your emails. Then do a second loop on those records that ImpactYN = false. Makes the logic a little easier.
 
Upvote 0 Downvote
Thanks MajP. I apologize for it taking so long to post back........always crazy busy here.

Because we are not quite ready for paperless so only the ImpactYN = true emails are being sent now. (You will see the ImpactYN = False commented out for now and a few declarations that are not used yet)

Here is the code - Thanks to MajP!
Code: 
Dim StrEmailName As String
Dim StrSubEmail As String
Dim Elook As New Outlook.Application
Dim EnewLook As Outlook.MailItem
Dim ADL As String

Dim SfrmEmail As String
Dim RSHasImpact As DAO.Recordset
Dim RSNoImpact As DAO.Recordset
Dim StrSQL1 As String
Dim StrSQL2 As String
Dim Dbs As Database
Dim AItemN As String
Dim RSCnt As Integer

            
    Set EnewLook = Elook.CreateItem(olMailItem)
    
    If Me.DWDraftStatus.Value = True Then
    
        Me.Label36.Visible = False
    
                   
    SfrmEmail = Nz(Me!subfrmOE.Form!Combo34, "")
    Set Dbs = CurrentDb()
    

    ADL = vbNewLine & vbNewLine
    
    
    StrSQL1 = "Select * from tblActionItems where DEVID = GetDWNumberOE()  AND ImpactYN = -1"
    'StrSQL2 = "Select * from tblActionItems where ECNNO = GetECNNumber()  AND ImpactYN = 0"
    
    
    Set RSHasImpact = Dbs.OpenRecordset(StrSQL1, dbOpenDynaset)
    'Set RSNoImpact = Dbs.OpenRecordset(StrSQL2, dbOpenDynaset)
    
    
    With RSHasImpact
    If (RSHasImpact.RecordCount) Then
            RSHasImpact.MoveFirst
                    
            StrSubEmail = "PENDING " & Me.RequestFor & "  :" & Me.DWNUMBER & " , Notify Only,  " & Me.[Customer]
                   
            StrEmailName = ""
            Do Until RSHasImpact.EOF
                RSHasImpact.Edit
                StrEmailName = StrEmailName + ";" + Nz(RSHasImpact!DeptEmails, "")
                RSHasImpact!AINotifyS = fOSUserName
                RSHasImpact!AINotifyDate = Date
                RSHasImpact.Update
                
                            
                RSHasImpact.MoveNext
            Loop
            
            EnewLook.To = StrEmailName
            EnewLook.Subject = StrSubEmail
            EnewLook.Body = "On PENDING  " & Me.RequestFor & "  :" & Me.DWNUMBER & "   Customer Name: " & Me.[Customer] & ADL & _
                    "Requestor:   " & Me.DWRequestor & "         With an Impact Assessment of :  " & Me.IMPACTDev & ADL & _
                    "ISSUE:      " & Me.DWdescription & ADL & _
                    "CHANGE:     " & Me.DWJustif & ADL & ADL & _
                    "THIS IS TO NOTIFY YOU OF THE PENDING DEVIATION, YOU DO NOT HAVE ANY APPROVALS OR ACTIONS ITEMS TO COMPLETE." & ADL & _
                    "YOU WILL BE NOTIFIED BY EMAIL WHEN THE ECN IS APPROVED."
            
            
            EnewLook.Send
            RSHasImpact.Close
            
            Set RSHasImpact = Nothing
    Else
        msg = "THERE ARE NO RECORDS WITH NOTIFY ONLY TO EMAIL"
        Style = vbInformation + vbOKOnly
        Title = "NO RECORDS TO SEND EMAILS TO"
        MsgBox msg, Style, Title
    End If
    End With
    
    Else
        Me.Label36.Visible = True
    
    End If
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

TrekBiker
  • Locked
  • Question
Subform not updating 1
Replies
12
Views
329
TrekBiker
TrekBiker
Bubba002
  • Locked
  • Question
Pass a subform fieldname-ID as a string variable in CF expression
2 3
Replies
50
Views
1K
dhookom
dhookom
TrekBiker
  • Locked
  • Question
Use Crosstab query in subform
Replies
9
Views
222
dhookom
dhookom
TrekBiker
  • Locked
  • Question
Form Data Not Updating
Replies
6
Views
281
dhookom
dhookom
Lamar Washington
  • Locked
  • Question
Programmatically send email using only OWA
Replies
2
Views
176
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top