add blank lines to report

[Barry A&P]

I am looking for help mimicking a form that was previously created with
excel. i want to create a block in a report wich may possibly be a sub
report that will stay the same size and add blank lines to any additional
space below the data being displayed so that users can Hand write additional
entries once the form is printed..
I would like the access populated rows to "Grow" and Wrap text to fit the
reports width. but the following blank lines to be something like 20pt or 1/4"

Any help would be greatly appreciated

Thanks
Barry

 

[Steve]

See Below ....

Steve
(e-mail address removed)

I am looking for help mimicking a form that was previously created with
excel. i want to create a block in a report wich may possibly be a sub
report that will stay the same size and add blank lines to any additional
space below the data being displayed so that users can Hand write
additional
entries once the form is printed..
I would like the access populated rows to "Grow" and Wrap text to fit the
reports width. but the following blank lines to be something like 20pt or
1/4"

Any help would be greatly appreciated

Thanks
Barry

Creating Blank Lines In A Report



Blank line items are needed in reports such as Purchase orders and Invoices
to give the area below the last detail and the top of the Report Header the
same appearance as the filled-in line items. This can be done by creating a
Table2 containing whatever the number of blank rows it takes to fill the
detail section of the report and an Union Query combining Table1 and Table2.
The example below shows a simple report using this technique.

Table1

AID A A1 A2

1
$1
11
Text1

2
$2
12
Text2

3
$3
13
Text3

4
$4
14
Text4

5
$5
15
Text5


Table2

AROW ROW B B1 B2

1
R1

2
R2

3
R3

4
R4

5
R5


Union Query

SELECT Table1.Aid, Table1.A, Table1.A1, Table1.A2

FROM Table1

WHERE (((Table1.A1)<"14"))

UNION

SELECT Table2.Row, Table2.B, Table2.B1, Table2.B2

FROM Table2

WHERE (((Table2.AROW)<="3"));



Report

A A1 A2

$1
11
Text1

$2
12
Text2

$3
13
Text3





Note: The first Where clause in the Union Query determines which records
from Table1 will be in the report. In a Purchase Order or an Invoice, the
Where Clause would specify the POID or InvoiceID to determine which detail
records would be in the report. The second Where clause in the Union Query
determines which records from Table2 (or the number of blank line items)
will be in the report. In a Purchase Order or an Invoice, the Where Clause
is used to specify the number of blank line items. In the code, the number
of detail line items would be counted and that number would be subtracted
from the number of line items that would fit in the detail section of the
report. The calculated difference would be placed in a textbox which becomes
the criteria in the second Where clause.

 

[Barry A&P]

Steve

Thanks for your reply.. i didnt think of a union query to add blank lines.
Your response talks about code and a text box to count the number of rows to
add from the second table.. do i just do a Dcount with the same criteria as
your first WHERE clause? as the number for the second WHERE?? what do i do
about "CAN GROW" on the first set of records returning a bad count for
remaining rows to fill ??

Ill start researching for more details. thank you so much for getting me on
track

Barry

 

[Steve]

Hi Barry,

You will need to determine how many records fit on the first page of the
report and each of the succeeding pages. You will need to do this manually.
This will be the number of records it takes to completely fill your report.
Say it takes 25 on the first page and 29 on each succeeding page. The number
of records it takes to completely fill your report can be determined with
the formula:
25 + (Pages - 1) * 29

Next , Yes, use a DCount to determine how many records are in the
recordsource of the report:
DCount(...............)

Finally, in the textbox's controlsource put the number of blank rows needed:
Number from Formula - DCount

Re: Can Grow. Set it to Yes and you don't need to worry about it. The Detail
section will grow to fill the last page down to the Report Footer.


Steve

 

[Barry A&P]

Steve

I have read alot about the infamous "Steve" ??

I truely appreciate the help..

Barry

 

[BruceM via AccessMonster.com]

Steve has provided some good suggestions from time to time. The trouble is
he strays from that approach, and instead solicits work in the newsgroups.

I remember this topic from a thread a week or two ago. I didn't know a
solution had been developed. I have not tried it, but I see the reasoning.
It is something I may try applying for a sign-in sheet where some of the
names are entered in advance and others on the fly. I would probably remove
the quotes from around the numbers, as the text value "2" may be evaluated as
greater than the number 14. Perhaps Access can sort that out, but even if so
a number is better treated as number than as text.

Also, if numbers are derived from the record count (you could use DCount, or
you could try Me.Recordset.RecordCount) and you allow the text boxes to grow
you may not get the correct result. I expect this would work best with fixed
height text boxes and Detail section. It may be possible to use a report
event procedure to test the height of each text box, add them together, and
calculate the number of blank rows remaining, but even if it is possible it
is a new level of complexity. In any case, it sounds as if you may want the
detail section to be the same height each time.

Steve

I have read alot about the infamous "Steve" ??

I truely appreciate the help..

Barry

Hi Barry,

[quoted text clipped - 169 lines]

 

[John Spencer]

Since the poster wants the detail sections to grow, I am not sure this
solution would work very well.

If all the poster wants is to add blank lines across the last page until the
bottom of the last page is reached, then that is possible. I have seen it
done, but I would need to do some research in order to do this. I think Duane
Hookom may have posted a solution to this in the past.

If the poster wants labels to print plus the lines, this also could be done.
It would be a bit trickier and again, I would need to research the solution
since I don't have an example available.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Gina Whipp]

Barry,

Here are two potential solutions...

From Marsh MVP

http://www.eggheadcafe.com/conversation.aspx?messageid=29530730&threadid=29526322


OR


You might want to remove the borders from all controls and use code to
drawthe boxes. The following code in the On Page event of the report might
work
for you.

Private Sub Report_Page()

Dim intNumLines As Integer

Dim intLineNumber As Integer

Dim intTopMargin As Integer

Dim ctl As Control

Dim intLineHeight As Integer

intNumLines = 12

intTopMargin = Me.Section(3).Height

intLineHeight = Me.Section(0).Height

For Each ctl In Me.Section(0).Controls

For intLineNumber = 0 To intNumLines - 1

Me.Line (ctl.Left, intTopMargin + _

(intLineNumber * intLineHeight)) _

-Step(ctl.Width, intLineHeight), , B

Next

Next

End Sub

--

Duane Hookom

Microsoft Access MVP


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm