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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-11-2019, 11:51 PM   #1
Lanason
Newly Registered User
 
Lanason's Avatar
 
Join Date: Sep 2003
Location: England
Posts: 255
Thanks: 10
Thanked 0 Times in 0 Posts
Lanason is an unknown quantity at this point
Angry Not finding end of the file in loop

I have a routine that opens a form and creates emails and reports based up fields in the form (day of week etc). There are 31 possible reports.

The routine has a sub loop in case a certain reports needs to be personalised from each recipient. I have a "call" that writes an audit trail during the process.

HOWEVER, the main loop does not stop and find the end of the file and ends up with an error "No current record". I tried checking for it but that doesn't work. (ps the form is not filtered)

Can anyone help, as its doing my head in

Code:
    Set myrec1 = Forms!frmReportsInternalSummary.Recordset
    With myrec1 
        myrec1.MoveFirst
        Do Until myrec1.EOF

ACTION etc

                    Set myrec2 = Forms.Item(RecordBasedEmailForm).Recordset
                    With myrec2
                        myrec2.MoveFirst
                        Do Until myrec2.EOF

                            ACTION ETC

                        myrec2.MoveNext
                        Loop
                    End With
            If myrec1.EOF = False Then
                                GBL_Process = myrec1![ID] & " - " & myrec1![Subject]
                                GBL_Action = "A55 - MOVE next" ' & myrec1.CurrentRecord
                                If AuditTrail = "Yes" Then Call Audit_Trail_Globals                                    'xxxxxxxxxxxx Audit Trail A60
                myrec1.MoveNext
            Else
                                GBL_Process = myrec1![ID] & " - " & myrec1![Subject]
                                GBL_Action = "A57 - Dont MOVE"
                                If AuditTrail = "Yes" Then Call Audit_Trail_Globals                                    ''xxxxxxxxxxxx Audit Audit Trail A60
            End If
        Loop
    End With

__________________
Adrian
Lanason is offline   Reply With Quote
Old 06-12-2019, 12:21 AM   #2
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,428
Thanks: 0
Thanked 568 Times in 564 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Not finding end of the file in loop

I did a test. The Else branch is never entered.

The error is on exactly which line?

If you want to provide db for analysis, follow instructions at bottom of my post.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 06-12-2019, 12:22 AM   #3
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,435
Thanks: 166
Thanked 1,738 Times in 1,707 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Not finding end of the file in loop

I'm pretty sure this line

myrec1.MoveNext

Should be outside the if myrec1.EOF section. i.e before the Loop command.
As if it is EOF it won't move to the EOF so the loop won't stop.

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 06-12-2019, 12:46 AM   #4
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,428
Thanks: 0
Thanked 568 Times in 564 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Not finding end of the file in loop

The posted code structure works for me. As I said, the ELSE branch is never entered.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 06-12-2019, 01:44 AM   #5
Lanason
Newly Registered User
 
Lanason's Avatar
 
Join Date: Sep 2003
Location: England
Posts: 255
Thanks: 10
Thanked 0 Times in 0 Posts
Lanason is an unknown quantity at this point
Re: Not finding end of the file in loop

the "if else" at the end is me trying to debug it - it doesn't do anything.

so to understand from the beginning it starts on the first record and processes round the loop until the last record. On the last record it should process it and then how does the movenext work as its already on the last record - is this why I have an issue?
__________________
Adrian
Lanason is offline   Reply With Quote
Old 06-12-2019, 01:57 AM   #6
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,397
Thanks: 436
Thanked 805 Times in 780 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Not finding end of the file in loop

I would walk through the code with F8. ?
You MoveFirst and if no records for whatever reason you would get that message.?
Put Debug.Print in the each path of the logic and just run it.?

Also you say Action etc. What is there to say that is not affecting any record movement.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 06-12-2019, 02:29 AM   #7
Lanason
Newly Registered User
 
Lanason's Avatar
 
Join Date: Sep 2003
Location: England
Posts: 255
Thanks: 10
Thanked 0 Times in 0 Posts
Lanason is an unknown quantity at this point
Re: Not finding end of the file in loop

how do I walk through with F8 ?
there are always 31 records to run through
what does Debug.Print do and do I insert in my code?

you could be right about the ACTION etc but don't think so - may I comment all the commands out to prove this...

__________________
Adrian
Lanason is offline   Reply With Quote
Old 06-12-2019, 02:38 AM   #8
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,397
Thanks: 436
Thanked 805 Times in 780 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Not finding end of the file in loop

Quote:
Originally Posted by Lanason View Post
how do I walk through with F8 ?
there are always 31 records to run through
what does Debug.Print do and do I insert in my code?

you could be right about the ACTION etc but don't think so - may I comment all the commands out to prove this...
This can explain it better than I can.

https://www.techonthenet.com/access/...10/debug01.php

Yes comment out that code or put a goto and label in to skip over them.
However I would probably use as is first.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 06-12-2019, 03:16 AM   #9
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,242
Thanks: 94
Thanked 2,026 Times in 1,973 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Not finding end of the file in loop

Lanason,
Links for debugging and error handling concepts and techniques; and many more articles on database planning and design generally.
Good luck with your project.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 06-12-2019, 07:28 AM   #10
smig
Newly Registered User
 
Join Date: Nov 2009
Location: Israel
Posts: 2,030
Thanks: 78
Thanked 134 Times in 125 Posts
smig is on a distinguished road
Re: Not finding end of the file in loop

Just a quick example of code

Code:
Set rsBusiness_BankAcounts = db.OpenRecordset(strSQL)
With rsBusiness_BankAcounts
    If .RecordCount > 0 Then
        .MoveFirst
        Do While Not .EOF
            Array_Business_BankAcounts = Array_Business_BankAcounts & "," & .Fields("BankAcountID")
            Array_Business_BankAcounts = Array_Business_BankAcounts & "," & .Fields("BankAcount")
            Array_Business_BankAcounts = Array_Business_BankAcounts & "," & .Fields("BankID")
            Array_Business_BankAcounts = Array_Business_BankAcounts & "," & .Fields("BankName")
            .MoveNext
        Loop
        .Close
    End If
End With
smig is offline   Reply With Quote
Old 06-12-2019, 10:27 AM   #11
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,153
Thanks: 3
Thanked 470 Times in 463 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Not finding end of the file in loop

A possible explanation of the problem. The recordset is declared outside the procedure and the call to the audit trail procedure advances the recordset pointer so when the recordset eof is reached and the next movenext is encountered, the error occurs.


Post your database if you can't debug the problem.
Cronk is offline   Reply With Quote
Old 06-12-2019, 11:09 AM   #12
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,107
Thanks: 10
Thanked 222 Times in 210 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Not finding end of the file in loop

neither the OP or the code related to "If .RecordCount > 0 Then" reveals what type of recordset is at play.

According to http://allenbrowne.com/ser-29.html, ADO recordsets can return -1 as a count, thus I always use the 1st method in paragraph 3.

__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Reply

Tags
lanason , loop error

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to write On Error code for finding a file? lookingforK Forms 3 09-21-2012 12:23 PM
Finding a file in a directory saj Modules & VBA 2 03-17-2011 04:03 PM
Finding a file and displaying its full path mazza Modules & VBA 6 06-07-2007 12:08 PM
finding newest timestamped file steve_bris Modules & VBA 11 03-23-2005 08:12 PM
File Finding Demo Fizzio General 5 08-06-2002 07:33 AM




All times are GMT -8. The time now is 01:16 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