Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-08-2015, 10:55 AM   #1
sspreyer
Newly Registered User
 
Join Date: Nov 2013
Location: England Stanford-le-hope ,Essex
Posts: 243
Thanks: 66
Thanked 0 Times in 0 Posts
sspreyer is on a distinguished road
how to loop through recordset and only attach records that are true

hi ,


I have some code that loops the clone recordset of my subform and generates a email with attachments. I have mainform and continuous subform within the subform I have field called address this holds paths to files and another field called send and this is a yes/no field

now what I'm trying to do is loop through the subform if send field is true then attach file from the address path but if send field is false then do not attach file

here the code I have

Code:
Dim olApp       As Outlook.Application
    Dim olMail      As Outlook.MailItem
    Dim olAttach    As Outlook.Attachment
    Dim rstAttach   As DAO.Recordset
     
    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
    
    With Me.attachments_subform.Form
        ' Commit changes (if necessary)
        If .Dirty Then .Dirty = False
        
        ' Fetch your subform's recordset
        Set rstAttach = .RecordsetClone
    End With
    
    ' Compose email, attach docs and send
    With olMail
        .BodyFormat = olFormatHTML
        .To = "test@test.com"
        .CC = ""
        .Subject = "tests!"
        .Body = "this is a testcode"
        
        ' Add Attachments
         With rstAttach
            If .RecordCount > 0 Then
                .MoveFirst
                Do While Not .EOF
                    strpath = !Address
                    olMail.Attachments.Add strpath, olByValue
                    DoEvents
                    .MoveNext
                Loop
            End If
        End With
        
        .Display
'        .Save
'        .Send
    End With
    
    Set olMail = Nothing
    Set olApp = Nothing
    Set rstAttach = Nothing
thanks in advance

shane

sspreyer is offline   Reply With Quote
Old 07-08-2015, 10:58 AM   #2
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: how to loop through recordset and only attach records that are true

Shane, is this going to be used by multiple users at the same time?
vbaInet is offline   Reply With Quote
Old 07-08-2015, 11:02 AM   #3
sspreyer
Newly Registered User
 
Join Date: Nov 2013
Location: England Stanford-le-hope ,Essex
Posts: 243
Thanks: 66
Thanked 0 Times in 0 Posts
sspreyer is on a distinguished road
Re: how to loop through recordset and only attach records that are true

hi, vbaInet

no this will not be used by multiple user at the same time

thanks

shane

sspreyer is offline   Reply With Quote
Old 07-08-2015, 11:05 AM   #4
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: how to loop through recordset and only attach records that are true

This code looks suspiciously like the one we worked on at some point

After getting the clone, use the Filter property of the recordset to filter it down to those checked records.
vbaInet is offline   Reply With Quote
Old 07-08-2015, 11:20 AM   #5
sspreyer
Newly Registered User
 
Join Date: Nov 2013
Location: England Stanford-le-hope ,Essex
Posts: 243
Thanks: 66
Thanked 0 Times in 0 Posts
sspreyer is on a distinguished road
Re: how to loop through recordset and only attach records that are true

Quote:
Originally Posted by vbaInet View Post
This code looks suspiciously like the one we worked on at some point
.
vbaInet Excellent memory

here my attempt I know it not right but am I going in the right direction before I go tying myself up

Code:
With rstAttach
         .Filter = !Send
          Me.FilterOn = True
          
         
            If .RecordCount > 0 Then
                .MoveFirst
                Do While Not .EOF
                    strpath = !Address
                    olMail.Attachments.Add strpath, olByValue
                    DoEvents
                    .MoveNext
                Loop
            End If
        End With
        
        .Display
'        .Save
'        .Send
    End With
    
    Set olMail = Nothing
    Set olApp = Nothing
    Set rstAttach = Nothing
thanks again

shane
sspreyer is offline   Reply With Quote
Old 07-08-2015, 11:22 AM   #6
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: how to loop through recordset and only attach records that are true

I know when I see my code and my comments

Here's a link that explains the Filter property

https://msdn.microsoft.com/en-us/lib...or=-2147217396

And remove the FilterOn code.
vbaInet is offline   Reply With Quote
Old 07-08-2015, 11:59 AM   #7
sspreyer
Newly Registered User
 
Join Date: Nov 2013
Location: England Stanford-le-hope ,Essex
Posts: 243
Thanks: 66
Thanked 0 Times in 0 Posts
sspreyer is on a distinguished road
Re: how to loop through recordset and only attach records that are true

hi

after looking at the link you provided me this my attempt but i think it trying to add new or edit not filter

Code:
       
        ' Add Attachments
         With rstAttach
           
            If .RecordCount > 0 Then
                .MoveFirst
                     
                Do While Not .EOF
             rstAttach!Send = True
                    strpath = !Address
                    olMail.Attachments.Add strpath, olByValue
                    DoEvents
                    .MoveNext
                Loop
            End If
        End With
        
        .Display
