Adding empty records

[frankiejj98]

A similar question to mine was asked before with one reply back in 2006. I tried to use the information provided but was unable to make it work.

I created a database to print out a report that is the same as the original hand written form. The problem I'm running into with this report is when the last record prints and there are no more additional lines (i.e. if the document has 15 lines to be filled out, and if I print
from Access with only 10 records entered, 10 lines is all I get.).

I'd like to complete the additional lines using empty records (5 records per the previous example) since there are many times we will need to add information to the report by hand after it has been printed and we will need the lines.

here is what I was attempting to use from a previous post.

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
If Me.MoveCount < intLineCnt Then
Me.MoveLayout = True
Me.PrintSection = False
Me.NextRecord = False
Me.MoveCount = Me.MoveCount + 1
Else
Me.MoveLayout = True
Me.PrintSection = True
Me.NextRecord = True
End If
End Sub

Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer)
Me.MoveCount = 0
intLineCnt = CalcLinesToMove()
End Sub

Any help would be much appreciated.

Thanks!

[Rabbit]

Use a union query to append blank records to the record source. No need to mess with code.

[NeoPa]

If I'm not missing something, they would also need to restrict the number of records returned in the query to 15. This can be done using the TOP predicate of the SELECT clause. This can also be handled in the properties of a QueryDef by setting the Top Values property.

[Rabbit]

Actually, though not stated, I believe the goal is to reach a multiple of 15. In which case, create a table with the numbers 1 through 15 and use a mod to return the number of blank rows you need to union.

[NeoPa]

Rabbit:
I believe the goal is to reach a multiple of 15.

I'm inclined to agree, although the OP was specific in their explanation and that was for 15.

In case it's not clear though (and I had to think hard before I worked out what you might be suggesting), what I think you're suggesting is something like :

SELECT *
FROM   [OPTable]
UNION ALL
SELECT TOP (14 - (((SELECT Count(*)
                    FROM   [OPTable]) - 1) MOD 15))
FROM   [Rec15]

[Rabbit]

Essentially yes. But it would be 15 subtract the mod and only if the mod is not equal to 0.

[NeoPa]

Rabbit:
But it would be 15 subtract the mod and only if the mod is not equal to 0.

And how would that be coded, other than how I suggested, without having to repeat the subquery?

[Rabbit]

Sorry, you were right. I missed the -1.

[frankiejj98]

Thanks for the help but I am just not knowledgeable enough in this to be able to put it to use. Up to this point I have done everything using the wizards and it has been pretty fun... My experience using access is very limited. I like the idea of using queries though since I would not have to use the code, but I do not know how to implement this.
Thank you,
Frank

[zmbd]

frankiejj98:
It's been such a long time since I was there with the Wizards as my friends. :)

OK, a small walk thru as I'd bet you're a tad shy on the SQL
- Create your second table as given in post 4
-Open the query editor.
-Change the view to SQL. -

[V2010] Ribbon, Create, Query, Design
Close the popup dialog without adding tables
You will now have a blank upper field and a grid below.
Easy way to SQL is to Right click in the field, select SQL View in the context menu. You can also select this from the Ribbon, or the bottom right hand corner.[/V2010]

-Copy and paste the SQL in the code box in NeoPa's post #5

Now here's where things will get troublesome if you don't know SQL so I'll do a break down:

SELECT * - This will select all of the fields in the table given in the table OPTable:
FROM [OPTable] You will need to change this to match your data table's name.
UNION ALL this is the magic that will allow you to get the empty records from the second table as given in post #4
SELECT TOP 14 Pull the first 14 records...
- ( less the calculated
((SELECT Count(*) returned count of the records in table OPTable by selecting all of records
FROM [OPTable]) from your data table Make sure you change the name!
- 1) subtract 1 from that number
MOD 15)) and calculate the remainder from 15 then append that number of records:
FROM [Rec15] the second table as given in post #4 and you will have to change this to the table name that you create.

[frankiejj98]

Thank you all very much for your time. I will work on it as soon as I can....Hopefull y this afternoon. I'm sure that I will be in touch.
Thanks again!

[NeoPa]

Z explains that well.

I would just explain that the table names I used were purely for reference, and to mean something in this thread. OPTable just refers to the OP's (your) table. and Rec15 just indicates where you get the blank records that form the balance of the multiple of fifteen.

Before you use this you will need to create and populate this table. I suggest a name something like [tblBlank] in your actual project. It should have all the same fields that will be used in your report, as well as some type of ID field that holds unique values that can be sorted on. Unfortunately the TOP predicate only returns the exact number of items specified when the values of the sorted field(s) are different. This may be a problem with the SQL suggested. Give it a try and let us know the results. If we need to we'll find a way around that for you with some updated SQL.

[Rabbit]

You can include a unique ID to get around that. Just don't put it on the report.

You don't technically have to include the fields you want in the table definition. It can just be a table with one field with the numbers from 1 to 15 and then in the SQL you can define the number of blank strings needed. That way, you can reuse the table.

[frankiejj98]

Okay I gave it a shot and created a tblBlank with the same fields that are used on my report. My original table is tblEvidence. I used the SQL provided and changed the names to my particular tables but I get an error that says 'The SELECT statement includes a reserved word or argument name that is misspelled or missing, or the punctuation is incorrect.' I checked the spelling but do not see anything.

SELECT  *
FROM     [tblEvidence]
UNION ALL SELECT TOP (14 - (((SELECT Count(*)
                    FROM   [tblEvidence]) - 1) MOD 15))
FROM   [tblBlank];

[Rabbit]

You didn't specify which fields to select in your bottom query and so it thinks you're trying to select a field named from.