Empty line in a report

[Guest]

I address on my report, some have 2 address lines while others have only 1.
e.g.

John smith
123 Address line 1
Address line 2
Las Vegas, NE 14785 - 0000

What I would like to do is if Address 2 is not available then shrink it so
that the city/state/zipcode line goes up to that line.... How do I do that?

 

[Joseph Meehan]

I address on my report, some have 2 address lines while others have
only 1. e.g.

John smith
123 Address line 1
Address line 2
Las Vegas, NE 14785 - 0000

What I would like to do is if Address 2 is not available then shrink
it so that the city/state/zipcode line goes up to that line.... How
do I do that?

Make sure there are no spaces in that second address field and make sure
the "can shrink" property of the filed on the report is set to Yes.

 

[Guest]

There are two ways you can do this.

1. Set the CanShrink property of each address line control in the report to
True. In a report the canShrink property of the section in which the
controls are located would normally also be set to True unless you want the
height of the section to remain static even if the controls in it have
shrunk. Note, however, that if there are other non-shrinking controls
alongside the shrinkable control, including graphics such as lines, the
control will not shrink. You should also make sure that the text box
controls for each line of the address are contiguous top to bottom as any
white space between them won't shrink. This means making the controls higher
than the height of the text they contain so as to keep a visually pleasing
line spacing.

2. Put the whole address in a single text box control by concatenating the
lines and inserting a carriage return/line break between them. The secret
here is not to use the & (ampersand) concatenation operator to tack on the
cr/lf to each line, but to use the + (addition sign). This is because in
arithmetical expressions Nulls propagate, so Null + anything = Null.
Consequently if the line is Null the cr/lf will be suppressed.

Access provides a vbNewLine constant for the cr/lf but you can't use this
directly in the ControlSource of a control, so first create a function in a
standard module:

Function InsertLine()

InsertLine = vbNewLine

End Function

As the ControlSource for a single text box to show both address lines out:

=[AddressLine1] & (InsertLine()+[AddressLine2])

Make sure the control has a different name to either of the fields, so call
it something like txtAddressLines and set its CanGrow property to True (Yes)
in the properties sheet. Do the same for the section's CanGrow property
unless you want that to remain static. Make the control just one line high
in report design view.

Once you've created the InsertLine function you can of course use it
anywhere in the database, so it can be used on forms or in queries as well as
in the report.

Ken Sheridan
Stafford, England

 

[Guest]

Ken that was good it worked but as mentioned in Number 1 I do have vertical
and horizontal lines as seen below
_______________________________________
John smith |
123 Address line 1 |
Address line 2 |
Las Vegas, NE 14785 - 0000 |
____________________________|

What is actually happening is if there ia address line2 the Horizal moves
down and the continous Horizontal line is missing as follows
__________________________________
John smith |
123 Address line 1 |
Address line 2 |
Las Vegas, NE 14785 - 0000 |
_____________________________

There are two ways you can do this.

1. Set the CanShrink property of each address line control in the report to
True. In a report the canShrink property of the section in which the
controls are located would normally also be set to True unless you want the
height of the section to remain static even if the controls in it have
shrunk. Note, however, that if there are other non-shrinking controls
alongside the shrinkable control, including graphics such as lines, the
control will not shrink. You should also make sure that the text box
controls for each line of the address are contiguous top to bottom as any
white space between them won't shrink. This means making the controls higher
than the height of the text they contain so as to keep a visually pleasing
line spacing.

2. Put the whole address in a single text box control by concatenating the
lines and inserting a carriage return/line break between them. The secret
here is not to use the & (ampersand) concatenation operator to tack on the
cr/lf to each line, but to use the + (addition sign). This is because in
arithmetical expressions Nulls propagate, so Null + anything = Null.
Consequently if the line is Null the cr/lf will be suppressed.

Access provides a vbNewLine constant for the cr/lf but you can't use this
directly in the ControlSource of a control, so first create a function in a
standard module:

Function InsertLine()

InsertLine = vbNewLine

End Function

As the ControlSource for a single text box to show both address lines out:

=[AddressLine1] & (InsertLine()+[AddressLine2])

Make sure the control has a different name to either of the fields, so call
it something like txtAddressLines and set its CanGrow property to True (Yes)
in the properties sheet. Do the same for the section's CanGrow property
unless you want that to remain static. Make the control just one line high
in report design view.

Once you've created the InsertLine function you can of course use it
anywhere in the database, so it can be used on forms or in queries as well as
in the report.

Ken Sheridan
Stafford, England

I address on my report, some have 2 address lines while others have only 1.
e.g.

John smith
123 Address line 1
Address line 2
Las Vegas, NE 14785 - 0000

What I would like to do is if Address 2 is not available then shrink it so
that the city/state/zipcode line goes up to that line.... How do I do that?

 

[Guest]

If you shrink controls lines are a problem. You have to draw them at
runtime. Continuous vertical limes are not a problem because you can set
them artificially tall and they'll be cut off at the bottom of the section.
Here's an example which draws a line 2 inches in from the left margin
extending the full height of the detail section. First you out a procedure
in the report's module:

Sub DrawLine()

Dim lngColor As Long
Dim sngHeight As Single

' Set scale to twips
Me.ScaleMode = 1
' Set line width in pixels
Me.DrawWidth = 5
' Set height to artificially high value
sngHeight = 14400
' Make colour black
lngColor = RGB(0, 0, 0)
' Draw line 2 inches in from left margin
Me.Line (2880, 0)-(2880, sngHeight), lngColor

