Solved Adding blank rows to Grouped Report (1 Viewer)

peerogel

New member
Local time
Yesterday, 20:11
Joined
Feb 14, 2020
Messages
10
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.

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

New member
Local time
Yesterday, 20:11
Joined
Feb 14, 2020
Messages
10
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.
 

Attachments

  • Database14.zip
    1.8 MB · Views: 63

peerogel

New member
Local time
Yesterday, 20:11
Joined
Feb 14, 2020
Messages
10
Hello, I found another sample but now im getting just the "blanks" but not the stored data. o_O

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

New member
Local time
Yesterday, 20:11
Joined
Feb 14, 2020
Messages
10
😫😩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

New member
Local time
Yesterday, 20:11
Joined
Feb 14, 2020
Messages
10
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

Enthusiastic Amateur
Local time
Today, 04:11
Joined
Sep 21, 2011
Messages
14,607
😫😩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

bit cruncher
Local time
Yesterday, 20:11
Joined
Mar 17, 2004
Messages
8,199
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

New member
Local time
Yesterday, 20:11
Joined
Feb 14, 2020
Messages
10
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

New member
Local time
Yesterday, 20:11
Joined
Feb 14, 2020
Messages
10
This mess is what i got so far.
 

Attachments

  • Database15.zip
    365.1 KB · Views: 72

MarkK

bit cruncher
Local time
Yesterday, 20:11
Joined
Mar 17, 2004
Messages
8,199
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

New member
Local time
Yesterday, 20:11
Joined
Feb 14, 2020
Messages
10
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).
 

Attachments

  • Database16.zip
    364.4 KB · Views: 60
  • Database16-error.zip
    391.8 KB · Views: 57
  • Error database.JPG
    Error database.JPG
    19 KB · Views: 52

MarkK

bit cruncher
Local time
Yesterday, 20:11
Joined
Mar 17, 2004
Messages
8,199
Do you know how to set a breakpoint, and step thru code line by line while it executes?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:11
Joined
May 7, 2009
Messages
19,249
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.
 

Attachments

  • Database16.accdb
    1,020 KB · Views: 64

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:11
Joined
May 7, 2009
Messages
19,249
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).
 

Attachments

  • Database16.accdb
    1 MB · Views: 78

Users who are viewing this thread

Top Bottom