Generate Fixed Number of Entries

[dustonabt12]

I need to create a report to electronically fill a form that I use at work. I'm having a heck of a time trying to figure out the best way to do it. It also doesn't help that I'm new at access. The form looks like this:


My report right now looks like this:


The idea was to have my query generate however many records I currently have for the details section (usually 2-5) and then somehow generate the remaining records as blanks to fill in the form. I needed exactly 14 total records to make the form whole when I print it out. Long story short, I am falling flat on my face. I can not figure out how to somehow force 14 results for each query.



Issue number two is that I also cannot figure out why everything fits perfectly when my footer image is in a report footer but then when I put the same thing in a page footer there is always a small gap between the details and page footer sections. I need it in a page footer so that when my query returns 4 names (groups) and 5 weapons/name (5 records/group) I can force a new page and have one form per name (group).

Does anyone have some suggestions or solutions?

[June7]

Not easy to do.

Review:

http://www.tek-tips.com/viewthread.cfm?qid=1695691
http://bytes.com/topic/access/answer...-lines-reports
https://www.accessforums.net/reports...rts-35879.html

[Dal Jeanis]

I'm trying to make sure I understand your purpose.

(1) Are the empty rows to be used to fill in for additional lines of data? If so, what is the significance of how many rows show on the screen? the default behavior for Access is to show one empty row, which is enough to allow the user to enter the next line.

(2) Are the empty rows used to make sure a printed form looks exactly like your current form, Army Hand Receipt DA 2062? If so, then it's possible to make that happen. As a report, the result would not be updateable, though.

ONE INELEGANT BUT EASY METHOD FOR REPORTS

(3) One quick method, off the top of my head, to make the report version, is to add a subreport to the report footer.

(3A) On the footer, make sure there is a non-visible field txtCount that calculates the number of lines that were used.

(3B) Make the detail line of the subreport look exactly like the detail line of the real report, but make all the text boxes unbound.

(3C) Then, you just have to make the record source for the subreport ANY QUERY that gives you the exact number of records that you need. Since the subreport has no fields bound to the fields in your query, it will print a blank line for each.

For this example, I created a 12-record table called tblDummylines. Each record has one field, and the records contain the numbers 1 thru 12.

tblDummyLines
   LineNo   number

The record Source for the subreport is as follows:

SELECT 1 FROM tblDummyLines WHERE [tblDummyLines].[LineNo] > [txtCount];

If the txtCount textbox has calculated that there were 5 lines, then blank lines will be created in the subreport for the seven records 6 thru 12 in tblDummylines.

NOTES FOR REFERENCE

(4) Since the subreport doesn't have anything bound to the query return values, I'm returning the constant "1".

(5) I tried using the TOP(N) keyword in the record source of the subreport, and Access complained severely, so the tblDummyLines workaround was the simplest method.

(6) If you had several different reports that wanted to use this method, with different total numbers of lines, then it might be preferable to have the footer calculate the desired number into [txtLinesNeeded] rather than the used number into [txtCount], and reverse the sign of the WHERE clause to <= [txtLinesNeeded].

That way your tblDummyLines could have any arbitrary number (like 99) of records in it and be a solution used for more than one type of report.

[June7]

I managed to get the code in Tek-Tips thread to work. An issue is if the report is opened first in PrintPreview then sent to printer, the code for the blank lines doesn't work properly. Have to send direct to printer.

[dustonabt12]

Thank you for the replies. A little beyond my understanding but I'll have my buddy look at it who is more experienced. I'm sure we can come up with a solution using those suggestions.

[dustonabt12]

Thank you very much Dal for giving good directions. It took a day or two of playing around but I finally got it working. It was exactly what I needed to make this form look correct.

[Dal Jeanis]

You're welcome. It's amazing what you can do with a hammer, pliers and tinsnips if you just keep at it.