Nested loops

Lanason

Lanason

Registered User.
Local time
Today, 07:42
Joined
Sep 12, 2003
Messages
258
I have some code below that generates emails for me - it includes a nested loop. Since the Reports definition allows both sent to a pre-defined mail list - or has a nested loop to send a customised report to specific users

When I try and run I get error message "Loop without Do"
I cant see why - can anything point out the obvious to me please :banghead:

Code: 
    Dim myrec1 As dao.Recordset      
    Dim myrec2 As dao.Recordset      

    Set myrec1 = Forms!ABC.Recordset 
    With myrec1         
        myrec1.MoveFirst
    Do While Not myrec1.EOF
        StDocName = myrec1![StDocName]      
        ReportType = myrec1![ReportType]    
        MailTo = myrec1![MailTo]            
        MailCC = myrec1![MailCC]            
        MailBcc = myrec1![MailBcc]          
        MailFormat = myrec1![MailFormat]    
        Frequency = myrec1![Frequency]      
        RecordBasedEmailForm = myrec1![RecordBasedEmailForm] 
        Subject = myrec1![Subject]          
        MailGreeting = myrec1![MailGreeting] & vbCrLf & vbCrLf  
        MailMessage = myrec1![MailMessage] & vbCrLf & vbCrLf     
        MailSignature = myrec1![MailSignature]   
        RunQuery = myrec1![RunQuery]             
        EditEmail = myrec1![EditEmail]      

        ProcessRecord = "No"
        If Frequency = "Daily" Then
            ProcessRecord = "Yes"
        ElseIf Frequency = "Monthly" Then
            If DayOfMonth = 1 Then
                If DayOfWeek >= 1 And DayOfWeek < 6 Then
                    ProcessRecord = "Yes"
                End If
            ElseIf DayOfMonth = 2 Then
                If DayOfWeek = 1 Then
                    ProcessRecord = "Yes"
                End If
            ElseIf DayOfMonth = 3 Then
                If DayOfWeek = 1 Then
                    ProcessRecord = "Yes"
                End If
            End If
        Else
            If Today = Frequency Then
                ProcessRecord = "Yes"
            End If
        End If     
    
    If ProcessRecord = "Yes" Then

        If RecordBasedEmailForm <> "None" Then
            DoCmd.OpenForm StDocName, acNormal, , , , acNormal
            Me.FilterOn = False
            Me.Refresh
            Set myrec2 = Forms!frmXYZ.Recordset    
                With myrec2    
                    myrec2.MoveFirst
                Do While Not myrec2.EOF   
                    MailTo = myrec2![FirstOfWorkEmailAddress] 
                    If ReportType = "Email" Then 
                        If MailFormat = "acFormatPDF" Then
                            DoCmd.SendObject acReport, StDocName, acFormatPDF, MailTo, MailCC, MailBcc, Subject, MailGreeting & MailMessage & MailSignature, True
                        Else
                            DoCmd.SendObject acReport, StDocName, MailFormat, MailTo, MailCC, MailBcc, Subject, MailGreeting & MailMessage & MailSignature, True
                        End If
                    ElseIf ReportType = "Printed" Then
                        DoCmd.OpenReport StDocName, acNormal
                    Else
                    End If
                myrec2.MoveNext
                Loop
                End With
                Close
          Else
'****************
        If ReportType = "Email" Then 
            If EditEmail = "Yes" Then
                If MailFormat = "acFormatPDF" Then
                    DoCmd.SendObject acReport, StDocName, acFormatPDF, MailTo, MailCC, MailBcc, Subject, MailGreeting & MailMessage & MailSignature, True
                Else
                    DoCmd.SendObject acReport, StDocName, MailFormat, MailTo, MailCC, MailBcc, Subject, MailGreeting & MailMessage & MailSignature, True
                End If
            Else
                If MailFormat = "acFormatPDF" Then
                    DoCmd.SendObject acReport, StDocName, acFormatPDF, MailTo, MailCC, MailBcc, Subject, MailGreeting & MailMessage & MailSignature, False
                Else
                    DoCmd.SendObject acReport, StDocName, MailFormat, MailTo, MailCC, MailBcc, Subject, MailGreeting & MailMessage & MailSignature, False
                End If
            End If
        ElseIf ReportType = "Printed" Then
            DoCmd.OpenReport StDocName, acNormal
        Else
            MsgBox "Report " & StDocName & " not required"
        End If
        If RunQuery <> "None" Then
            DoCmd.OpenQuery RunQuery  'run query to update fields
        End If
    End If
    myrec1.MoveNext
    Loop
    End With
Close
End If
 
