Access Report - Fill gap between end of lines of detail and report footer
Report is listing jobs by employee to do for week and has columns across the page with the days of the week for employees to fill in hours against each job. Each text box has a solid border so a grid is printed. At the bottom of the page there is a footer with spaces to total the hours for each day.
The report is usually 2-3 pages for each employee. They get one off jobs which they manually enter on the sheet and it would be good to extend the grid so they use the rest of the page.
Report works fine but finishes depending on the number of jobs on the last page for each employee.
I am trying to get the footer to print at the bottom of the page and for the gap between the end of the detail line and the top of the footer to be filled with a grid so additional jobs can be entered. This could be repeating a line of blank text boxes or drawing lines on the report.
I can draw the lines etc just need to know how to work out the gap and which event to use to draw the lines.
I have been thinking of adding an additional footer and setting the hieght of this based on the bottom of the detail section and top of the footer section. I think I need to set a report variable for the top of these when each is formated?. but not sure which events to use.
Appreciate any ideas.
The report is usually 2-3 pages for each employee. They get one off jobs which they manually enter on the sheet and it would be good to extend the grid so they use the rest of the page.
Report works fine but finishes depending on the number of jobs on the last page for each employee.
I am trying to get the footer to print at the bottom of the page and for the gap between the end of the detail line and the top of the footer to be filled with a grid so additional jobs can be entered. This could be repeating a line of blank text boxes or drawing lines on the report.
I can draw the lines etc just need to know how to work out the gap and which event to use to draw the lines.
I have been thinking of adding an additional footer and setting the hieght of this based on the bottom of the detail section and top of the footer section. I think I need to set a report variable for the top of these when each is formated?. but not sure which events to use.
Appreciate any ideas.
ASKER
OK in reply:-
I was using the page header with no report header- I get 26 lines on a page with no footer - The footer will take up the space of 4 lines - so up to 22 on page with group/page footer. (Every 2nd or third page)
I was using the page header with no report header- I get 26 lines on a page with no footer - The footer will take up the space of 4 lines - so up to 22 on page with group/page footer. (Every 2nd or third page)
Basically, you need to set up the Report so that every page has the exact same structure.
In my case I also had to move Group Header info to the page header.
Here is a sample, let me know if this is what you are after.
JeffCoachman
Access-EEQ-24702767-PaddingFillR.mdb
In my case I also had to move Group Header info to the page header.
Here is a sample, let me know if this is what you are after.
JeffCoachman
Access-EEQ-24702767-PaddingFillR.mdb
ASKER
Jeff,
Thanks for this - Your orders report is what I am looking for. Putting group header info in page header no problem but I do need a footer on the last page of each group rather than the next page as you have it. If each page must have the same number of records I could put this in the page footer on all pages and make not visible on the say the first 2 pages of 3 of the group.
Thanks
Don.
Thanks for this - Your orders report is what I am looking for. Putting group header info in page header no problem but I do need a footer on the last page of each group rather than the next page as you have it. If each page must have the same number of records I could put this in the page footer on all pages and make not visible on the say the first 2 pages of 3 of the group.
Thanks
Don.
<I do need a footer on the last page of each group rather than the next page as you have it.>
This is exactly what my report does.
Please examine the sample again a bit more closely.
Note the number of records in each group in the Orders table and in the Temp Orders table.
33 records will fit on a page.
So if a group has 29 records, 4 "Fake" records will be generated (33 records total) and the group total is on the bottom of the same page. (Not on the "Next" page as you state)
If a group has 37 records then 29 "fake" records a generated (two pages worth of records, 66 records).
The group total is on the end of the second page for the group.
(Again, not the "Next" page)
So the group total will always be on the end of the last page in the group.
I don't understand what you mean when you say that the total is on the "Next" page.
JeffCoachman
This is exactly what my report does.
Please examine the sample again a bit more closely.
Note the number of records in each group in the Orders table and in the Temp Orders table.
33 records will fit on a page.
So if a group has 29 records, 4 "Fake" records will be generated (33 records total) and the group total is on the bottom of the same page. (Not on the "Next" page as you state)
If a group has 37 records then 29 "fake" records a generated (two pages worth of records, 66 records).
The group total is on the end of the second page for the group.
(Again, not the "Next" page)
So the group total will always be on the end of the last page in the group.
I don't understand what you mean when you say that the total is on the "Next" page.
JeffCoachman
ASKER
Thanks Jeff,
I cannot see the code on your report - Cannot get to design view and code "Unviewable" - not sure if this was intentional or to do with my set up or the way I imported.
When I view the report the first page has 33 records for Argentina on the first page and on the second page totals - frieght of $400. THen page 3 and 4 are Austria with a total of $7391 on page 5. THis may have to do with paper size?. Anyway not to worried about this - If I could see your code for the lines I am sure I can work through.
Thanks
Don
I cannot see the code on your report - Cannot get to design view and code "Unviewable" - not sure if this was intentional or to do with my set up or the way I imported.
When I view the report the first page has 33 records for Argentina on the first page and on the second page totals - frieght of $400. THen page 3 and 4 are Austria with a total of $7391 on page 5. THis may have to do with paper size?. Anyway not to worried about this - If I could see your code for the lines I am sure I can work through.
Thanks
Don
<This may have to do with paper size?>
Yes, because it displays fine for me.
Remember that I am in the US, and my margins are .25 inches all around (top, bottom, left, and right).
And my paper size is 8.5 inches by 11 inches.
Here is a new sample set up for A5 Paper, that fits 22 records per page as you specified.
(margin set to about 7.62mm or .762cm)
Try it first, and see if it displays OK.
If not, then Preview the Report, and while the report is in Preview on the screen, set the margins to about 7.249mm or .7249cm
...and it should display just fine...
If not, then it should if you play around with the margins it should display correctly.
New sample and verification screenshots are attached.
I posted an MDE file to save space, and just to see if it was acceptable.
If you tell me this revised sample is acceptable now, I will post the MDB file.
JeffCoachman
Access-EEQ-24702767-PaddingFillR.mdb
untitled1.JPG
untitled2.JPG
Yes, because it displays fine for me.
Remember that I am in the US, and my margins are .25 inches all around (top, bottom, left, and right).
And my paper size is 8.5 inches by 11 inches.
Here is a new sample set up for A5 Paper, that fits 22 records per page as you specified.
(margin set to about 7.62mm or .762cm)
Try it first, and see if it displays OK.
If not, then Preview the Report, and while the report is in Preview on the screen, set the margins to about 7.249mm or .7249cm
...and it should display just fine...
If not, then it should if you play around with the margins it should display correctly.
New sample and verification screenshots are attached.
I posted an MDE file to save space, and just to see if it was acceptable.
If you tell me this revised sample is acceptable now, I will post the MDB file.
JeffCoachman
Access-EEQ-24702767-PaddingFillR.mdb
untitled1.JPG
untitled2.JPG
ASKER
Jeff,
Thank for further info - Yes revised mde shows the same as your screenshots regarding page footer so perfect for what I am looking for - Appreciate it if you could supply code to do this.
Regards,
Don.
Thank for further info - Yes revised mde shows the same as your screenshots regarding page footer so perfect for what I am looking for - Appreciate it if you could supply code to do this.
Regards,
Don.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Jeff,
Thanks for this.
Principle seems to be to create a temporary table and insert blank lines to print on report to match number of records on page. I'll have a go at adapting my one to do this.
I am thinking from this that I can work out the number of records for each page and allow less for the last page which would include a footer and insert the appropriate number. Also that on open I can insert records in tblOrdersTemp as you have done and then count the records for each group (Country) with "select count(OrderID) as Count from tblOrdersTemp group by ShipCountry" and insert number required from there.
Anyway I have enough to finish - thanks for help.
Thanks for this.
Principle seems to be to create a temporary table and insert blank lines to print on report to match number of records on page. I'll have a go at adapting my one to do this.
I am thinking from this that I can work out the number of records for each page and allow less for the last page which would include a footer and insert the appropriate number. Also that on open I can insert records in tblOrdersTemp as you have done and then count the records for each group (Country) with "select count(OrderID) as Count from tblOrdersTemp group by ShipCountry" and insert number required from there.
Anyway I have enough to finish - thanks for help.
Great,
...and thanks for the interesting and thought provoking question.
;-)
JeffCoachman
...and thanks for the interesting and thought provoking question.
;-)
JeffCoachman
This is so that every page can fit the exact same amout of records.
Then tell me how many record can fit on a page.