'        .Save
'        .Send
    End With
    
    Set olMail = Nothing
    Set olApp = Nothing
    Set rstAttach = Nothing
thanks in advance

sspreyer is offline   Reply With Quote
Old 07-08-2015, 12:05 PM   #8
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: how to loop through recordset and only attach records that are true

Shane take your time and review the link again. Look carefully at how the Filter property is being used.
vbaInet is offline   Reply With Quote
Old 07-09-2015, 12:48 PM   #9
sspreyer
Newly Registered User
 
Join Date: Nov 2013
Location: England Stanford-le-hope ,Essex
Posts: 243
Thanks: 66
Thanked 0 Times in 0 Posts
sspreyer is on a distinguished road
Re: how to loop through recordset and only attach records that are true

right this is my 2nd attempt still not working

Code:
Dim olApp       As Outlook.Application
    Dim olMail      As Outlook.MailItem
    Dim olAttach    As Outlook.Attachment
    Dim rstAttach   As DAO.Recordset
Dim rstFiltered As DAO.Recordset
     
    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
    
    With Me.attachments_subform.Form
        ' Commit changes (if necessary)
        If .Dirty Then .Dirty = False
        
        ' Fetch your subform's recordset
        Set rstAttach = .RecordsetClone
    End With
    
    ' Compose email, attach docs and send
    With olMail
        .BodyFormat = olFormatHTML
        .To = "test@test.com"
        .CC = ""
        .Subject = "test"
        .Body = "testcode"
        
        ' Add Attachments
         With rstAttach
           
            If .RecordCount > 0 Then
                .MoveFirst
                     
                Do While Not .EOF
             
  .Filter = !Send = True
             Set rstFiltered = .OpenRecordset
          
             
             
       strpath = !Address
                    olMail.Attachments.Add strpath, olByValue
                    DoEvents
                    .MoveNext
                Loop
            End If
        End With
        
        .Display
'        .Save
'        .Send
    End With
    
    Set olMail = Nothing
    Set olApp = Nothing
    Set rstAttach = Nothing
sspreyer is offline   Reply With Quote
Old 07-09-2015, 01:48 PM   #10
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,137
Thanks: 3
Thanked 466 Times in 459 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: how to loop through recordset and only attach records that are true

The original issue was
Quote:
if send field is true then attach file from the address path but if send field is false then do not attach file
Does this mean if the send field is false, then the email is not to be sent
OR the email is to be sent with no attachment.

If the latter, a filtered recordset is not required, just an If/Endif around the attachment add line.
Cronk is offline   Reply With Quote
Old 07-09-2015, 02:20 PM   #11
sspreyer
Newly Registered User
 
Join Date: Nov 2013
Location: England Stanford-le-hope ,Essex
Posts: 243
Thanks: 66
Thanked 0 Times in 0 Posts
sspreyer is on a distinguished road
Re: how to loop through recordset and only attach records that are true

Hi
I a main form with continous subform I just want to loop through the sub form records to find the attachment for the email if the send field is true then attach the file but if the send field is false then don't attach file

Thanks

Shane
sspreyer is offline   Reply With Quote
Old 07-09-2015, 06:33 PM   #12
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: how to loop through recordset and only attach records that are true

It's clearly explained in the article:
Code:
    'Now filter the Recordset to return only the customers from that city
    rst.Filter = "City = '" & strCity & "'"
Now compare what you have with this, it's not even close to what you've written. And after two attempts you give up, instead of looking back at the article.

However, Cronk raised a good point. If the Send field is False (or not checked) would you still send an email without an attachment? This will determine what approach to take.
vbaInet is offline   Reply With Quote
Old 07-10-2015, 11:03 AM   #13
sspreyer
Newly Registered User
 
Join Date: Nov 2013
Location: England Stanford-le-hope ,Essex
Posts: 243
Thanks: 66
Thanked 0 Times in 0 Posts
sspreyer is on a distinguished road
Re: how to loop through recordset and only attach records that are true

Quote:
Originally Posted by vbaInet View Post
And after two attempts you give up, instead of looking back at the article.
To be honest I'm no way shape or form a VBA programmer I muddle by to make my life easier.

BUT I NEVER GIVE UP !!!!

Quote:
Originally Posted by vbaInet View Post
However, Cronk raised a good point. If the Send field is False (or not checked) would you still send an email without an attachment? This will determine what approach to take.
if say one record the send field was false then I would send the email but with out that attachment but the next record maybe true so I would want to attach the attachment

after you point out were I needed to look

Quote:
Originally Posted by vbaInet View Post

