ACCESS FEED: RE: [MS_AccessPros] Print fixed height vertical lines in detail section

John,
I tested this in Northwind with a subreport and line control. In my main report, the line control on the main report displays over the subreport so it is visible.

Duane Hookom MVP
MS Access

----------------------------------------
> To: MS_Access_Professionals@yahoogroups.com
> From: maxccess@gmail.com
> Date: Fri, 5 Apr 2013 15:03:10 +0500
> Subject: Re: [MS_AccessPros] Print fixed height vertical lines in detail section
>
> I have uploaded a screenshot too in the file section :)
>
>
> On Fri, Apr 5, 2013 at 12:41 PM, John Viescas <JohnV@msn.com> wrote:
>
> > **
> >
> >
> > Nevermind. Solid lines DO appear on top in Print Preview or Report View.
> > They're tough to set up because in Design View they "hide" behind the
> > Subreport control!
> >
> >
> > 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 John Viescas
> > Sent: Friday, April 05, 2013 9:38 AM
> > To: MS_Access_Professionals@yahoogroups.com
> >
> > Subject: RE: [MS_AccessPros] Print fixed height vertical lines in detail
> > section
> >
> > Duane-
> >
> > I don't see how that would work. You can certainly add a line control, but
> > it won't appear on top of the subreport. Subreport controls are always "on
> > top," so any line you add to the outer report will always disappear behind
> > the subreport control.
> >
> > 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)
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Abdul
> > Shakeel
> > Sent: Friday, April 05, 2013 6:39 AM
> > To: MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: Re: [MS_AccessPros] Print fixed height vertical lines in detail
> > section
> >
> > Dear Duane
> >
> > Thanks.... I am amazed it was that simple its really works for me
> >
> > On Thu, Apr 4, 2013 at 11:33 PM, Duane Hookom
> > <duanehookom@hotmail.com <mailto:duanehookom%40hotmail.com> >wrote:
> >
> >
> > > **
> > >
> > >
> > > I'm still not sure I totally understand. However if this was the
> > > Northwind and you wanted the detail section(s) to display 10 possible
> > > order details regardless of how many actual detail records existed for
> > > the order... I would base the report on the orders table and use a
> > > subreport based on the order details. Set the height of the order
> > > details subreport to a specific height and don't allow it to grow or
> > > shrink. You can then add lines to the main report that span the entire
> > height of the 10 possible order details.
> > >
> > >
> > > Duane Hookom MVP
> > > MS Access
> > >
> > > ----------------------------------------
> > > > To: MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > From: JohnV@msn.com <mailto:JohnV%40msn.com>
> > > > Date: Thu, 4 Apr 2013 14:33:00 +0200
> > > > Subject: RE: [MS_AccessPros] Print fixed height vertical lines in
> > > > detail
> > > section
> > >
> > > >
> > > > Abdul-
> > > >
> > > >
> > > >
> > > > It's a bit tricky. Here's what I did in Northwind 2007:
> > > >
> > > >
> > > >
> > > > 1) Create ztblReportLines and insert 15 numbered rows
> > > >
> > > > 2) Created a new query called Invoice Data Filter:
> > > >
> > > > PARAMETERS [Forms]![Order Details]![Order ID] Long;
> > > >
> > > > SELECT [Forms]![Order Details]![Order ID] AS OrderNo, Orders.[Order
> > > > ID], Orders.[Ship Name], Orders.[Ship Address], Orders.[Ship City],
> > > Orders.[Ship
> > > > State/Province], Orders.[Ship ZIP/Postal Code], Orders.[Ship
> > > > Country/Region], Orders.[Customer ID], Customers.Company AS
> > > > [Customer
> > > Name],
> > > > Customers.Address, Customers.City, Customers.[State/Province],
> > > > Customers.[ZIP/Postal Code], Customers.[Country/Region], [Employees
> > > > Extended].[Employee Name] AS Salesperson, Orders.[Order Date],
> > > > Orders.[Shipped Date], Shippers.Company AS [Shipper Name], [Order
> > > > Details].[Product ID], Products.ID AS [Product ID], [Order
> > > > Details].[Unit Price], [Order Details].Quantity, [Order
> > > > Details].Discount, CCur(Nz([Unit
> > > > Price]*[Quantity]*(1-[Discount]),0)/100)*100 AS ExtendedPrice,
> > > > Orders.[Shipping Fee], CLng(DCount("*","[Order Details]","[Product
> > > > ID]
> > > <= "
> > > > & NZ([Product ID],0) & " And [Order ID] = " & NZ([Orders].[Order
> > > ID],0))) AS
> > > > LineNo, Products.[Product Name]
> > > >
> > > > FROM (Shippers RIGHT JOIN (Customers RIGHT JOIN (Orders LEFT JOIN
> > > [Employees
> > > > Extended] ON Orders.[Employee ID] = [Employees Extended].ID) ON
> > > Customers.ID
> > > > = Orders.[Customer ID]) ON Shippers.ID = Orders.[Shipper ID]) LEFT
> > > > JOIN ([Order Details] LEFT JOIN Products ON [Order Details].[Product
> > > > ID] =
> > > > Products.ID) ON Orders.[Order ID] = [Order Details].[Order ID]
> > > >
> > > > WHERE (((Orders.[Order ID])=[Forms]![Order Details]![Order ID]));
> > > >
> > > >
> > > >
> > > > Note that in the sample database, the report opens with a filter on
> > > > Order ID, but to get the LEFT JOIN to work in the following query, I
> > > > had to
> > > add a
> > > > parameter pointing to the form that's always open and use that to
> > > generate
> > > > Order ID in all lines - even the "dummy " ones because the report is
> > > grouped
> > > > on Order ID.
> > > >
> > > >
> > > >
> > > > 3) Create a new query called Invoice Data Line Nos:
> > > >
> > > > SELECT ztblReportLines.LineNo, [Invoice Data Filter].OrderNo AS
> > > > [Order
> > > ID],
> > > > [Invoice Data Filter].[Ship Name], [Invoice Data Filter].[Ship
> > > > Address], [Invoice Data Filter].[Ship City], [Invoice Data
> > > > Filter].[Ship State/Province], [Invoice Data Filter].[Ship
> > > > ZIP/Postal Code], [Invoice
> > > Data
> > > > Filter].[Ship Country/Region], [Invoice Data Filter].[Customer ID],
> > > [Invoice
> > > > Data Filter].[Customer Name], [Invoice Data Filter].Address,
> > > > [Invoice
> > > Data
> > > > Filter].City, [Invoice Data Filter].[State/Province], [Invoice Data
> > > > Filter].[ZIP/Postal Code], [Invoice Data Filter].[Country/Region],
> > > [Invoice
> > > > Data Filter].Salesperson, [Invoice Data Filter].[Order Date],
> > > > [Invoice
> > > Data
> > > > Filter].[Shipped Date], [Invoice Data Filter].[Shipper Name],
> > > > [Invoice
> > > Data
> > > > Filter].[Product ID], [Invoice Data Filter].[Unit Price], [Invoice
> > > > Data Filter].Quantity, [Invoice Data Filter].Discount, [Invoice Data
> > > > Filter].ExtendedPrice, [Invoice Data Filter].[Shipping Fee],
> > > > [Invoice
> > > Data
> > > > Filter].LineNo, [Invoice Data Filter].[Product Name]
> > > >
> > > > FROM ztblReportLines LEFT JOIN [Invoice Data Filter] ON
> > > > ztblReportLines.LineNo = [Invoice Data Filter].LineNo;
> > > >
> > > >
> > > >
> > > > If you open the Order Details form and move to an order that has an
> > > invoice,
> > > > then open the above query, you should see the data lines from the
> > > > order details plus "n" blank lines depending on how many rows you
> > > > defined in ztblReportLines.
> > > >
> > > >
> > > >
> > > > 4) Modified the Invoice report so that it can open without a filter
> > > (remove
> > > > the Embedded Macro in the Open event) to test it. Changed the sort
> > > > on Product ID to descending to put all the "blank" lines at the end,
> > > > and modified the expresson for the txtExtendedPrice control to:
> > > > =IIf(IsNull([Product ID]),Null,[ExtendedPrice]) .. to clear out the
> > > > $0.00 display for the dummy lines.
> > > >
> > > >
> > > >
> > > > Report now always prints 15 lines per order regardless of the number
> > > > of products.
> > > >
> > > >
> > > >
> > > > 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%40yahoogroups.com>
> > > > [mailto:MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Abdul
> >
> > > Shakeel
> > > > Sent: Thursday, April 04, 2013 1:00 PM
> > > > To: MS_Access_Professionals@yahoogroups.com
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > Subject: Re: [MS_AccessPros] Print fixed height vertical lines in
> > > > detail section
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > On Thu, Apr 4, 2013 at 3:31 PM, John Viescas <JohnV@msn.com
> > <mailto:JohnV%40msn.com>
> > > > <mailto:JohnV%40msn.com> > wrote:
> > > >
> > > > > qryReportInventory
> > > >
> > > > Dear John,
> > > >
> > > > Very thank full to you for this help, but I could not employ this in
> > > > my database for some reasons, I threrfore requested you to use these
> > > > queries in Northwind sample database & upload in the file section of
> > > > the group it will also helpful to others out there
> > > >
> > > > --
> > > > Regards,
> > > > Abdul Shakeel
> > > >
> > > > [Non-text portions of this message have been removed]
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > [Non-text portions of this message have been removed]
> > > >
> > > >
> > > >
> > > > ------------------------------------
> > > >
> > > > Yahoo! Groups Links
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> > --
> > Regards,
> > Abdul Shakeel
> >
> > [Non-text portions of this message have been removed]
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
>
>
>
> --
> Regards,
> Abdul Shakeel
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>