End Sub

Then you call it in the detail section's Print event procedure with:

' Call the Drawline procedure
DrawLine

A horizontal top line is no problem, but I can't see any way to draw a
bottom horizontal line when the height of the section changes.

Ken Sheridan
Stafford, England

 

[Guest]

Ken thansk for the reply, however I am have a problem when I try to compile
the report, its pointing to the first me saying compile error: Invalid use of
me keyword. What should I do?

 

[Guest]

My guess would be that you've put the procedure in a standard module rather
than the report's module. You could put it in a standard module and pass a
reference to the report object as an argument, but as the code is likely to
be specific to the report outing it in the report's module makes more sense.

Ken Sheridan
Stafford, England

 

[Guest]

You are very right, I had put it in the standatrd module, thanks alot that
worked...

 

[Guest]

It did draw a line, my other question how do I make it appear at a certain
place, the height of the real line I had was 1.125, left was 3.875 top was
0.0417, I tried to plug in the nymbers but It looked like the report had a
line drawn like / from one edge to the other...

 

[Guest]

The unit for the dimensions in VBA is the Twip (one twentieth of a point).
There are 1440 Twips per inch which is why the 2880 in this:

Me.Line (2880, 0)-(2880, sngHeight), lngColor

draws the line 2 inches in. You can also draw a box by adding a B, so the
following would draw a box 2 inches square 2 inches in and 100 twips from the
top:

Me.Line (2880, 100)-(5760, 2880), lngColor, B

In this case the section would have to be deep enough to hold the square or
the bottom of the box will be truncated. When controls are shrinking or
growing this causes a problem. The first example above is designed to draw a
continuous line through all the detail rows. Its actually drawing a separate
line for each detail but they all join up. This is done by starting the line
at zero (the second dimension) and making its height artificially long so it
will always be truncated no matter how much the section grows.

If you want a line to start below the top of the section you change the
second dimension to something greater than zero (experiment to get it right).
To stop the line before the bottom of the section you'd have to set the
value of the sngHeight variable in the detail section's Format section so
that it varies depending on whether the second address line is Null or not.
You'd then pass the value into the DrawLine procedure as a sngHeight argument
rather than declaring it as a variable in the procedure.

A top horizontal line can just be a line object in the report of course. A
bottom line can't however, as the section's height is variable. One way
would be to draw the horizontal line with another procedure, passing the
value for its vertical position into it as an argument. Another way would be
to have an unbound text box at the bottom of the detail section with a
continuous line of underscore characters in it. As the section grows this
would be pushed down.

Ken Sheridan
Stafford, England

 

[Guest]

Ken, thank, I tried and managed to draw the line where I want it but it works
if there are 2 Address lines, but when I have only one, the height of the
line is equal to the line 2 address lines

this is how I twicked it....
Sub DrawLine()

Dim lngColor As Long
Dim sngHeight As Single


' Set scale to twips
Me.ScaleMode = 1
' Set line width in pixels
Me.DrawWidth = 5
' Set height to artificially high value
sngHeight = 1980 'Changed this from 5
' Make colour black
lngColor = RGB(0, 0, 0)
' Draw line 2 inches in from left margin
'Me.Line (2880, 0)-(2880, sngHeight), lngColor
Me.Line (5572, 50)-(5572, sngHeight), lngColor ' changed the first 3
fields
End Sub

 

[Guest]

Ken, how do I set the value of the sngHeight variable in the detail section's
Format section so that it varies depending on whether the second address line
is Null or not.

I know for sure that if the height is 1980 it will accomodate the 2
addresslines while 1720 will accomodate 1 addressline.

 

[Guest]

You need to adjust the height of the line conditionally, depending on whether
the second address is Null or not. One way to do this would be create a more
generic procedure in the report’s module:

Sub DrawLine(lngLeft As Long, _
lngTop As Long, _
lngRight As Long, _
lngHeight As Long, _
intLineWidth As Integer)

Dim lngColor As Long

' Set scale to twips
Me.ScaleMode = 1
' Set line width in pixels
DrawWidth = intLineWidth
' Make colour black
lngColor = RGB(0, 0, 0)
' Draw line 2 inches in from left margin
Me.Line (lngLeft, lngTop)-(lngRight, lngHeight), lngColor

End Sub

You can then pass the values into it when you call it in the detail
section’s print event procedure, changing the value for the height depending
on whether the second address line is Null or not. Taking the line 2 inches
in as an example you would call it along the following lines. Find the best
height value for when the second address line is Null by experimenting with
different values for the shorter line:

If IsNull(Me.AddressLine2) Then
‘ make line shorter
DrawLine 2880, 0, 2880, 1400, 5
Else
‘ make line full length
DrawLine 2880, 0, 2880, 1980, 5
End If

Note that AddressLine2 must be a control bound to the second address line
field, so if you concatenating the whole address into a single control you’d
still need a hidden (Visible property set to No in properties sheet) control
bound to the AddressLine2 field (or whatever the field is called). This can
be small and under a (non-shrinking) visible control, however, as it is only
there to give the code something to refer to. Unlike in a form you can’t
refer directly to a field in a report’s module, only to a control bound to it.

Ken Sheridan
Stafford, England

 

[Guest]

Ken, That helped alot, thank you so much. I appreaciated your help!

 

[mmellring]

jmodrwe743re2943w w932e q9 6d jai3o13 we2323e19 361 w31i2