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

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/