ACCESS FEED: Re: [MS_AccessPros] Sub reports borders should touch main reports Page footer

John

As you mentioned it's a method of trial and errors to get it exactly right. I've played many many times and come to this stage that on preview of report i can see complete box on the page.

But when i do print left side is completely not printing. Again i do copy my code, please see still am i making any mistake. Also is there any way to get this box "Bold" and also if left side is not coming in print range is there any connection with page set up of access/office ? I did played there also but cannot resolve it yet.

Sub DrawLine()

Dim rpt As Report, lngColor As Long

Dim sngTop As Double, sngLeft As Double

Dim sngWidth As Double, sngHeight As Double

Set rpt = Reports![COO Aventis]

' Set scale to inches.

rpt.ScaleMode = 1

' Top inside edge.

' Calculate the top of the rectangle

sngTop = rpt.ScaleTop - 20

'------------------------

' Left inside edge.

sngLeft = rpt.ScaleLeft - 1 ' -1(putting this value in +ve and increasing value decreases top of box)

'--------------------------

' Width inside edge.

sngWidth = rpt.ScaleWidth - 2 'This increases Right width of box

'-----------------------------

' Height inside edge.

sngHeight = rpt.ScaleHeight - 0.75

'-------------------------------

' Make color black.

lngColor = RGB(0, 0, 0) ' Black

'------------------------------

' Draw line as a box.

rpt.Line (sngTop, sngLeft)-(sngWidth, sngHeight), lngColor, B

End Sub

Regards, Khalid

On Wednesday, November 20, 2013 2:10 PM, John Viescas <JohnV@msn.com> wrote:

Khalid-

You have set the ScaleMode to millimeters, which should be a small enough increment to allow you to adjust a bit at a time until it comes out how you want it. You need to play with this:

' Left inside edge.

sngLeft = rpt.ScaleLeft + 1

If the line is too far from the edge, try adding negative values. If it's not far enough from the edge, make the amount you add larger until the line moves until you want it. I have often found it's a matter of trial and error to get it exactly right.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid Tanweer
Sent: Wednesday, November 20, 2013 9:39 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Sub reports borders should touch main reports Page footer

John,

Thanks indeed for giving in detail help, with your guidance i am near to make my required box. I now put the Sub Drawline() on the report On Page event.

Now i am getting:

top of box-----> OK

Right of box-------------> OK

Bottom of box----------> OK (touching bottom of Page footer)

Only i could not adjust left border of box giving many tries. I am trying to give you an idea how far it is from left. It should come before a word having six alphabats in captial "XXXXXX" (Times New Roman, 14, Bold). My current is as below. Kindly guide where i am wrong.

Sub DrawLine()

Dim rpt As Report, lngColor As Long

Dim sngTop As Single, sngLeft As Single

Dim sngWidth As Single, sngHeight As Single

Set rpt = Reports![COO Aventis]

' Set scale to inches.

rpt.ScaleMode = 6 ' 6 This increases width of box

' Top inside edge.

' Calculate the top of the rectangle

sngTop = rpt.ScaleTop + 20

'------------------------

' Left inside edge.

sngLeft = rpt.ScaleLeft + 1

'--------------------------

' Width inside edge.

sngWidth = rpt.ScaleWidth - 2

'-----------------------------

' Height inside edge.

sngHeight = rpt.ScaleHeight - 0.75

'-------------------------------

' Make color black.

lngColor = RGB(0, 0, 0) ' Black

'------------------------------

' Draw line as a box.

rpt.Line (sngTop, sngLeft)-(sngWidth, sngHeight), lngColor, B

End Sub

Regards, Khalid

On Tuesday, November 19, 2013 1:45 PM, John Viescas <JohnV@msn.com> wrote:

Khalid-

You said it was printing red, so I gave you the correction for that. I didn't check the coordinates you specified. It looks like you copied those directly from the Help topic, and the topic says it draws a border around the entire current section. I would guess you're calling this from the Detail Format or Print event, which is the wrong place. To draw something on the entire page, you need to call it from the Page event. You need to figure out how far down the page you want the rectangle to start. Use the ScaleMode method to set the type of measurement:

expression.ScaleMode

expression A variable that represents a Report object.

Remarks

The ScaleMode property uses the following settings.

Setting	Description
0	Custom values used by one or more of the ScaleHeight, ScaleWidth, ScaleLeft, and ScaleTop properties
1	(Default) Twips
2	Points
3	Pixels
4	Characters (horizontal = 120 twips per unit; vertical = 240 twips per unit)
5	Inches
6	Millimeters
7	Centimeters