Last edited by a moderator:
I've re-indented it - and you appear to have an extraneous loop command at the bottom?
Code: 
Private Sub Flibble()
    Dim myrec1           As dao.Recordset
    Dim myrec2           As dao.Recordset

    Set myrec1 = Forms!ABC.Recordset
    With myrec1
        myrec1.MoveFirst
        Do While Not myrec1.EOF
            StDocName = myrec1![StDocName]
            ReportType = myrec1![ReportType]
            MailTo = myrec1![MailTo]
            MailCC = myrec1![MailCC]
            MailBcc = myrec1![MailBcc]
            MailFormat = myrec1![MailFormat]
            Frequency = myrec1![Frequency]
            RecordBasedEmailForm = myrec1![RecordBasedEmailForm]
            Subject = myrec1![Subject]
            MailGreeting = myrec1![MailGreeting] & vbCrLf & vbCrLf
            MailMessage = myrec1![MailMessage] & vbCrLf & vbCrLf
            MailSignature = myrec1![MailSignature]
            RunQuery = myrec1![RunQuery]
            EditEmail = myrec1![EditEmail]

            ProcessRecord = "No"
            If Frequency = "Daily" Then
                ProcessRecord = "Yes"
            ElseIf Frequency = "Monthly" Then
                If DayOfMonth = 1 Then
                    If DayOfWeek >= 1 And DayOfWeek < 6 Then
                        ProcessRecord = "Yes"
                    End If
                ElseIf DayOfMonth = 2 Then
                    If DayOfWeek = 1 Then
                        ProcessRecord = "Yes"
                    End If
                ElseIf DayOfMonth = 3 Then
                    If DayOfWeek = 1 Then
                        ProcessRecord = "Yes"
                    End If
                End If
            Else
                If Today = Frequency Then
                    ProcessRecord = "Yes"
                End If
            End If

            If ProcessRecord = "Yes" Then

                If RecordBasedEmailForm <> "None" Then
                    DoCmd.OpenForm StDocName, acNormal, , , , acNormal
                    Me.FilterOn = False
                    Me.Refresh
                    Set myrec2 = Forms!frmXYZ.Recordset
                    With myrec2
                        myrec2.MoveFirst
                        Do While Not myrec2.EOF
                            MailTo = myrec2![FirstOfWorkEmailAddress]
                            If ReportType = "Email" Then
                                If MailFormat = "acFormatPDF" Then
                                    DoCmd.SendObject acReport, StDocName, acFormatPDF, MailTo, MailCC, MailBcc, Subject, MailGreeting & MailMessage & MailSignature, True
                                Else
                                    DoCmd.SendObject acReport, StDocName, MailFormat, MailTo, MailCC, MailBcc, Subject, MailGreeting & MailMessage & MailSignature, True
                                End If
                            ElseIf ReportType = "Printed" Then
                                DoCmd.OpenReport StDocName, acNormal
                            Else
                            End If
                            myrec2.MoveNext
                        Loop
                    End With
                    Close
                Else
                    '****************
                    If ReportType = "Email" Then
                        If EditEmail = "Yes" Then
                            If MailFormat = "acFormatPDF" Then
                                DoCmd.SendObject acReport, StDocName, acFormatPDF, MailTo, MailCC, MailBcc, Subject, MailGreeting & MailMessage & MailSignature, True
                            Else
                                DoCmd.SendObject acReport, StDocName, MailFormat, MailTo, MailCC, MailBcc, Subject, MailGreeting & MailMessage & MailSignature, True
                            End If
                        Else
                            If MailFormat = "acFormatPDF" Then
                                DoCmd.SendObject acReport, StDocName, acFormatPDF, MailTo, MailCC, MailBcc, Subject, MailGreeting & MailMessage & MailSignature, False
                            Else
                                DoCmd.SendObject acReport, StDocName, MailFormat, MailTo, MailCC, MailBcc, Subject, MailGreeting & MailMessage & MailSignature, False
                            End If
                        End If
                    ElseIf ReportType = "Printed" Then
                        DoCmd.OpenReport StDocName, acNormal
                    Else
                        MsgBox "Report " & StDocName & " not required"
                    End If
                    If RunQuery <> "None" Then
                        DoCmd.OpenQuery RunQuery  'run query to update fields
                    End If
                End If
                myrec1.MoveNext
           [COLOR="Red"] Loop[/COLOR]
        End With
        Close
    End If


End Sub
 
don't I need it ??

If I take it out I get "Do without loop"
 
Hmmm - let me have another look - I was Friday afternoon quick with my assumption...
 
Actually it appears to be a mis-placed end if. Change the end of it to;
Code: 
                    End If
                End If
                myrec1.MoveNext
            End If
        Loop
    End With
    Close
 
thanks buddy I will test on Monday when back:D
 
End If
myrec1.MoveNext
End If
End If
Loop
End With
Close

worked - it was about positioning - thanks
 
You must log in or register to reply here.

Similar threads

L
Send looping Email
Replies
3
Views
1,061
looma123
L
G
How is the best way to add this revision to a sendobject function?
Replies
14
Views
1,654
gojets1721
G
J
Save Email to Directory
2 3
Replies
58
Views
6,371
johankotze
J
Lanason
Not finding end of the file in loop
Replies
11
Views
894
Deleted member 147267
D
Lanason
pdfformat
Replies
10
Views
1,311
Lanason
Lanason

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom