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....Hopefully 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.

[zmbd]

Rabbit:
I thought I try that... using a very simple select query against one field in my test db, I'm getting that same error if you even do something like
SELECT TOP (14 - 1)....
Appears the engine does not like calculated values.


<EDIT>
ALL, DISTINCT, DISTINCTROW, TOP Predicates

The value that follows TOP must be an unsigned Integer.

</EDIT>

[Rabbit]

I've used it in SQL Server but not Access, if you can't use top, move it the WHERE clause on a field that numbers the records from 1 to 15.

[NeoPa]

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.

That's because my interpretation of your suggestion omitted that from the SQL (OOOoooops)! My bad there.

SELECT TOP (formula)

This doesn't work in Jet SQL! Well, that certainly sucks big time.

How about this :

SELECT Null AS [IDField]
     , *
FROM   [tblEvidence]
UNION ALL
SELECT [IDField]
FROM   [tblBlank]
WHERE  ([IDField] Between ((((SELECT Count(*)
                             FROM   [tblEvidence]) - 1) Mod 15) + 2) And 15)

[tblBlank] needs records with just the [IDField] set and numbered from 1 to 15 (at least).

[frankiejj98]

I am trying but still am not having any luck. Maybe I we can find a manual solution? I tried to add the blank records manually and it did work...however, it is printing the blank records first and then the actual records with data. If I could figure out a way to sort it the opposite way I could make this work manually...Any ideas?

[Rabbit]

You need to describe what's not working. What did you try? What code did you use? What went wrong? Did you get error messages?

Why go manual when you can just set up an automated process? Just because you failed to set it up correctly a few times shouldn't discourage you from doing it the more efficient way.

[NeoPa]

Have you tried including the [IDField] field as the first field for sorting by in your report? If Nulls appear in the wrong position you can easily change the SQL to return a zero (0) instead for the records returned by [tblEvidence].

[frankiejj98]

Thanks Rabbit,
The error that is returned is 'The select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.'
Here is the code that I am using:

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

Thanks again!

[Rabbit]

1) You have SELECT *,*. There's no need to select all the columns twice. That's certainly going to cause conflict as there will be two of every column with the same name.

2) You need to reread the posts because we determined that you can not use an expression in the TOP predicate and proposed an alternate solution. (Refer to NeoPa's post, #18). It's not quite right, but it gets you most of the way there, he forgot to select the rest of the columns in the blank table.

[frankiejj98]

Okay, I followed the instructions from post# 18. I ran the query and get the following error msg: 'The number of columns in the two selected tables or queries of a union query do not match'. So just in case, I copied my tblEvidence and saved it as tblBlank. I already had the IDField created in tblEvidence. I'm not sure what else to do. I am really frustrated and I hope that it is not too frustrating for you...I am clearly over my head with this stuff. I understand if you do not want to continue with this thread.
Thanks,
Frank

[zmbd]

That is a fluke of MS Access UNION queries.
The number of fields in each has to match:
SO lets back up a tad and use an example (forgive an old Chemist and TA, it's the only way I can make sense of my thoughts - I have to get them on "paper" sometimes :-), wait till you get old - it'll happen to you too (chuckle) )

OK
tbl_data
[data_pk] autonumber
[data_field1] (doesn't matter, just example)
[data_field2] (doesn't matter, just example)
[data_field3] (doesn't matter, just example)

tbl_blank
[blank_pk] number(long) (in this case records with values 1 thru 15)
[blank_field1] text(1) (in this case leave null, or enter a " " space)

Now, lets use the information provided in post #18 tweaked for this generic example - and I'm going to use hard coded fields for the example:

SELECT [data_pk],
   [data_field1],
   [data_field2] 
FROM   [tbl_data] 
UNION ALL 
   SELECT [blank_field1] as F1,
      [blank_field1] as F2,
      [blank_field1] as F3
   FROM   [tbl_Blank] 
   WHERE  ([blank_pk] Between
      ((((SELECT Count(*)
          FROM [tbl_data]) - 1) Mod 15) + 2) And 15)

(please note, this SQL has been vetted against my test DB using the tables and records as indicated for MS Access 2010: [data_pk]=autnumber; [data_field1]=date; [data_field2]=text(50); [blank_field1]=text(1) are all null/empty value; [blank_pk]=number(long))

The main thing to notice here is that the number of fields in each "SELECT" portion are the same.
[edit - toddler playing with enter key :) - sigh]
So if you use the code from #18 as is, then you need to make sure that within the second "SELECT" following the "UNION ALL" has the same number of fields as the first one does in order for the query to work.

[NeoPa]

Rabbit:
It's not quite right, but it gets you most of the way there, he forgot to select the rest of the columns in the blank table.

Not so much forgot, as misinterpreted your comment from post #13. i read that as meaning the number of fields didn't need to match. Now I reread it I see that wasn't what you said.

@FrankieJJ
Try the following, but include Null entries for all the fields in the second query that occur in the first (IE. [tblEvidence]).

SELECT Null AS [IDField]
     , *
FROM   [tblEvidence]
UNION ALL
SELECT [IDField]
     , Null
     , Null
     , ... etc
FROM   [tblBlank]
WHERE  ([IDField] Between ((((SELECT Count(*)
                             FROM   [tblEvidence]) - 1) Mod 15) + 2) And 15)