Not finding end of the file in loop (1 Viewer)

Lanason · Jun 12, 2019

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 :banghead:

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

June7 · Jun 12, 2019

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.

Minty · Jun 12, 2019

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.

June7 · Jun 12, 2019

The posted code structure works for me. As I said, the ELSE branch is never entered.

Lanason · Jun 12, 2019

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?

Gasman · Jun 12, 2019

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.?

Lanason · Jun 12, 2019

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...

Gasman · Jun 12, 2019

Lanason said:
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/tutorials/vbadebug2010/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.?

jdraw · Jun 12, 2019

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.

smig · Jun 12, 2019

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

Cronk · Jun 12, 2019

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.

Micron · Jun 12, 2019

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.

Not finding end of the file in loop (1 Viewer)

Lanason

Registered User.

June7

AWF VIP

Minty

AWF VIP

June7

AWF VIP

Lanason

Registered User.

Gasman

Enthusiastic Amateur

Lanason

Registered User.

Gasman

Enthusiastic Amateur

jdraw

Super Moderator

smig

Registered User.

Cronk

Registered User.

Micron

AWF VIP

Similar threads

Users who are viewing this thread