Invoice in Report, Details section should be auto adjust Paper Size (1 Viewer)

[Jayessh]

Hi,
Thanks in advance!

I am making a Program making and printing invoices.

I need the Details section of the invoice should auto adjust To fit the Paper Size, as sometimes due to fewer data (records) in the result (Invoice) Say 2/3 rows... The invoice seems too tiny and fits all data in half a page... I need a fixed position for whole fields so the Invoice will look nice.... Pls refer ScreenShots

 

[June7]

Might be able to program report to add blank 'filler' rows so the report length remains same. I have done this for simple report. Fairly common topic. Start with https://stackoverflow.com/questions...an-access-report-so-that-it-matches-a-printed and https://www.tek-tips.com/viewthread.cfm?qid=1695691

 

[Gasman]

I am sure @arnelgp did this for another user a while back.
Have a search on this site. I have marked him in this post so he might respond.

 

[Jayessh]

Might be able to program report to add blank 'filler' rows so the report length remains same. I have done this for simple report. Fairly common topic. Start with https://stackoverflow.com/questions...an-access-report-so-that-it-matches-a-printed and https://www.tek-tips.com/viewthread.cfm?qid=1695691

Thank you for your help.
See Brother I am not a Coder, most of the time, I do Access programming through Scratch and some simple code line, but not a full fledge coder. So to understand this trick can you suggest me a youtube Video? so that I can able to perform the trick.

 

[Jayessh]

Thank you for your help.
See Brother I am not a Coder, most of the time, I do Access programming through Scratch and some simple code line, but not a full fledge coder. So to understand this trick can you suggest me a youtube Video? so that I can able to perform the trick.

I understood the trick of using Union Query in this, but my Report is filtered and there is the use of Grouping for the Invoice Number Field, So union Query's blank Rows do not help, as they are also got filtered while running a query/report.

 

[June7]

I provided links to resources I am aware of. If you want to provide your db for analysis, someone might tackle the coding for you. Not sure it could be done with a grouped report. As I said, I did it with a simple report.

 

[KitaYama]

Not sure it could be done with a grouped report

You can do the same with a grouped report.
The concept is the same. First check how many Distinct record exists for the grouped field (against your filter).
Then check how many blank rows you need for each of those distinct values.
Then add necessary union you need for each distinct value in grouped field, but don't forget to fill the grouped field in union query with the same value.

The following adds blank rows to a report grouped by SupplierFk.
First I checked the filter, It returns two distinct SupplierFK for the filter I have.
Then I built the unions and filled supplierFK field with the distinct values I have.

SELECT
    SupplierFK,RequestPK,PartsFK,ReqID,Part
FROM
    tblOrders
WHERE
   Part LIKE '*Test*' AND SupplierFK=30
Union ALL
SELECT
   TOP 2 30,Null,Null,Null,Null
FROM
   tblOrders
UNION ALL
SELECT
   SupplierFK,RequestPK,PartsFK,ReqID,Part
FROM
   tblOrders
WHERE
   Part LIKE '*Test*' AND ReqSupplierFK=139
Union ALL
SELECT
   TOP 8 139,Null,Null,Null,Null
FROM
   tblOrders

I have a function for this purpose. I pass the following parameters and it create the whole sql for grouped query with blank rows.
TableName, Filter, FieldList, GroupedField, LinePerPage

Searching AWF brings up different solutions for adding blank rows to grouped reports.
One of them:

Solved - Adding blank rows to a report

I know that I can use a union query to add blank rows to a report to fill a printed a page. This method works as far as there is no grouping and sorting in query. As soon as I add a group to the report, the blank lines are printed on a separate page. Obviously, because the rows that union query...

www.access-programmers.co.uk

 

[KitaYama]

Just a note:
I don't know how many characters can be used for the sql of a report.
If the result of your filter is too much and building the recordset of the report with unions exceeds the limit, you can use a temp table and add the necessary blank rows to the temp able, then union your main table with the temp table.

 

[Jayessh]

Just a note:
I don't know how many characters can be used for the sql of a report.
If the result of your filter is too much and building the recordset of the report with unions exceeds the limit, you can use a temp table and add the necessary blank rows to the temp able, then union your main table with the temp table.

Thank you so much for your sugession

 

[Jayessh]

You can do the same with a grouped report.
The concept is the same. First check how many Distinct record exists for the grouped field (against your filter).
Then check how many blank rows you need for each of those distinct values.
Then add necessary union you need for each distinct value in grouped field, but don't forget to fill the grouped field in union query with the same value.

The following adds blank rows to a report grouped by SupplierFk.
First I checked the filter, It returns two distinct SupplierFK for the filter I have.
Then I built the unions and filled supplierFK field with the distinct values I have.

SELECT
    SupplierFK,RequestPK,PartsFK,ReqID,Part
FROM
    tblOrders
WHERE
   Part LIKE '*Test*' AND SupplierFK=30
Union ALL
SELECT
   TOP 2 30,Null,Null,Null,Null
FROM
   tblOrders
UNION ALL
SELECT
   SupplierFK,RequestPK,PartsFK,ReqID,Part
FROM
   tblOrders
WHERE
   Part LIKE '*Test*' AND ReqSupplierFK=139
Union ALL
SELECT
   TOP 8 139,Null,Null,Null,Null
FROM
   tblOrders

I have a function for this purpose. I pass the following parameters and it create the whole sql for grouped query with blank rows.
TableName, Filter, FieldList, GroupedField, LinePerPage

I know you can do it better than me, But if needed I can share the function.

Searching AWF brings up different solutions for adding blank rows to grouped reports.
One of them:

Solved - Adding blank rows to a report

I know that I can use a union query to add blank rows to a report to fill a printed a page. This method works as far as there is no grouping and sorting in query. As soon as I add a group to the report, the blank lines are printed on a separate page. Obviously, because the rows that union query...

www.access-programmers.co.uk

Thank you, Brother, I actually figure out it but forget to add "TOP ##" to SQL and hence it is was again not fixed till morning... Thank You once again