By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,845 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

Split a report text box.

P: 30
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.
Feb 28 '09 #1
Share this Question
Share on Google+
7 Replies

Expert 100+
P: 1,923
see this link:

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.
Feb 28 '09 #2

P: 30
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":

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  3. Option Explicit
  5. Dim FirstPass As Integer
  7. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  8.     On Local Error GoTo Detail_Format_Err
  9.     If Not FirstPass Then
  10.         Me!txt_Item_Desc = Me![Item Desc] & " " & "SN: " & Me![SerNbr]
  11.         FirstPass = True
  12.     Else
  13.         Me!txt_Item_Desc = Me!txt_Item_Desc & "," & Me![SerNbr]
  14.     End If
  15. Detail_Format_End:
  16.     Exit Sub
  17. Detail_Format_Err:
  18.     MsgBox Error$
  19.     Resume Detail_Format_End
  20. End Sub
  22. Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
  23.     Me!txt_Item_Desc = Null
  24.     FirstPass = False
  25. 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.
Feb 28 '09 #3

Expert 100+
P: 1,923
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Page()
  3. Dim intLineCount As Integer
  4. Dim intLines As Integer
  5. Dim intLineSpacing As Integer
  6. Dim intTopMargin As Integer
  7. Dim intYPos As Integer
  9. intLines = 10
  10. intTopMargin = 8640                        '720 twips per half inch X 12 =  6 inch top margin
  11. intLineSpacing = Me.Detail.Height
  13.         If Me.Page = Me.Pages Then
  14.             For intLineCount = 1 To intLines
  15.                 intYPos = (intLineCount * intLineSpacing) + _
  16.                 intTopMargin
  17.                 Me.Line (0, intYPos)- _
  18.                 Step(Me.Width, 0)
  19.             Next
  20.         End If
  21. End Sub
Feb 28 '09 #4

Expert 100+
P: 1,923
If all else fails, Stephen Leban's solution should work:
Mar 1 '09 #5

P: 30
Thanks I'll try them out as soon as I get a chance and post the results.
Mar 1 '09 #6

P: 30
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?
Mar 6 '09 #7

Expert 100+
P: 1,923
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 is probably your best option.
Mar 8 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.