Line to seperate Report with 2 columns grouped by ProductType

[LaughingDev]

I am working on a report to print a list of all the products in my DB with the unit prices, essentially a price list. The report is split into two columns via the pagesetup and is grouped by the field ProductType.

I would like to draw a single distinct vertical line between the two columns, I have tried using the Line method in the OnPrint event of the detail section. However this method results in 2 lines as there are 2 columns in that section.

I am currently trying to use the Line method in the Page event of the report instead. However since the line method uses the X & Y coordinates from the first page only which has a larger pageheader unlike the rest of the pages, the line draws correctly only for the first page but is too short for the rest of the pages.

Private Sub Report_Page()

Dim X1 As Single, Y1 As Single, X2 As Single, Y2 As Single
Dim col As Long

Me.DrawStyle = 0
Me.DrawWidth = 25

X1 = 5095
Y1 = (Me.PageHeaderSection.Height + Me.ReportHeader.Height)
X2 = X1

col = RGB(35, 115, 93)
Y2 = Me.ScaleHeight - (Me.PageFooterSection.Height + 100)

Me.Line (X1, Y1)-(X2, Y2), col
​

End Sub

Would appreciate any help or advice on this issue, Thanks

 

[vbaInet]

What about the Line control?

The idea is:

1. Create a textbox to get the column number
2. Hide the line for even numbers and make it visible for odd numbers (i.e. using Mod)

 

[LaughingDev]

Hi, sounds like a good idea, but how exactly would one retrieve the current column number?

Thanks

 

[vbaInet]

The same way you number a record in a number row-wise report.

1. Place a textbox in the section and enter =1 in the Control Source.
2. Set the Running Sum property of the textbox to Over Group.

 

[LaughingDev]

I probably should have mentioned, that the columns are laid out 'down, then across', which means using this method each column will have both odd & even numbers which will probably give 2 broken lines. Is there any other way to accomplish one solid line between the columns?

 

[vbaInet]

That's still not a problem. How many lines per page?

 

[LaughingDev]

Excuse my ignorance, but could I ask for some noob friendly instructions on how I would code it and in which event so that the line appears only at the end of the first column and not the second as well. At the moment I have a textbox which is counting each row downwards starting at 1 and restarting at each group header (ProductType).

Thanks.

 

[vbaInet]

That's why I asked you how many lines per page appear in your report?

Also, take a screenshot of the report, paste it in Paint and show me where you would like the lines. Two more posts before you can upload files

 

[LaughingDev]

Using the line control in the details section gives me 2 lines per page, I am positioning the line at the end of the first column and thus it is repeated at the end of the second column.

 

[vbaInet]

Sorry, by lines I meant, how many rows per page appear in your report.

For example, is it a 6 row by 2 column report?

 

[LaughingDev]

Well, technically there are 44 rows, but some of these are used by the GroupHeaders (ProductType) which can vary depending on the products table.

 

[vbaInet]

In your next post you can attach the screenshot

So each ProductType group doesn't print on a separate page it just carries on from the next?

I'm thinking it might even be easier if you just upload a stripped down version of your db.

 

[LaughingDev]

Yes, each ProductType continues in the same column and page, I would like to keep it this way.
Ive attached a screenshot of the report with the line in the details section.

 

[vbaInet]

You haven't told me which line you would like to appear? I thought you said you just wanted a continuous vertical line that separates the columns. But in your pic you've drawn several lines both dotted and bold. Care to elaborate?