Use ScaleTop to get the top edge of curent object, ScaleLeft to get the left edge, ScaleWidth to get the width, and ScaleHeight to get the height. Then call Line to draw your rectangle:

rpt.Line (Top, Left) - (Width, Height), Color, B

When you do this in the Page event, ScaleTop should return the top edge of the page, ScaleLeft the left edge, ScaleWidth the width of the page, and ScaleHeight the height of the page. Let's say the top of the Detail section always starts 3" down from the top of the page. To calculate the top of the rectangle you want to draw, add three inches to the current ScaleTop:

Dim sngTop As Single

' Set scale to inches

rpt.ScaleMode = 5

' Calculate the top of the rectangle

sngTop = rpt.ScaleTop + 3

You'll probably have to fiddle with it a bit to get the rectangle exactly where you want it. It's a simple matter of setting the X, Y coordinates relative the the top, left, height, and width of the current page.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid Tanweer
Sent: Tuesday, November 19, 2013 6:43 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Sub reports borders should touch main reports Page footer

John,

The issue is not color anyways i have changed it to black.

At present i can observe a rectangular box over each row, as i can see a little gap about 1 mm in between each row, this sounds me that with each row a box is been created.

If i do preview report for a particular "ConsignmentNo" say "2013-A-09" the out put is 16 rows and the last row is about 1 inch above page footer. I need that left & right border lines should join/meet the top of page footer. My current code is below:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

' Call the Drawline procedure

DrawLine

End Sub

Sub DrawLine()

Dim rpt As Report, lngColor As Long

Dim sngTop As Single, sngLeft As Single

Dim sngWidth As Single, sngHeight As Single

Set rpt = Reports![COO Aventis]

' Set scale to pixels.

rpt.ScaleMode = 3

' Top inside edge.

sngTop = rpt.ScaleTop + 5

' Left inside edge.

sngLeft = rpt.ScaleLeft + 5

' Width inside edge.

sngWidth = rpt.ScaleWidth - 10

' Height inside edge.

sngHeight = rpt.ScaleHeight - 10

' Make color black.

lngColor = RGB(0, 0, 0) ' Black

' Draw line as a box.

rpt.Line (sngTop, sngLeft)-(sngWidth, sngHeight), lngColor, B

End Sub

Please look in it deeply and help.

Regards, Khalid

On , John Viescas <JohnV@msn.com> wrote:

Khalid-

Change the setting of lngColor:

lngColor = RBG(0,0,0) 'Black

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid Tanweer
Sent: Monday, November 18, 2013 1:10 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Sub reports borders should touch main reports Page footer

John,

Discussing about our first issue Reports Detail section should join/touch reports Page footer, I have read your link and from there pasted the example and then previewed the report. Except the report showing outline border in red color. Our main issue is still there. I am pasting the code here, could you please guide where should i make changes for my requirement.

Private Sub Report_Page()

' Call the Drawline procedure

DrawLine

End Sub

Sub DrawLine()

Dim rpt As Report, lngColor As Long

Dim sngTop As Single, sngLeft As Single

Dim sngWidth As Single, sngHeight As Single

Set rpt = Reports![COO Aventis]

' Set scale to pixels.

rpt.ScaleMode = 3

' Top inside edge.

sngTop = rpt.ScaleTop + 5

' Left inside edge.

sngLeft = rpt.ScaleLeft + 5

' Width inside edge.

sngWidth = rpt.ScaleWidth - 10

' Height inside edge.

sngHeight = rpt.ScaleHeight - 10

' Make color red.

lngColor = RGB(255, 0, 0)

' Draw line as a box.

rpt.Line (sngTop, sngLeft)-(sngWidth, sngHeight), lngColor, B

End Sub

Regards,

Khalid

On Monday, November 18, 2013 4:11 PM, John Viescas <JohnV@msn.com> wrote:

Khalid-

Putting lines in the Detail section won't work because the Detail sections stops at the last line of data. You need to use the Line method in the Print event to draw the lines all the way to the bottom. You can read about it here:

http://msdn.microsoft.com/en-us/library/office/ff198297.aspx

As long as you have just the Client ID (ClientCIN) in the CollectionVoucher table, the new name should appear the next time you run a query that includes CollectionVoucher and Clients. There should NOT be a copy of the name in the CollectionVoucher table.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid Tanweer
Sent: Monday, November 18, 2013 10:59 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Sub reports borders should touch main reports Page footer

