error while Adding blank rows to a report (2 Viewers)

[Murad]

I want to modify the sorting in the attached report.
When grouping is applied, empty rows appear at the top.
I would like this to be adjusted so they display correctly.

Thanks

 

[Gasman]

I can't get past

when opening report?

 

[mike60smart]

I want to modify the sorting in the attached report.
When grouping is applied, empty rows appear at the top.
I would like this to be adjusted so they display correctly.

Thanks

Why do you need the empty lines?

 

[GPGeorge]

I want to modify the sorting in the attached report.
When grouping is applied, empty rows appear at the top.
I would like this to be adjusted so they display correctly.

Thanks

Help us understand the request by specifying the sort order you want to apply.

 

[GPGeorge]

Why do you need the empty lines?

I think the goal is to create 26 lines on the report per group, regardless of how many records are in each group.

 

[mike60smart]

I want to modify the sorting in the attached report.
When grouping is applied, empty rows appear at the top.
I would like this to be adjusted so they display correctly.

Thanks

I Imported the database into a new database and then applied Descending Order

 

[mike60smart]

I think the goal is to create 26 lines on the report per group, regardless of how many records are in each group.

Hi George
Ithink that is beyond my knowledge, but would appear pointless to me.

 

[GPGeorge]

Hi George
Ithink that is beyond my knowledge, but would appear pointless to me.

I'm partly guessing based on the use of the const in the VBA and the presence of the talley table. I've been wrong before, though.

 

[Gasman]

I think the goal is to create 26 lines on the report per group, regardless of how many records are in each group.

My thoughts as well.
26 lines, regardless of how many real records.
Thought wouldn't that have to be done for each page?
O/P's example is just for 11 records. What if there are 27 records?

 

[DHookom]

A couple issues.
1) your record source is a table name with no field names. Change the record source to qselYourTable with a SQL view of

SELECT YourTable.ID, YourTable.OrderNumber, YourTable.StopNumber, YourTable.CustomerName, YourTable.PONumber, YourTable.ItemNumber, YourTable.ModelNumber
FROM YourTable;

2) change the sorting and grouping to an expression using: =Nz([StopNumber],99999)

 

[Gasman]

your record source is a table name with no field names.

I have that all the time.
In the Open the O/P sets it to sql = "SELECT * FROM " and appends the table name, which is the recordsource to start with. ?

 

[DHookom]

I don't believe you can create a union query with SQL like:

SELECT *
FROM Yourtable
UNION
SELECT A, B, C, D
FROM Yourtable

EDIT: I tried and this actually worked

SELECT yourTable.*
FROM yourTable
UNION
SELECT Null, Null, Null, Null, Null, Null,Null
FROM YourTable;

I also changed the name of the report to rptYourTable since duplicate names of an object in a database is never good practice.

The attached works for me and I believe matches the OP's request.

 

[Gasman]

That does give 26 records, blank or otherwise.
Now all the O/P needs to do is correct the logic for > MAX_LINES

 

[arnelgp]

you may also check this one.
i added another dummy table (dummy2).
using union query will fail if you have lot of stopNumber on your table and
the Union query will increase the the sql string and eventually will fail.

 

[Murad]

Thanks everyone for the replies and for taking the time to look into this.
I appreciate all the input.
**

This is an Access report and grouping IS required.

Records are grouped normally and print in order.
What I need is a fixed page layout where each page always contains exactly 20 detail rows.

The behavior should be page-based, not group-based:
- Groups should flow naturally across pages.
- If a page ends with fewer than 20 records (for example, 15), the remaining rows on that same page should be blank.
- If the records (across groups) exceed 20, the report continues onto the next page and the same rule applies.
- Blank rows should never appear at the top of a page.
- Blank rows should never appear between records or between groups.
- Blank rows are only used to pad the bottom of each page to reach 20 rows.

The goal is consistent printing, not padding per group.

 

[CJ_London]

Why not use the force new page property?

Section.ForceNewPage property (Access)

Access VBA reference

learn.microsoft.com

 

[Gasman]

Blank rows are only used to pad the bottom of each page to reach 20 rows

So why is Max_Lines 26?

 

[Ken Sheridan]

The behavior should be page-based, not group-based:
- Groups should flow naturally across pages.
- If a page ends with fewer than 20 records (for example, 15), the remaining rows on that same page should be blank.
- If the records (across groups) exceed 20, the report continues onto the next page and the same rule applies.
- Blank rows should never appear at the top of a page.
- Blank rows should never appear between records or between groups.
- Blank rows are only used to pad the bottom of each page to reach 20 rows.

The goal is consistent printing, not padding per group.

You don't need to return any blank rows. You can simply force a page break in the detail section after each 20th row. Firstly add a page break control to the bottom of the Detail section. Next declare a module level variable in the report's module's Declarations area. The following is from one of my own reports.

Dim intCounter As Integer

In the group header's Format event procedure initialise the variable:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

    If FormatCount = 1 Then
        intCounter = 1
    End If
   
End Sub

In the detail section's Format event procedure increment the variable, and set the page break control's Visible property to True if the required number of rows is reached:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    If FormatCount = 1 Then
        intCounter = intCounter + 1
   
        If intCounter > 20 Then
            intCounter = 0
            Me.PageBreak.Visible = True
        Else
            Me.PageBreak.Visible = False
        End If
    End If
   
End Sub

Force a page break after the last row in the group by setting the group footer's ForceNewPage property to 'After Section'.

 

[CJ_London]

Still don’t need the counter, just the group footer's ForceNewPage property as suggested a number of posts back - if that creates more than the 20 or 26 rows required, just increase the height of the page footer

 

[Ken Sheridan]

Still don’t need the counter, just the group footer's ForceNewPage property as suggested a number of posts back - if that creates more than the 20 or 26 rows required, just increase the height of the page footer

But what if some rows wrap to two or more lines?