my own page numbering?

Topic

New Reply

Hi,

I am working in Access 97.

I am creating a report based on an Employee table. I’ve created a query for the table since only reports are to be generated only for active employees (non-active ones are still in the Employee table). Each employee is to begin a new page. I have that down – no problem.

The problem is with Access’s numbering of pages. When creating the Report with the Wizard, Access creates a “page of pages” footer.

This may be problematic for the employees. Employee A sees his report as “1 of 5”; Employee B sees her report as, say, “2 of 5”. Etc. So Employee A may ask “where’s the other 4 pages of the 5?” B may ask why her report starts at page 2. So I’m trying to avoid this.

Even if I leave out the [Pages] part, a particular employee’s report, except the first, won’t start at page 1.

So I’m searching for another approach. Ideally, each employee would see “1 of n” and continuing for as many pages as needed for that employee. The next employee would start over at “1 of m”. Next step down in preference would just provide for page numbering as “1”, “2”, etc with no “of n”.

I’ve tried some code for the latter. On Report Open, I set a variable SaveEmployee = “noEmployee”. Then On Page Footer, I examine if EmployeeName (the field from the underling query) = SaveEmployee.
– if so, I increment the page number
– if not, I set the page number = 1 and update SaveEmployee
In either case, I set a control in the footer to my page number as part of the On Footer macro.

The above seems to work as far as the numbering goes – it starts at 1 for each employee. However, since the report isn’t fleshed out yet (just has a group header for the employee name and his/her start date), I can’t tell what will happen if the report goes to page 2. So I just put in some labels and repeated the employee name as a text box in the detail section. What I’m getting is a blank page 1 as far as the report content goes. My labels and text box come out on page 2. When I start a new employee, same thing.

I also thought about accessing the records of the table directly from within a loop and opening the report with a Where string constructed for each employee. I haven’t tried this yet. This would seem like it should solve all the problems and even give me the “1 of n” and start over for each employee. But I do have concerns about the speed of this as opposed to handling this through the query based on the table.

Thoughts on any of the above or other approaches to getting the page #s “right”?

Thanks.

Fred

Reply | Quote

Replies

[indent]

---

Next step down in preference would just provide for page numbering as “1”, “2”, etc with no “of n”.

---

[/indent] I believe setting Group Properties as in the attached example and a text box in the Page Footer containing =”Page ” & [Page] will achieve your second preference. I’m watching for a solution to your first preference.

Reply | Quote

There are samples in the Solutions database for each version of Access that demonstrate custom “Page 1 of N” numbering for grouped reports. Of course, they depend of set the report to insert a new page each group. The Solutions database for 2000 is on the CD, but I believe you had to download it for 97.

Reply | Quote

The samples are on my copy of the Office97 Pro CD.

Reply | Quote

There’s also a sample reports database you can download from Microsoft that wasn’t on the CD.

Reply | Quote

Charlotte,

Thks for the tip. I’m familiar with the Solutions sample DB and have it. In fact,I had looked at it for other things but didn’t notice this. I’ll take another look. Thks.

Fred

Reply | Quote

I could be misremembering. It’s possible that the example is in the sample reports database you can download from MS. The filenames are RptSmp00.exe and RptSmp97.exe. I don’t know where they have the 97 version stashed anymore. The last time I found it, it was in a virtual directory somewhere on the MS site.

Reply | Quote

Charlotte,

I’ve now looked at both the Solutions and Northwind DBs as well as the rptsmp00 file on the MSKB site. The first 2 DBs didn’t have anything at all on the topic. The MSKB file had an example of prompting for the starting page# of a rpt when other than 1. This is not what I’m after.

Perhaps I will need to go thru the table of employees in code and call the report with a Where string for each employee. I saw an example of a similar but not-quite approach in the Solutions DB where a rpt could be called for all items in a category (all pages numbered from 1 to the end with “of n” on each page) or just for a specific item which is selected from a list box.

My appl is similar. I have a bunch of employees. In one instance, I have to print a report for all employees. In another instance (using the Solutions as a guide), I only need to print a specific employee. In the former case, I prefer each employee to begin with a pg1 and “of n” to be just for that employee. I can get the pg1 part with code but can’t get the “of n” just for him/her.

My 2 fallbacks are:
– forget the “of n” but this is still a problem per previous email (get no details on page 1)
– use code to go thru the table of employees and open the report with a Where constructed for each employee.

Any other thoughts? Thks.

Fred

Reply | Quote

Brian,

Thks for the response. In fact, I had my group settings for the employee group pretty much as you suggested. Just didn’t have a footer. But this did not work (yet). Recall that I made up some sample data to see what happens when I have info that forces a second page. Problem was that the first page ends up with just the employee name and hire date and a page footer at the bottom. Rest of the page is blank. The data comes out on page 2. Definitely an undesired result. Maybe with real data it will work but not sure why it should.

Thks for the suggestion. I’m going to try Charlotte’s suggestion and check out the custom 1 of N in the Solutions DB.

Fred

Reply | Quote