Create custom report with VBA

[ino_mart]

All

I need to create an XLS-sheet based upon records in an Access-table. The XLS-should have reporttitles for each new group (and some other custom design such as font,....). I start from Access where I created a button.

I am however stuck to export the records without the error "No current record". With my current code, this error message is normal as I want to assign a value to a variable from the EOF-recordset.

I hereby sent a part of the code which I have reduced/simplified in order to output the records to the debug-screen.

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

The code outputs the table as expected, but I want to get rid of the error "No current record" in a proper programming way. I think I place the reassignments of the Orig-variables on a wrong location.

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

 

[ino_mart]

I found a solution. Seems I had to use IF-functions instead of Do While

Private Sub Button1_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