How to include blank records in reports

[saudkazia]

Hi All,

I just joined so please be lenient with me if my question is not worded correctly.

I have been stuck with a problem for a few days and despite best efforts at googling and trying out different methods I am still stuck.

Basically I am using Northwind Database from Microsoft as a "template" that I have modified to my requirements with the little knowledge of Access I have and the support of Google .



My problem is that when printing the invoice I need to include blank rows to fill up the report so all invoices have uniform space. I am trying union queries as well as vba on print event but nothing seems to be working.

on the union query I am able to see blank rows but not tied to the current report [order id]. So I see this (simplified)

here is the union query
select id, [Order ID], [Customer ID], [Customer Name], location_id, [Order Date], [Shipped Date], [Product ID], id, qry_sales_subform.Quantity, qry_sales_subform.Margin, [Product Name], qry_sales_subform.[Sub Total], qry_sales_subform.VAT, qry_sales_subform.Total, qry_sales_subform.[Unit Price],Price, phone, Place, tin
from [invoice data]

UNION ALL
SELECT [id],null,null,null,null,null,null,null,null,null,null ,null,null,null,null,null,null,null,null,null
FROM [invoice blank data]
WHERE ([id] Between
((((SELECT Count(*)
FROM [invoice data]) - 1) Mod 15) + 2) And 15)

Exp1000 | Order ID | Other fields
117 | 730 | *
117 | 731 | *
91 | 731 | *
89 | 731 | *
8 | [blank] | *
9 | [blank] | *
10 | [blank] | *

what i need in this case (to fix the union query) is so that all extra added rows need to have the order ID currently used in the report which is filtered through the order details form after clicking on create invoice.

Can someone help me on this either fixing the union query or another strategy to include the blank rows based on current data.

Another thing about this is that my page is in landscape mode and is going to be printed twice on one a4 portrait sheet (customer invoice) (company invoice)

[ranman256]

you need to add a constant, this field will exist no matter what data you query. Like todays date, or the report name.

[saudkazia]

Ranman. If I do that how will the report filter the order ID after the union query

[ranman256]

if you have zero records , it wont matter, the constant is in ALL queries on the union.

[saudkazia]

Let me clarify

If one record of order details based on order I'd then 9 blank records based on order I'd

If 2 then 8
If 3 then 7 and so on

If 11 then 10 on one page and 1 with 9 blank on next page

[Micron]

There is a zip file you can download that might do what you want - I only took a quick look at it. Take a look at the report property sheet in design view and you'll see a couple of function calls for the group header and detail section. BTW, I believe the only view suitable for what you want is print preview so you need to be OK with that.

http://www.access-programmers.co.uk/...d.php?t=183782

[saudkazia]

There is a zip file you can download that might do what you want - I only took a quick look at it. Take a look at the report property sheet in design view and you'll see a couple of function calls for the group header and detail section. BTW, I believe the only view suitable for what you want is print preview so you need to be OK with that.

http://www.access-programmers.co.uk/...d.php?t=183782

Unfortunately that is a Access 97 Database. Im on Access 2013

[saudkazia]

I installed 2007 and converted the database but it doesn't help me do what I want. There is no filter on the report. When I change filter to supplierid=1 (expecting 3 products with supplier id being 1 and rest blank) it shows 2 products on first page with blank lines and another page with one product with rest blank lines....Also the lines are NOT professional as borders are on the cells. Any way to emulate the borders and to filter correctly.

[saudkazia]

I gave up on the Union Query as I couldn't get it to logically union.

So as suggested tried the print method which is now getting me almost to what I want.

Found this -
Dim intRows As Integer
Dim intLoop As Integer
Dim intTopMargin As Integer
Dim intDetailHeight As Integer
intRows = 9
intDetailHeight = Me.Section(0).Height
intTopMargin = 3100
Me.FontName = calibri


Me.FontSize = 11
For intLoop = 0 To intRows
Me.CurrentX = 150
Me.CurrentY = intLoop * intDetailHeight + intTopMargin
Me.Print intLoop + 1
Me.Line (0, intLoop * intDetailHeight + intTopMargin)- _
Step(me.width, intDetailHeight), , B
Next


The only issue is how do I print vertical lines between each of the fields

their is also the minor aspect of the generated numbers being too close to the lines any way I can make them in the middle between lines.

If I get these things solved, I will be happy to close this thread.

[Micron]

As mentioned, I didn't study the example and would have no way of knowing whether or not it would be exactly laid out as you required. I figured it would get you started in that at least, it was a solution that seemed to generate a fixed number of report 'rows' - something you weren't getting from anyone else. I'm surprised that you could not simply convert it from 97 with your 2013 version, but maybe that's because I'm stuck in 2007. The conversion worked for me.

Aside from that, try drawing a vertical line between your controls and size the controls so that they provide more white space above and below the text. The line should repeat for each record. I confess that I didn't bother to figure out how the lines were generated in the example, so hopefully these suggestions give you what you need.

[saudkazia]

Micron. The logic of my query and the grouping in the sample database you linked is different.

My report (Northwind Traders database invoice) filters on a criteria and shows details for that filter only.
The database linked shows all records but groups them. Different logic.

[ranman256]

I don't understand what you want.

[saudkazia]

I basically need to include blank rows in my report to fill up space so all invoices have uniform spacing

now there are two ways to do this. A union query (which I have not been successful with) or an on print even that prints the lines.

Then 2nd method I have been very successful with except that the lines in between fields are not being printed by the vba.

Please help me in either the union query or the vba

[saudkazia]

im still stuck in this. and the issue is causing handover delays....

[Micron]

Micron. The logic of my query and the grouping in the sample database you linked is different.

My report (Northwind Traders database invoice) filters on a criteria and shows details for that filter only.
The database linked shows all records but groups them. Different logic.

I don't see how this really matters because the example showed how to code for extra lines in a report. How you've grouped your data should be irrevelant, and your task would be to understand how it was done and adapt it to your use. If I'm wrong about that then I too don't know what you want. You indicated you had some success but want lines in between the controls. I suggested just adding vertical lines but don't know what was wrong with that idea - no feedback.

Then 2nd method I have been very successful with except that the lines in between fields are not being printed by the vba.

Maybe you should upload a stripped down version of what you have and see if anyone can help further. As noted, I cannot open versions later than 2007.