Solved Adding blank rows to Grouped Report

[peerogel]

Hello AccessWorld.
I found a post by arnelgp (AddBlankLinesInReport.accdb) that helped me add blank rows to complete a report. I was able to use the report when printing a single person's report. However, now I am now trying to use the same code but in a grouped report. I've tried but have failed miserably. Is it possible to apply the same code to a group report?

This is the code that arnelgp created.

    Dim iFill As Integer
    Dim total_records As Long
    Dim sql As String
   
    With CurrentDb.OpenRecordset(Me.RecordSource, dbOpenSnapshot)
        If Not (.BOF And .EOF) Then
            .MoveLast
            .MoveFirst
        End If
        total_records = .RecordCount
    End With
    If total_records < MAX_LINES Then
        iFill = MAX_LINES - total_records
       
    ElseIf total_records > MAX_LINES Then
        iFill = total_records Mod MAX_LINES
       
    End If
    sql = Trim$(Replace$(Me.RecordSource, ";", ""))
    If InStr(1, sql, "SELECT") <> 1 Then
        sql = "SELECT * FROM " & sql
    End If
    If iFill <> 0 Then
        sql = sql & " UNION " & fnCreateDummyLineQuery(iFill, "OrderNumber", "StopNumber", "CustomerName", "PONumber", "ItemNumber", "ModelNumber")
        Me.RecordSource = sql
    End If

I thought that maybe I would be able to combine it with this code that numbers pages in grouped reports but i get an error that i cant modify the report once created. I'm lost..

Dim i As Integer
Dim dbs As Database

    Set dbs = CurrentDb

  If Me.Pages = 0 Then
    ReDim Preserve GrpArrayPage(Me.Page + 1)
    ReDim Preserve GrpArrayPages(Me.Page + 1)
    GrpNameCurrent = HashID
   
   
    'Debug.Print GrpNameCurrent
   
   
    If GrpNameCurrent = GrpNamePrevious Then
        GrpArrayPage(Me.Page) = GrpArrayPage(Me.Page - 1) + 1
        GrpPages = GrpArrayPage(Me.Page)
            For i = Me.Page - ((GrpPages) - 1) To Me.Page
                GrpArrayPages(i) = GrpPages
            Next i
    Else
        GrpPage = 1
        GrpArrayPage(Me.Page) = GrpPage
        GrpArrayPages(Me.Page) = GrpPage
       
       
       
    End If
           
  Else

    ctlGrpPages = "Page " & GrpArrayPage(Me.Page) & " of " & GrpArrayPages(Me.Page)
   
  End If
  GrpNamePrevious = GrpNameCurrent

 

[peerogel]

Attached is a sample of the form I'm trying to add blank lines to. Report ranges between 1 to 4 pages, with approximately 400 users (different groups). I found a couple more samples from arnelgp but all i can manage to do is add blank lines on an empty report. Thanks for your time.

 

[peerogel]

Hello, I found another sample but now im getting just the "blanks" but not the stored data.

Option Compare Database
Option Explicit

Dim lngI As Long
Dim lngForeColor As Long

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    lngI = lngI + 1
    If Me!HashID Like "z@*" Then
        'Me!HashID.ForeColor = IIf(lngI Mod 2, Me.Detail.BackColor, Me.Detail.AlternateBackColor)
    Else
        'Me!HashID.ForeColor = lngForeColor
    End If
End Sub

Private Sub Report_Open(Cancel As Integer)

    'see normalReport the total line per page is 27
    Const report_line As Integer = 12
    
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim lngCount As Long
    Dim strSQL As String
    Dim intPad As Integer
    Dim lngGroup As Long
    lngForeColor = Me.HashID.ForeColor
    strSQL = "select HashID, count('1') as Expr1 from qry_Inventory group by HashID order by HashID;"
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
    strSQL = vbNullString
    With rst
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            lngGroup = !expr1
            intPad = lngGroup Mod 12
            strSQL = strSQL & _
            "select ID, Inventory, Description, Serial, Category, Make, Model, HashID, FullName, IssuedDate, ReturnedDate, ExpDate from qry_Inventory where HashID = " & !HashID


            
            If intPad > 0 Then
                intPad = report_line - intPad
                strSQL = strSQL & _
                " union all " & _
                "select top " & intPad & " " & _
                !HashID & ",'z@', null, null, null, null, null, null, null, null, null, null from qry_Inventory"
            End If
            .MoveNext
            If Not .EOF Then
                strSQL = strSQL & " union all "
            End If
        Loop
        .Close
    End With
    Set rst = Nothing

 

[peerogel]

Getting Closer. I figured out that my issue is with the HashID. My HashID is short text and the sample i found has a Group ID that is numeric.

 

[peerogel]

I'm cant win... I modified the table and form to accommodate an extra column a numeric Group. I got it working on my test database but now when i transfer the new report/vba i get the following error. "Run-time error '2176': The setting for this property is too long." I'm guessing that the new sql is to long for the recordsource.

 

[Gasman]

Getting Closer. I figured out that my issue is with the HashID. My HashID is short text and the sample i found has a Group ID that is numeric.

Just suround your HashID with singĺe quotes if it is text.

 

[MarkK]

I downloaded your database, and Report_Open() is missing function fnCreateDummyLineQuery(). If I paste in the Report_Open() code you posted on this thread, it is missing qry_Inventory. If you provide code where we can replicate your result, or where the failure is the same as what you cite, then I expect you will get more useful assistance.

 

[peerogel]

Sorry, that was for the report that was not grouped. After I posted the database i was using another vba. Ill update another database with what i got so far.

 

[peerogel]

This mess is what i got so far.

 

[MarkK]

And what is the problem with this database, for instance, I am not getting error 2176. What should I be looking at that needs to be fixed, or that isn't working?

 

[peerogel]

I attached an updated database (database16) and it works as intended with the records it has. Once I add more groups i get that error database16-error).

 

[MarkK]

Do you know how to set a breakpoint, and step thru code line by line while it executes?

 

[arnelgp]

the code will not work if you have many Groups (petsID).
since there is a limit on how long your SQL query can be.

i introduced temp tables (tbl_dummyInventory and tbl_T1).
at the end of the code (Open event) of the report, tbl_T1 is set as
it's Recordsource.

If you don't want to bloat your db, you can move tbl_T1 to a
separated db and create a Linked table for it.

 

[peerogel]

Thank you so much. I've been trying for days.

 

[arnelgp]

there is a little problem with the last db i posted.
it does not show the correct number of pages for each group.
it is rectified on the attached db (see page number of the report).