MS Access: A Hack to Print Blank Rows in a Report

This is a way to insert empty or empty-like rows into a list of “seats” that contains not only reservations, but a number saying how many seats a group of people have. If the number is greater than the number of seats, this adds new blank rows for empty seats.

Sub insertBlankRows()
    Dim dbs As Database, qdf As QueryDef, strSQL As String
    Dim rst As Recordset
    
    Set dbs = CurrentDb
    strSQL = "SELECT tblSeats.OrganizationId, [MaxOfSeats]-Count([OrganizationId]) AS Difference, " & _
            " Count(tblSeats.OrganizationId) AS CountOfOrganizationId, Max(tblSeats.Seats) AS MaxOfSeats " & _
            " FROM tblSeats GROUP BY tblSeats.OrganizationId;"
    Set rst = dbs.OpenRecordset("qryDifferences", dbOpenForwardOnly)
    
    While (Not rst.EOF)
        For i = 1 To rst!Difference
            insSQL = "INSERT into tblSeats (OrganizationID, LastName, FirstName) VALUES (" _
                & rst!OrganizationId & ", '', '')"
            ' MsgBox (insSQL)
            dbs.Execute (insSQL)
        Next
        rst.MoveNext
    Wend

    
End Sub

Attachment	Size
emptyrows.jpg	90.86 KB