John,

There is no code for line method, i simply drew lines equal to controls height in detail section. Perhaps i don't know the line method for which you are asking the code.

My second question is that i have a form "Find Client" its sql is:

SELECT Clients.ClientCIN, Clients.ClientName, Clients.FatherName, Clients.ConsigneeID, Clients.ConsigneeName, Clients.[NIC Number], Clients.Nationality, Clients.ContractDate, Clients.Working, Clients.MobileNo, Clients.Photo, Clients.HomeHouseNo, Clients.Street, Clients.Block, Clients.Area, Clients.HomePhone, Clients.HomeMobile, Clients.City, Clients.PostalCode, Clients.[Province/State], Clients.Country, Clients.[Bussiness Address], Clients.Notes

FROM Clients

WHERE (((Clients.Working)=True));

In this form only ClientCIN field is set to Locked. Now if suppose i change ClientName in this form. I need that in table "CollectionVoucher" where ever is this "ClientName" it should change with what i changed in form "Find Client" ClientName

Regards,

Khalid

On Monday, November 18, 2013 2:06 PM, John Viescas <JohnV@msn.com> wrote:

Khalid-

I would have to see your code for the Line method to see perhaps what's wrong.

I don't understand your second question. Is the Consignee form bound to the CollectionVoucher table?

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid Tanweer
Sent: Monday, November 18, 2013 7:21 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Sub reports borders should touch main reports Page footer

John,

I tried all your suggestions but was unable to get my required preview. Also my sql for subreport was not giving me exact data for e.g if i had actual 23 rows it was showing me 23 rows on two pages but total pages were becoming 46.

Anyways now i have excluded subreport and using this sql:

SELECT [Invoice Values Aventis].ConsignmentNo, Importer.ImporterNameEnglish, Importer.ImporterCityCountryEnglish, [Invoice Values Aventis].ExportDocs, [Invoice Values Aventis].ProductName, [Invoice Values Aventis].TotalCartons, [Invoice Values Aventis].UnitOfCarton, [Invoice Values Aventis].TotalProductQty, [Invoice Values Aventis].TotalGrossWt, [Invoice Values Aventis].UnitOfGrossWeight

FROM [Invoice Values Aventis], Importer

WHERE ((([Invoice Values Aventis].ConsignmentNo) Like [Enter Consignment No]));

All is OK except the same blank space if rows are less on one page blank space is there up to Page Footer. Or if rows are more, then on other page getting blank space up to Page Footer. I am using line method in detail section only vertical lines, on the Page footer there are two boxes (left and right, height 6.108 cm) in between these boxes there are some labels and text boxes.

One more question apart from this issue:

If on a "Consignee" form i edit any field how could it be changed or updated in "CollectionVoucher" table where data already exists for that field?

Regards,

Khalid

On Wednesday, November 13, 2013 8:06 PM, John Viescas <JohnV@msn.com> wrote:

Khalid-

Design the height of the subreport control so that it is tall enough to reach the page footers even when there are few rows. Set Can Shrink to No and leave Can Grow to Yes. When there are more rows than would fit on one page so that it goes to a second page, you would probably have to do something in the Format event to adjust the height accordingly.

An alternative would be to not put any borders around the subreport control and use the Line method to "draw" the borders all the way to the bottom of the page.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid Tanweer
Sent: Wednesday, November 13, 2013 8:09 AM
To: ms_access_professionals@yahoogroups.com
Subject: [MS_AccessPros] Sub reports borders should touch main reports Page footer

Hi John,

I have a report "COO Aventis" and i have put a subreport on it "COO Aventis QueryQuery subreport" on the detail section.

My question is that if subreport output is fewer rows its borders should join or touch Page Footers top of main report "COO Aventis". Now i am getting a blank space in between "COO Aventis QueryQuery subreport" & Page Footers top of main report "COO Aventis". (Page footer does contains about 12 lines which are to be diplayed necessary)

Second situation is if output is more than say 23 rows then (page 1 of 2) is OK it does fills "COO Aventis QueryQuery subreport" to the top of Page footer of main report "COO Aventis" but on (page 2 of 2) after 3-4 rows i get the blank space upto page footer of main report.

How to get rid of it? I need Sub reports borders should touch main reports Page footer.

Regards, Khalid