'Now filter the Recordset to return only the customers from that city
rst.Filter = "City = '" & strCity & "'" .
here my revised code it still not working but sure I made progress hopefully

Code:
Dim olApp       As Outlook.Application
    Dim olMail      As Outlook.MailItem
    Dim olAttach    As Outlook.Attachment
    Dim rstAttach   As DAO.Recordset
Dim rstFiltered As DAO.Recordset
    Dim strsend As String
    
    
    
    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
    
    With Me.attachments_subform.Form
        ' Commit changes (if necessary)
        If .Dirty Then .Dirty = False
        
        ' Fetch your subform's recordset
        Set rstAttach = .RecordsetClone
         strsend = rst!Send
    End With
    
    ' Compose email, attach docs and send
    With olMail
        .BodyFormat = olFormatHTML
        .To = "test@test.com"
        .CC = ""
        .Subject = "test"
        .Body = "testcode"
        
        ' Add Attachments
         With rstAttach
           
            If .RecordCount > 0 Then
                .MoveFirst
                     
                Do While Not .EOF
           
             
               rst.Filter = "Send = '" & strsend & "'"
             Set rstFiltered = rst.OpenRecordset
                       
             
       strpath = !Address
                    olMail.Attachments.Add strpath, olByValue
                    DoEvents
                    .MoveNext
                Loop
            End If
        End With
        
        .Display
'        .Save
'        .Send
    End With
    
    Set olMail = Nothing
    Set olApp = Nothing
    Set rstAttach = Nothing
changes are in red

error is now object can't be found this line ( strsend = rst!Send)


thanks in advance

shane
sspreyer is offline   Reply With Quote
Old 07-10-2015, 11:38 AM   #14
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: how to loop through recordset and only attach records that are true

Keep at it
Quote:
Originally Posted by sspreyer View Post
if say one record the send field was false then I would send the email but with out that attachment but the next record maybe true so I would want to attach the attachment
In that case Cronk was right on point. You will need to use an IF statement, no filter required. If you weren't sending mails to those records with a Send value of No then you will need the Filter property.

See what happens!
vbaInet is offline   Reply With Quote
Old 07-10-2015, 11:53 AM   #15
sspreyer
Newly Registered User
 
Join Date: Nov 2013
Location: England Stanford-le-hope ,Essex
Posts: 243
Thanks: 66
Thanked 0 Times in 0 Posts
sspreyer is on a distinguished road
Re: how to loop through recordset and only attach records that are true

surely you need to use a filter as its continuous subform with muiltiple records attached to the main form record ID

1) filter through records in sub form that are true

2) Attach only the records that are true to the email

I had another go at the code

Code:
  Dim olApp       As Outlook.Application
    Dim olMail      As Outlook.MailItem
    Dim olAttach    As Outlook.Attachment
    Dim rstAttach   As DAO.Recordset
Dim rstFiltered As DAO.Recordset
    Dim strsend As String
  
    
    
    
    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
    
    With Me.attachments_subform.Form
        ' Commit changes (if necessary)
        If .Dirty Then .Dirty = False
        
        ' Fetch your subform's recordset
        Set rstAttach = .RecordsetClone
      
    End With
    
    ' Compose email, attach docs and send
    With olMail
        .BodyFormat = olFormatHTML
        .To = "test@test.com"
        .CC = ""
        .Subject = "test"
        .Body = "testcode"
        
        ' Add Attachments
         With rstAttach
           
            If .RecordCount > 0 Then
                .MoveFirst
                     
                         strsend = !Send = True
                Do While Not .EOF
           
             
               rstAttach.Filter = "Send = '" & strsend & "'"
             Set rstFiltered = rstAttach.OpenRecordset
          
             
             
       strpath = !Address
                    olMail.Attachments.Add strpath, olByValue
                    DoEvents
                    .MoveNext
                Loop
            End If
        End With
        
        .Display
'        .Save
'        .Send
    End With
    
    Set olMail = Nothing
    Set olApp = Nothing
    Set rstAttach = Nothing
error now is data miss-match in criteria expression line: ( Set rstFiltered = rstAttach.OpenRecordset)


thanks in advance

shane

sspreyer is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Loop through a recordset while adding new records to another? aron.ridgway Modules & VBA 8 06-09-2014 11:59 PM
Create table, loop one recordset, create two records in new, if criteria met tiberius Modules & VBA 3 04-12-2009 07:02 PM
Newbie Question: How do I attach a form to each record in a query's recordset??? HappyBunny Queries 1 07-07-2008 07:04 PM
Recordset EOF is True, but there are records. D.W. Schingenga Modules & VBA 6 06-24-2005 04:13 AM
Loop to all records in a recordset. doulostheou Modules & VBA 9 04-25-2002 05:04 PM




All times are GMT -8. The time now is 08:12 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World