Is their a way to run vertical lines all the way through many report sections in access?

 I would suggest not using the page footer, but the group footer.

 And to answer the question, best place to draw lines (Vertical or horizontal) is in the OnPage event.

Jim.

Thanks for the comment J.. I didn't even think about using the group footer.. Do you know if the group footer can be forced to print at the bottom of the page?

Also, I'm not familiar with OnPage event.. Could you tell me how to use that?

  I think you can get to where you want using the group footer, but in case you want to draw lines, this will help:

http://www.lebans.com/PrintLines.htm

Jim.

Probably my lack of knowledge thus far for vba but that seems a little more than what I'm willing to try and tackle.. Do you know if I can just throw in a sub report? and how I can link that to each page of my main report?

e.g. Each page of my main report displays Assemblies (ParentAssemblySeq) from Jobs (jpJobNum)

Group #1 Job
   Group #2 AssemblySeq
       Details Would like to place the subreport here that returns all the process and materials for the particular Job\AssemblySeq..

(Each Page has it's own AssemblySeq on it)

Hopefully some of that info may help?

Printing a group footer at a specific point is doable (use method #3):

ACC2000: How to Print a Group Footer at a Specific Location
http://support.microsoft.com/kb/208979

 That will get you pretty close to what you want (footer may not be smack on the bottom of the page depending on the size of the detail section - smaller the detail section the better you'll do).

 OnPage is the last event to fire before the page is printed.  At this point, the page is fully rendered and this is when you can draw vertical lines, borders, etc.  You would do something like this:

Me.DrawStyle = 0
Me.DrawWidth = 11

' Draw a border around the page.
Me.Line (0, 0)-(Me.ScaleWidth, Me.ScaleHeight), , B

Jim.

I guess the main reason I keep leaning back to a subreport because I'm thinking that will make it easy to add the gridlines to finish filling out the remainder of the page. I want these gridlines to fill out through the rest of the page so that when the report is exported a user will already have the lines in place to add more information if need be.

One method would be to modify your Reports RecordSource so that the query always returns a set number of rows, where the extra rows would all contain NULL values in each of the columns

To do this, I create another table (tbl_Numbers) with one field (intNumber) and 100 records (the values 0 to 99).  Then in the Recordsource for the report, I would do something like the following, which assumes that you need 15 rows to fill in the page.  I use 999999 as the 2nd column in the 2nd part of the union query to ensure that when when you Group by the OrderID and Sort by the DetailID, the empty records sort to the bottom.  This number (999999) might need to be bigger, depending on the number of records in your table.

SELECT OrderID, DetailID, Field2, Field3, Field4
FROM yourTable
WHERE OrderID = 2345
UNION
SELECT 2345, 9999999, NULL, NULL, NULL
FROM tbl_Numbers
WHERE intNumber < (15 - DCOUNT("Field1", "yourTable", "ID = 2345"))

 I would avoid the sub-report; harder to control things.   Use the group footer and move the detail section down.  It's not all that hard and will give you the blanks you want with the lines.

 Or you can use fyed's suggestion, which works as well, but requires extra work to maintain if you change your report/table layout at all.

Jim.

fyed, this sounds interesting althought I must post my current query that's being used to produce my data for this report.. See what you think under the circumstances..

SELECT DISTINCT dbo_View_JobProd.jpJobNum AS jpJobNum, dbo_View_JobHead_1.jhJobNum, dbo_View_JobAsmbl.jaAssemblySeq AS ParentAssemblySeq, dbo_View_JobAsmbl.jaPartNum AS ParentPartNum, dbo_View_JobAsmbl.jaDescription AS ParentPartDescription, dbo_View_JobMtl.jmMtlSeq, dbo_View_JobMtl.jmDescription, dbo_View_JobMtl.jmDescription AS jmCommentText, dbo_View_JobMtl.jmPartNum, dbo_View_PORel.prPONum, dbo_View_Vendor.vName, dbo_View_VendorPP.vpAddress1, dbo_View_VendorPP.vpCity, dbo_View_VendorPP.vpState, dbo_View_VendorPP.vpZip, dbo_View_RcvDtl.rdPackSlip
FROM dbo_View_RcvDtl RIGHT JOIN ((dbo_View_Vendor RIGHT JOIN (dbo_View_POHeader RIGHT JOIN (dbo_View_PORel RIGHT JOIN ((dbo_View_JobAsmbl RIGHT JOIN (dbo_View_JobHead RIGHT JOIN (dbo_View_JobHead AS dbo_View_JobHead_1 RIGHT JOIN dbo_View_JobProd ON dbo_View_JobHead_1.jhJobNum = dbo_View_JobProd.jpTargetJobNum) ON dbo_View_JobHead.jhJobNum = dbo_View_JobProd.jpJobNum) ON dbo_View_JobAsmbl.jaJobNum = dbo_View_JobProd.jpJobNum) LEFT JOIN dbo_View_JobMtl ON (dbo_View_JobAsmbl.jaJobNum = dbo_View_JobMtl.jmJobNum) AND (dbo_View_JobAsmbl.jaAssemblySeq = dbo_View_JobMtl.jmAssemblySeq)) ON (dbo_View_PORel.prJobNum = dbo_View_JobMtl.jmJobNum) AND (dbo_View_PORel.prAssemblySeq = dbo_View_JobMtl.jmAssemblySeq) AND (dbo_View_PORel.prJobSeq = dbo_View_JobMtl.jmMtlSeq)) ON dbo_View_POHeader.phPONum = dbo_View_PORel.prPONum) ON dbo_View_Vendor.vVendorNum = dbo_View_POHeader.phVendorNum) LEFT JOIN dbo_View_VendorPP ON (dbo_View_Vendor.vVendorNum = dbo_View_VendorPP.vpVendorNum) AND (dbo_View_Vendor.vPurPoint = dbo_View_VendorPP.vpPurPoint)) ON (dbo_View_RcvDtl.rdPONum = dbo_View_PORel.prPONum) AND (dbo_View_RcvDtl.rdPOLine = dbo_View_PORel.prPOLine) AND (dbo_View_RcvDtl.rdPORelNum = dbo_View_PORel.prPORelNum)

ORDER BY dbo_View_JobProd.jpJobNum, dbo_View_JobAsmbl.jaAssemblySeq;

UNION SELECT DISTINCT dbo_View_JobProd.jpJobNum AS jpJobNum, dbo_View_JobHead_1.jhJobNum, dbo_View_JobAsmbl.jaAssemblySeq AS ParentAssemblySeq, dbo_View_JobAsmbl.jaPartNum AS ParentPartNum, dbo_View_JobAsmbl.jaDescription AS ParentPartDescription, dbo_View_JobOper.joOprSeq, dbo_View_JobOper.joOpDesc, dbo_View_JobOper.joCommentText, dbo_View_JobOper.joPartNum, dbo_View_PORel.prPONum, dbo_View_Vendor.vName, dbo_View_VendorPP.vpAddress1, dbo_View_VendorPP.vpCity, dbo_View_VendorPP.vpState, dbo_View_VendorPP.vpZip, dbo_View_RcvDtl.rdPackSlip
FROM dbo_View_RcvDtl RIGHT JOIN ((dbo_View_Vendor RIGHT JOIN (dbo_View_POHeader RIGHT JOIN (dbo_View_PORel RIGHT JOIN ((dbo_View_JobAsmbl RIGHT JOIN (dbo_View_JobHead RIGHT JOIN (dbo_View_JobHead AS dbo_View_JobHead_1 RIGHT JOIN dbo_View_JobProd ON dbo_View_JobHead_1.jhJobNum = dbo_View_JobProd.jpTargetJobNum) ON dbo_View_JobHead.jhJobNum = dbo_View_JobProd.jpJobNum) ON dbo_View_JobAsmbl.jaJobNum = dbo_View_JobProd.jpJobNum) LEFT JOIN dbo_View_JobOper ON (dbo_View_JobAsmbl.jaJobNum = dbo_View_JobOper.joJobNum) AND (dbo_View_JobAsmbl.jaAssemblySeq = dbo_View_JobOper.joAssemblySeq)) ON (dbo_View_PORel.prJobNum = dbo_View_JobOper.joJobNum) AND (dbo_View_PORel.prAssemblySeq = dbo_View_JobOper.joAssemblySeq) AND (dbo_View_PORel.prJobSeq = dbo_View_JobOper.joOprSeq)) ON dbo_View_POHeader.phPONum = dbo_View_PORel.prPONum) ON dbo_View_Vendor.vVendorNum = dbo_View_POHeader.phVendorNum) LEFT JOIN dbo_View_VendorPP ON (dbo_View_Vendor.vVendorNum = dbo_View_VendorPP.vpVendorNum) AND (dbo_View_Vendor.vPurPoint = dbo_View_VendorPP.vpPurPoint)) ON (dbo_View_RcvDtl.rdPONum = dbo_View_PORel.prPONum) AND (dbo_View_RcvDtl.rdPOLine = dbo_View_PORel.prPOLine) AND (dbo_View_RcvDtl.rdPORelNum = dbo_View_PORel.prPORelNum);

Select allOpen in new window

I use something similar to what fyed suggested

You can create a formula to calculate how many records will fit on a page, then subtract the number of records present, then the result would be how many Blank records you would need to create

example: 25 records can fit on a page
18 records present
28-18=7 blank record to create.

You may have to do a bit more if the number of record exceeds the max

Perhaps because I am coming late to the party, I am not understanding something, or I am rehashing what the others have posted though...

I can post a sample of what I am proposing if you like...


JeffCoachman

boag, This does sound like what they are proposing.. I am definitely game for this assuming I can easily tweak my above posted SQL statement (if need be) or the formula goes somewhere else?.. There are definitely going to be occurrences where there will be records running on multiple pages as well so it would have to be intelligent enough to figure out how to add records at the end of several or so pages.

For example, (based on 20 records a page)

Assembly 1 may have 2 records so I would need 18 blanks to fill the remainder of the page

Assembly 2 may have 0 records so I would like to have N/A's posted across the top of the page in the 6 different columns and then fill in the remaining 19 records blank.

Assembly 3 may have 30 records so the first page would be full, and the second page would have 10 records with the remaining 10 needing to be blank.

Let me know if this sounds feasable to accomplish with some formulas.
Thanks

OK, to be fair, I'll let you continue with the previous experts to avoid confusion.

I'll simply post my sample as a reference, as they seem to be helping you with your issue directly...

Sounds good to me! I appreciate all of the help.

"fyed.... SELECT OrderID, DetailID, Field2, Field3, Field4
FROM yourTable
WHERE OrderID = 2345
UNION
SELECT 2345, 9999999, NULL, NULL, NULL
FROM tbl_Numbers
WHERE intNumber < (15 - DCOUNT("Field1", "yourTable", "ID = 2345"))
 "

Fyed, I don't have a Where clause in my Select statement.. I am choosing my records by filtering when the report is running based on a jobnum placed in a textbox. How would I change this so that the sql is pulling the textbox field?

Sorry, got preoccupied with work and will not get around to taking another look at this until the morning (It's just about 10PM now).

No problem fyed.. When you get to it.. Thanks!

j Thanks for your input.. I definitely like where fyed is going with it.. It would be nice to get a conistent set of records on each page so to finish out the page with blanks.. I did go through the link you provided and saw how that was working but I think even with that and creating lines I still won't necessarily have horizontal lines through each row.. In any case thanks again for the input.. I can still use that as an assisted solution for sure though..

There are a couple of demos for "filling" out a report in my examples page (link in profile).
Repeating the detail section isn't difficult.  (Stopping at the right point - somewhat more so :-p)

Hey L.. I guess these are similar to what J posted on earlier.. I don't necessarily follow these too well (lack of experience) and do you know if any of these methods will extend all of my lines down (horizontal and vertical) through the blank space as well? I really need to remainder of the page to format just like I have posted in my example..

<<do you know if any of these methods will extend all of my lines down (horizontal and vertical) through the blank space as well? I really need to remainder of the page to format just like I have posted in my example.>>

  If that formatting is in the detail section, then yes they will.  

  As mentioned, the problem is in stopping at a specific point.  Depending on the size of the detail and group sections, it can be difficult to get close to the bottom of the page.  Yours are fairly small, so you should not have too much difficulty in getting close.

<< I don't necessarily follow these too well (lack of experience) >>

  Just follow method #3 in the MSKB article and if you get stuck holler.

Jim.

Actually,  method #2 may be a little more easily understood and will probably still work fine for you.

  With both methods, your using MoveLayout, NextRecord, and PrintSection properties to control the report engine.

  Their pretty much just what they sound like:

1. MoveLayout - Should I move to the next print position on the page
2. NextRecord - Should I move to the next record when I'm done with this section
3. PrintSection - Should I print this detail section.

  By setting these to true / false, you can control how the report is being generated.

  Understanding these properties opens up a whole bunch of possibilities in reporting, so it's worthwhile to understand their use.

  For example, if you had a single record and wanted to produce many labels, you could do:

MoveLayout True, NextRecord False, PrintSection True

  until you had printed as many labels as you needed for the same record.  You could easily even add a "label count" field to the table and print a variable number of labels for each record in the table (rather then loading a table with dummy records).

  So with method two, your in the GroupFooter and it's getting ready to print.  By looking at the Top property, you can see at what point you are on the page.  If not far enough, you set the properties so that another detail section is printed.

  And then your in the group footer again.  If your now far enough, the footer is printed.

  Only real trick is calculating the point where you feel the footer should be printed.  Keep in mind that it is page size, less margins, less page footer, less size of the group footer.

HTH,
Jim.

 Actually, scratch method #2.  I just went back and looked at it and it doesn't repeat the detail section, but just skips the group footer down the page.

  Your stuck with method 3.  You need to advance down the page before you done with printing the detail sections.

Jim.

Jim, thanks for the detailed explanation.. That does help clear it up quite a bit for me.. I will look back into that and see if I can get it working.. I'll probably post some issues back here in a bit (as I tried this briefly earlier and wasn't getting the lines to print out, only the footer was moving down).

Oh, and also, These methods required me to add a module to the detail section, of which I already was calling out to some code.. Perhaps you could give me some input on how to merge the existing code I have in there with the code thats called out in these methods.. I'll repost that when i reach that point..

Leigh/Jim,

Thanks for picking up for me on this.  Got a short notice tasker at work and am not going to be able to break from that effort for the rest of the day.

Dale

Thanks for the update fyed.. I'm going to chug away at this for a bit using that method 3 that j has proposed..

Hey J, I got a chance to set up Method 3 using the example database Northwinds.. When I run this I'm seeing a few things that I have questions on. First, it isn't finishing off the lines through the remainder of the page when it finishes running all the details. Second, I really need a footer on every page regardless of whether that set of details continues onto the next page or not.
My footer information is generic for every page so it's not dependent on the group information.

I've requested that this question be closed as follows:

Accepted answer: 400 points for JDettman's comment http:/Q_27491949.html#37281298
Assisted answer: 100 points for fyed's comment http:/Q_27491949.html#37281421
Assisted answer: 0 points for SeyerIT's comment http:/Q_27491949.html#37291233

for the following reason:

A few different solutions for this here to choose from. The best for my particular situation was adding Me.MoveLayout = False &nbsp;to my Group Header On Format method.. I then increased the size of that group header to fill the page exactly to the group footer and filled it with the necessary gridlines.

SeyerIT,

  Please select your own last comment as answer.  

  While all the ideas presented got you pointed in the right direction, they were certainly not the solution.

  And to be sure it's clear;  you had a group header which had lines for the entire page (section was approx 11" long) and the only thing you did was do MoveLayout = False.

  That allowed the section to print on the entire page, but kept the layout at the top of the page for the printing of the next section.

  I've never heard of anyone doing that before and it's a valuable technique with pre-printed forms.

  I even learned something new; I never visualized that a section could address the entire page and then other sections could print on top of that.  Not sure why, but I always think of the report engine moving down the page section by section even though I know OnPage can address the entire page.

  So a very valuable technique indeed.  You certainly deserve the solution on this one.

Jim.

I would agree with Jim,  although i presented an option, it was by no means the solution you used.

Now I need to go back are reread this thread and test what you did, so I can remember it for the future.

Thanks Jim.. I just didn't want to cheat anyone out of any credit as the other answer were valuable as well.. In any case to clarify one thing, I am using the page header as my header information for the pages (as I can use it to display the correct information from my second group header) and then I'm just using group header for the gridlines. That way I have a good page header and then consistent gridlines on every page..

Here is a picture of the layout;

This was the best solution for the situation I have.

 Pictures worth a thousand words; thanks for the screenshot!

Jim.

 BTW, you really should do up an article on this.   In 19 years of Access development, I've never seen anyone do this, here or anywhere else.  This is a first.

 Let me know if you want to do up an article and I can get you started with that.

Jim.

Yeh that would be good.. It really was a revelation for me:) Now I just need to get the repeat section working properly.. I posted another question up on that. Was thinking I would like that to this depending on why it's actually not working right for me.

In any case I could create an article on it.. It would give me a chance to give a little back to the forums since I'm usually looking for answers :)

So let me confirm that the formatting in the ParentAssemblySeq Header section is sized to fill the entire page, but will not extend beyond the end of a page if there are details entered.  

I'm surprised that this works in group header, but it would make sense to put it in the group footer.

I think because I'm using the Page Header and Page Footer as my actual header and footers and then using the GroupHeader to underlay I can specify the height of all three and basically set a template to fill the page perfectly.. Then I adjust my details section height so that the lines in there fit over the grid I've created in the underlayed group header.

I'm thinking you could probably use either the group header or footer to underlay. Seems like it would work the same although I didn't try it yet.

SEYERIT: "Now I just need to get the repeat section working properly.. I posted another question up on that. Was thinking I would like that to this depending on why it's actually not working right for me."

I just deleted a question I had up on this as I solved this problem. Here is my adjusted method of getting the gridlines to print on EVERY page..

Use the GroupHeader as it was intended to be used and put my groupheader information there. Then I used my PageHeader section and underlayed it using the MoveLayout=False property. Then I just adjusted my gridlines so that they were not lined up with the top of the section until they were effectively starting right below the groupheader section.. Here is the layout:

 
And here is how it looks in print preview:
 

Oh, and then I was able to successfully set the groupheader Repeat Section to Yes and then that worked properly as well.

I'm not familiar with the MoveLayout property, what version of Access are you using?

Access 2010..

Here is the VBA

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)

Me.MoveLayout = False

End Sub

Select allOpen in new window

Learn something new every day.  

I went back to my Access 2000 Developers Handbook and looked it up, and it is in there, but the description of how to use it stinks.

Yeh, I'll say :). I was  very excited when I got this working.. I was getting a little nervous I wasn't going to get it setup the way I wanted it.

<<I went back to my Access 2000 Developers Handbook and looked it up, and it is in there, but the description of how to use it stinks.>>

 Really?  What page?  I've been over that book forwards and backwards and I've never seen that technique employed.

  I know they talk about Movelayout, etc, but I've never seen anyone format an entire page in the header and then not move the layout, overlaying the rest of the sections on what's already been rendered.

Jim.

Jim,

Well, by "it is in there" I meant that there was a reference to the property in the book (In the 2000 handbook, volume 1, it is referenced on pp 684-685).

By "the description of how to use it stinks", I meant that there was really no good explaination of how it works or how to use it.  This method seems a lot easier than trying to figure out how many additional empty rows to add to the query, which is what I have always done in the past.

https://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Windows/XP/A_8911-How-to-underlay-a-section-in-Microsoft-Access-Reports.html

Here is the link to the article.. Perhaps I should have referenced this thread in there?

Very nice, thanks for taking the time to write it up.

Jim.

No problem.. Although I didn't really get too detailed with it.. I assume people can ask questions on it if need be?

Seyer,

The only thing that has me confused is that in your last image, the lines appear to be spaced about 1/6" apart (6 lines per inch), but in your desired output, the sections appear to be about 1/2" in height, why all the extra lines, and are those all set to visible = false?

Sorry about that fyed.. I just realized that looking back that I posted the example from Form 1 of my report.. The example I started with was a screenshot of Form 2.. I went back to the article and posted the layout of Form 2..

great.  

Note that a page editor will be along on your article to help you with moving it to published status (that let's you correct errors, formatting, etc and gets another set of eyes on it), so no worries on the image or anything that might need to be cleaned up a bit (looks pretty good to me already!).

  I'm also the one that moved it from the OS zone to Access Reporting.

Jim.

Oh yeh, about the zone, The only two that appeared in my list was Windows and Mac.. I tried clicking on them and nothing else would come up.. Probably a user error on my part but thanks for moving it to the correct location...