Split a report text box.

[scottbouley]

I am creating an Access report that replicates a preprinted grid form by using bordered text boxes. Each detail row on the preprinted form accomodates two lines. However,some of my records consist of several lines. When the print view is generated, the first two lines appear in the text box and the remaining lines appear in a blank white space after the bordered area. I want to split records with more than two lines into multiple bordered text boxes so that the data forms one continuous grid with no white space between. I'm fairly certain the code will have to go in the on format event.

Thank you in advance for your help.

[puppydogbuddy]

see this link: http://support.microsoft.com/kb/299011

The link includes a method and code for concatenating values from multiple controls into fewer controls for display purposes, eliminating white space. See if that will work for you.

[scottbouley]

Thanks for your reply puppydogbuddy.

That's the opposite direction I want to go. I've already concatenated the many side of the relationship to reduce vertical space. However, some of the records are longer than two lines. I'm trying to reproduce an actual grid-style form by using borders on the textboxes. The actual form allows two lines per cell so I set the height of the textboxes to the height of the actual cell to show two lines. However, if a record contains more than two lines, all text after the second line appears outside of the border resulting in unbordered text and a disjointed grid. What I'm looking for, is a way to split the concatenated text field into as many text boxes as neccessary to keep the grid together. Here's my code for the "On Format Event":

Option Compare Database
 
Option Explicit
 
Dim FirstPass As Integer
 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    On Local Error GoTo Detail_Format_Err
    If Not FirstPass Then
        Me!txt_Item_Desc = Me![Item Desc] & " " & "SN: " & Me![SerNbr]
        FirstPass = True
    Else
        Me!txt_Item_Desc = Me!txt_Item_Desc & "," & Me![SerNbr]
    End If
Detail_Format_End:
    Exit Sub
Detail_Format_Err:
    MsgBox Error$
    Resume Detail_Format_End
End Sub
 
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
    Me!txt_Item_Desc = Null
    FirstPass = False
End Sub

Maybe I'm taking the wrong approach. Is it possible to generate the lines in code? Or maybe split the text in the "On Format" Sub?

I'm still somewhat a Newbie since I don't get to do this alot, so I don't know any other way to approach this. Anyway, thanks again.

[puppydogbuddy]

Hi Scott,
You can try to adapt the code I wrote (see below) to draw a fixed number of lines per page. You can also try the link, which has a free download mdb entitled "Grid report", which shows how to use vba code to draw grid lines in an ms access report.

http://www.mvps.org/access/resources/downloads.htm#G

Private Sub Report_Page()
 
Dim intLineCount As Integer
Dim intLines As Integer
Dim intLineSpacing As Integer
Dim intTopMargin As Integer
Dim intYPos As Integer
 
intLines = 10
intTopMargin = 8640                        '720 twips per half inch X 12 =  6 inch top margin
intLineSpacing = Me.Detail.Height
 
        If Me.Page = Me.Pages Then
            For intLineCount = 1 To intLines
                intYPos = (intLineCount * intLineSpacing) + _
                intTopMargin
                Me.Line (0, intYPos)- _
                Step(Me.Width, 0)
            Next
        End If
End Sub
 

[puppydogbuddy]

If all else fails, Stephen Leban's solution should work:

http://www.lebans.com/PrintLines.htm

[scottbouley]

Thanks I'll try them out as soon as I get a chance and post the results.

[scottbouley]

Both of the options you showed me appear to result in a grid that corresponds to the height of the row. I need a fixed grid where the horizontal lines are always 0.3125 inches apart. I was originally trying to split the rows with more than two lines into multiple rows. Which way do you think would be easier?

[puppydogbuddy]

Scott,
Just got your last response. This site was having difficulties the past couple of days. You mention only 2 options, which means you probably did not see my post about a third option avalable from Stephen Leban's web site. You should check it out....it is probably your best option.

http://www.lebans.com/PrintLines.htm