Need Label help

[ellenr]

I am trying to generate standard Avery 2160 address labels. Fonts are small enough to allow for up to 4 print lines none of which quite touch. Players have entered their own names and addresses via a website form, so I we never quite know what is in the fields. If the player has a foreign address, it will all be entered in the address field, whereas US addresses have 1 or 2 lines in the address field, and city, state and zip in their respective fields. Line 1 is set as name, no shrink or grow. Line 2 is set as address, shrink and grow set to yes, and line 3 (text4) is set for city & state & zip, concatenated and trimmed in the query, shrink and grow no. The detail OnPrint event is the following:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Me.Text4.Visible = False
If Len(Text4 & vbNullString) > 1 Then
    Me.Text4.Visible = True
End If
End Sub

The problem: Any row containing at least one label with 4 print lines pushes the next entire row of three labels down one line, throwing off the spacing of the labels. If I set line 3's can shrink to yes, then the label following the four-line label never has its text4.visible turned to yes, and the other problem (pushing next row down one line) persists. These labels were originally set up via the labels wizard (Access 2010).

How can I keep it from overflowing from one label to push down the next line? This just shouldn't be this hard!

 

[ellenr]

Solved! I added the following field to my query:

addr: [address] & IIf(Len([c])>1,Chr(13) & Chr(10) & [c],"")

I then deleted line 3 entirely (since its info is now added to line 2) and set the data source for line 2 to the new field [addr]. Line 2 is expanded tall enough to hold 3 print lines and set the CanGrow to no. This way, no label exceeds 4 print lines total.

 

[jdraw]

Good stuff and thanks for posting your solution.