Problem with EOF in custom report

**ino_mart** · 09-27-2011, 12:11 PM

All

I have to export an Access table to an Excel-file. The XLS would be some kind of report with a page title, subtitles, subfooters, footers.

I am currently in debugging state as I can't solve the "No current record" error.

A very basic example of my code is below. I do know why the error happens, but I need a proper solution how to avoid the error message.

Code:

Private Sub Command0_Click()
Dim OrigPostalCode As Integer
Dim OrigFullName As String
Dim OrigStreet As String
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("select * from tblAdres order by postalcode, street, fullname")
Debug.Print "Overview of adres"
rs.MoveFirst
OrigPostalCode = rs!postalcode
Do While Not rs.EOF And rs!postalcode = OrigPostalCode
    Debug.Print vbTab & "The postal code is " & rs!postalcode
    OrigStreet = rs!street
    Do While Not rs.EOF And rs!postalcode = OrigPostalCode And rs!street = OrigStreet
        Debug.Print vbTab & vbTab & "A street found for this postal code is: " & rs!street
        OrigFullName = rs!FullName
 
        Do While Not rs.EOF And rs!postalcode = OrigPostalCode And rs!street = OrigStreet And rs!FullName = OrigFullName
            Debug.Print vbTab & vbTab & vbTab & "A person living in this street is: " & rs!FullName
            rs.MoveNext
            OrigFullName = rs!FullName
        Loop
        OrigStreet = rs!street
    Loop
    OrigPostalCode = rs!postalcode
Loop
End Sub

I do get my report, but I do not find a proper solution for the error message.

Code:

Overview of adres
    The postal code is 1000
        A street found for this postal code is: Shrekstreet
            A person living in this street is: Mike Meyers
        A street found for this postal code is: Suite 750
            A person living in this street is: Lynda Blair
    The postal code is 2000
        A street found for this postal code is: Boulevard
            A person living in this street is: Joe Smith
            A person living in this street is: John X
        A street found for this postal code is: Saint Victor
            A person living in this street is: Julie Andrews

**pbaldy** · 09-27-2011, 12:21 PM

The fly in the ointment is that you're looping the same recordset more than once. When you loop it the second time (your inner loops), it get's left at EOF, so a line like this will error:

OrigStreet = rs!street

Since you have an additional test along with the EOF test, I'm not sure where it's happening, but my suspicion is that one of those loops is leaving the recordset at EOF which is followed by a line like the above. You can set a breakpoint and step through the code, which should make it easier to follow what's happening.

**ino_mart** · 09-27-2011, 01:23 PM

Paul

I already found one error. Next lines needs be switched (otherwise, I do not output the last record)

Code:

rs.MoveNext 
OrigFullName = rs!FullName

However, it now fails on the While-condition

Code:

Do While Not rs.EOF And rs!postalcode = OrigPostalCode And rs!street = OrigStreet And rs!FullName = OrigFullName

It is actually a same situation where I am checking a variable with a recordfield where the recordset is EOF.

The code does print all the records in the table. It is just a problem when de recordset is EOF. I had hoped VBA would pass the While-condition as soon it detected the rs was EOF.

I also tried to put the second part in another DO While-loop, but then my report misses the titles or got in a infinite loop.

I can't use error handling as there will be more subtitles, footers, indication of amount of records per group, ...

Regards
Ino

**ino_mart** · 09-27-2011, 02:07 PM

I found a solution. Seems I had not to work with "Do While", but with IF-functions

Code:

Private Sub Knop1_Click()


Dim reportmessage As String
Dim street As String
Dim postalcode As String
Dim rs As Recordset

    Set rs = CurrentDb.OpenRecordset("select * from tblAdres order by postalcode, street, fullname ")
    
    rs.MoveFirst
    
    Debug.Print "Overview of adres"
    postalcode = ""
    street = ""
    FullName = ""
    Do While Not rs.EOF
        If postalcode <> rs!postalcode Then
            postalcode = rs!postalcode
            reportmessage = vbTab & _
                            "The postal code is " & postalcode
            Debug.Print reportmessage
        End If
        
        If street <> rs!street Then
        
            street = rs!street
            reportmessage = vbTab & vbTab & _
                            "A street found for this postalcode is: " & street
            Debug.Print reportmessage
        
        End If
        
        If FullName <> rs!FullName Then
            FullName = rs!FullName
        reportmessage = vbTab & vbTab & vbTab & _
                        "A person living in this street is: " & rs!FullName
                        Debug.Print reportmessage
        End If

        rs.MoveNext
        
    Loop
    
    Set rs = Nothing
End Sub