Columns Revisited (Sort of…) (Access 2000)

Topic

New Reply

Reference post http://www.wopr.com/cgi-bin/w3t/showflat.p…=277356&fpart=1%5B/url%5D. I’m on to part 2 of my problem. Hans, the solution you provided in that post works great and is exactly what I needed. Now I am struggling with creating a six page report (I don’t want to say “based” on that first report) but rather mirrors it. Page one needs to have the same records and in the same order as column one on the original report. Page two needs to have the same records and in the same order as column two, etc. Both reports are based on the same query so the same records will be available. (The purpose of this report is as a grade sheet for each individual when they complete each event. So, the records will just be listed down the left side of the page.)

I’ve tried using page breaks but all I accomplished was either an 80 page report or a report with the same number of records on every page (which did not match the orginal report columns.)

I appreciate your help and I hope I’ve been more clear about what I’m asking for this time.

Reply | Quote

Replies

Hi jon,

It’s been a while. I will have to look at that old thread to refresh my memory. I’ll get back to you later.

Reply | Quote

Hi jon,

It’s been a while. I will have to look at that old thread to refresh my memory. I’ll get back to you later.

Reply | Quote

Jon,

I have taken the report from the final version I posted in the previous thread, and modified it to do what you want in this one. Obviously, the report doesn’t look the way you want it to look, but it is just meant to show how to distribute the records over the pages.

The code is somewhat simpler than in the previous thread. I have used a page break control that is made invisible (thereby inactive) at the top of each page, then made visible (thus active) when the required number of records for that page has been reached.

Reply | Quote

Hans,
Thanks for your help. That report appears to do exactly what I wanted it to do. However, I have noticed some strange behavior that I can’t quite figure out.

After the report opens and I click on the arrow at the bottom to go to the next page – the report works just like it is supposed to: It shows all 80 records in the proper format and on the correct page – just like I wanted. But when I click on the arrow|line to jump to the last page – the report eliminates 4 records after every 14th record. In other words, the first page shows the first 14 records, then the second page skips 4 records and shows 14 records, then the third page skips 4 and shows 14 records, etc, etc. If I close the report and reopen it, it is fine unless I jump to the last page.

Did I screw something up indavertently or has my installation gotten corrupted somehow? (BTW, I checked the underlying query in both situations and it showed all 80 records.)

Reply | Quote

I’m sorry, I can’t reproduce this behavior. I added some records in the version I posted yesterday, to make the query return 80 records. The report displays all 80, and it makes no difference whether I jump to the last page immediately, or page by page etc. I also tested with different paper sizes and section heights. The only problem I encountered occurs if the header and footer sections take up so much space that there is no room for 14 records on a page. The code has no check for that.

Does the report as I posted it exhibit the problem behavior on your PC?

Reply | Quote

Hi Hans,
To answer your question, I re-downloaded the file and tried it on my machine fresh. Similiar problem but different in that nothing happened if I tried to jump to the last page first. But then when I clicked through each page, the pages would stop on page 5 but I could continue clicking the arrow button – it did not gray out at the last page.

I then tried it on my laptop – same behavior. Laptop version Access 2000 (9.0.3821 SR-1), Desktop version Access 2000 (9.0.6926 SP-3).

This is the only report that does this so what I may do to work around this oddity is to not allow the users to preview it first – just print directly. Actually, what I need to do is figure out how to convert both reports to Word and then email the Word docs (automatically) to where they need to go (the gym).

In any case, thank you very much for your help on both of my problems!

Reply | Quote

Update –

Hans, I downloaded the attachment at work and it exibited the same strange behavior. I reread my initial post about this behavior and realized that I failed to mention that this oddity only occurs when you click the arrow button going to the last page before doing anything else after the report opens. When the report is closed and reopened, it works just like it is supposed to as long as you don’t go to the last page first. Very strange.

BTW, the machine at work is running NT with A2K.

Reply | Quote

I did try moving to the last page immediately after opening the report, before doing anything else. Makes no difference on my system. I just tried it on another PC, with a different printer; it works OK there too. I also tried changing paper size (A4 vs letter).

The only difference is that I’m using Access 2002; I don’t have Access 2000, but it seems improbable that this would be the cause. So I’m stumped on this one.

Reply | Quote

I do have Access 2000 on my machine, but I can’t make it jump to the last page! I can move one page at a time and I can jump to the first page, but the last page button doesn’t seem to work at all. That’s certainly behavior I’ve never seen before.

Reply | Quote

Charlotte,

Thanks for checking on Access 2000. That is very strange behavior indeed. I will see if I can find a different approach to accomplish what Jon wants; the present one clearly is problematic.

Reply | Quote

Hans – I thank you for your time that you have spent on this. I do appreciate it!

Charlotte – Thank you for verifying that I have not in fact gone insane! (At least, I don’t think I have.)

Reply | Quote

Hello Jon,

I hooked up my old Windows 95/Office 97 PC and the report’s behavior in Access 97 was very weird too. The culprit is a module-level variable I used to keep track of the record number on each page. Its value turned out to be inaccurate in Access 97, and I suppose in Access 2000 too. Perhaps I was lucky that it worked correctly on my Access 2002 systems (both at home and at work), or maybe Access 2002 does things differently. Instead of trying to get to the bottom of this, I decided to do away with the variable. The version attached to this post relies entirely on the hidden text box that keeps track of the overall record number; I am hopeful that it is more reliable. It works OK for me in Access 97 and in Access 2002. Holler if it still doesn’t work correctly in Access 2000.

Note: if you get an empty page after each filled one, check paper size and page margins. Different printers have different maximum printable areas.

Reply | Quote

Hey, Hans!
This is no joke – let me know the next time you’re in the Chicago area and I’ll buy you dinner!! The fixed version works beautifully. Thank you very much for helping me with this.

Reply | Quote

Hey, Hans!
This is no joke – let me know the next time you’re in the Chicago area and I’ll buy you dinner!! The fixed version works beautifully. Thank you very much for helping me with this.

Reply | Quote

Hi, Hans.
You provided me with this solution several months ago and it worked just fine until the powers that be decided they wanted a change. I was able to figure out how to make the changes to make the report appear they way they want it to but now I’m requesting a little education about how part of this works – when you get a few moments.

I know MOD is the remainder so the select case takes the remainder from the division. So if there are two extras, it will use case 2. What I have not been able to figure out how it works is the numbers after the equals (=) sign. Will you explain to me what these numbers are doing? (It took me some time experimenting to stumble upon the solution I was looking for.)

I have attached the underlying module for this report showing my changes and your original numbers remmed out. (Everything is working fine – all I’m asking for is an education.)

Thank you very much.

Reply | Quote

You want the records to be distributed over six pages in an irregular way. Since the records have to fit on 6 pages, the remainder of the number of records after division by 6 plays a role.

Say that there are 82 recruits. If we divide 82 by 6, we get a quotient of 13 with a remainder of 4. This means that we can place 13 records on each page, then have to decide where to place the 4 remaining ones. For each page, we can decide whether or not to put an extra record there.

In the On Open event of the report, the “fixed” number of records per page is calculated and stored in the variable intRowsPerPage. In the example above, it is 13. The intExtras array holds the cumulative count of extra records that is printed. In the example above, the elements of this array in the cody you attached are 0, 0, 1, 2, 3, 4. This means that the first two pages will have NO extra records above the fixed number of 13, and the subsequent pages will each have an extra record, causing the cumulative count of extra records increase from 1 to 4.

The check for the number of records per page is carried out in the On Format event of the detail section. The test

If Me.txtCounter = Me.Page * intRowsPerPage + intExtras(Me.Page) And Me.txtCounter < intTotalRecs Then
Me.pgBreak.Visible = True
End If

makes the page break visible once the number of records has reached the required count, calculated as (page number)*(fixed number of records per page) + (cumulative number of extra records)

Reply | Quote

Thanks for the lesson, Hans.

I understand everything except I’m having a little difficulty the concept of “cumulative.” I thought that meant something along the lines of successive addition so I would expect page 6 to try to spit out 10 (1+2+3+4) extra records. Obviously, that didn’t happen so why is my thinking wrong?

Thanks for your time.

Reply | Quote

The numbers have already been added in the array.

Reply | Quote

The numbers have already been added in the array.

Reply | Quote

Thanks for the lesson, Hans.

I understand everything except I’m having a little difficulty the concept of “cumulative.” I thought that meant something along the lines of successive addition so I would expect page 6 to try to spit out 10 (1+2+3+4) extra records. Obviously, that didn’t happen so why is my thinking wrong?

Thanks for your time.

Reply | Quote

You want the records to be distributed over six pages in an irregular way. Since the records have to fit on 6 pages, the remainder of the number of records after division by 6 plays a role.

Say that there are 82 recruits. If we divide 82 by 6, we get a quotient of 13 with a remainder of 4. This means that we can place 13 records on each page, then have to decide where to place the 4 remaining ones. For each page, we can decide whether or not to put an extra record there.

In the On Open event of the report, the “fixed” number of records per page is calculated and stored in the variable intRowsPerPage. In the example above, it is 13. The intExtras array holds the cumulative count of extra records that is printed. In the example above, the elements of this array in the cody you attached are 0, 0, 1, 2, 3, 4. This means that the first two pages will have NO extra records above the fixed number of 13, and the subsequent pages will each have an extra record, causing the cumulative count of extra records increase from 1 to 4.

The check for the number of records per page is carried out in the On Format event of the detail section. The test

If Me.txtCounter = Me.Page * intRowsPerPage + intExtras(Me.Page) And Me.txtCounter < intTotalRecs Then
Me.pgBreak.Visible = True
End If

makes the page break visible once the number of records has reached the required count, calculated as (page number)*(fixed number of records per page) + (cumulative number of extra records)

Reply | Quote

Hi, Hans.
You provided me with this solution several months ago and it worked just fine until the powers that be decided they wanted a change. I was able to figure out how to make the changes to make the report appear they way they want it to but now I’m requesting a little education about how part of this works – when you get a few moments.

I know MOD is the remainder so the select case takes the remainder from the division. So if there are two extras, it will use case 2. What I have not been able to figure out how it works is the numbers after the equals (=) sign. Will you explain to me what these numbers are doing? (It took me some time experimenting to stumble upon the solution I was looking for.)

I have attached the underlying module for this report showing my changes and your original numbers remmed out. (Everything is working fine – all I’m asking for is an education.)

Thank you very much.

Reply | Quote

Hello Jon,

I hooked up my old Windows 95/Office 97 PC and the report’s behavior in Access 97 was very weird too. The culprit is a module-level variable I used to keep track of the record number on each page. Its value turned out to be inaccurate in Access 97, and I suppose in Access 2000 too. Perhaps I was lucky that it worked correctly on my Access 2002 systems (both at home and at work), or maybe Access 2002 does things differently. Instead of trying to get to the bottom of this, I decided to do away with the variable. The version attached to this post relies entirely on the hidden text box that keeps track of the overall record number; I am hopeful that it is more reliable. It works OK for me in Access 97 and in Access 2002. Holler if it still doesn’t work correctly in Access 2000.

Note: if you get an empty page after each filled one, check paper size and page margins. Different printers have different maximum printable areas.

Reply | Quote

Hans – I thank you for your time that you have spent on this. I do appreciate it!

Charlotte – Thank you for verifying that I have not in fact gone insane! (At least, I don’t think I have.)

Reply | Quote

Charlotte,

Thanks for checking on Access 2000. That is very strange behavior indeed. I will see if I can find a different approach to accomplish what Jon wants; the present one clearly is problematic.

Reply | Quote

I do have Access 2000 on my machine, but I can’t make it jump to the last page! I can move one page at a time and I can jump to the first page, but the last page button doesn’t seem to work at all. That’s certainly behavior I’ve never seen before.

Reply | Quote

I did try moving to the last page immediately after opening the report, before doing anything else. Makes no difference on my system. I just tried it on another PC, with a different printer; it works OK there too. I also tried changing paper size (A4 vs letter).

The only difference is that I’m using Access 2002; I don’t have Access 2000, but it seems improbable that this would be the cause. So I’m stumped on this one.

Reply | Quote

Update –

Hans, I downloaded the attachment at work and it exibited the same strange behavior. I reread my initial post about this behavior and realized that I failed to mention that this oddity only occurs when you click the arrow button going to the last page before doing anything else after the report opens. When the report is closed and reopened, it works just like it is supposed to as long as you don’t go to the last page first. Very strange.

BTW, the machine at work is running NT with A2K.

Reply | Quote

Hi Hans,
To answer your question, I re-downloaded the file and tried it on my machine fresh. Similiar problem but different in that nothing happened if I tried to jump to the last page first. But then when I clicked through each page, the pages would stop on page 5 but I could continue clicking the arrow button – it did not gray out at the last page.

I then tried it on my laptop – same behavior. Laptop version Access 2000 (9.0.3821 SR-1), Desktop version Access 2000 (9.0.6926 SP-3).

This is the only report that does this so what I may do to work around this oddity is to not allow the users to preview it first – just print directly. Actually, what I need to do is figure out how to convert both reports to Word and then email the Word docs (automatically) to where they need to go (the gym).

In any case, thank you very much for your help on both of my problems!

Reply | Quote

I’m sorry, I can’t reproduce this behavior. I added some records in the version I posted yesterday, to make the query return 80 records. The report displays all 80, and it makes no difference whether I jump to the last page immediately, or page by page etc. I also tested with different paper sizes and section heights. The only problem I encountered occurs if the header and footer sections take up so much space that there is no room for 14 records on a page. The code has no check for that.

Does the report as I posted it exhibit the problem behavior on your PC?

Reply | Quote

Hans,
Thanks for your help. That report appears to do exactly what I wanted it to do. However, I have noticed some strange behavior that I can’t quite figure out.

After the report opens and I click on the arrow at the bottom to go to the next page – the report works just like it is supposed to: It shows all 80 records in the proper format and on the correct page – just like I wanted. But when I click on the arrow|line to jump to the last page – the report eliminates 4 records after every 14th record. In other words, the first page shows the first 14 records, then the second page skips 4 records and shows 14 records, then the third page skips 4 and shows 14 records, etc, etc. If I close the report and reopen it, it is fine unless I jump to the last page.

Did I screw something up indavertently or has my installation gotten corrupted somehow? (BTW, I checked the underlying query in both situations and it showed all 80 records.)

Reply | Quote

Jon,

I have taken the report from the final version I posted in the previous thread, and modified it to do what you want in this one. Obviously, the report doesn’t look the way you want it to look, but it is just meant to show how to distribute the records over the pages.

The code is somewhat simpler than in the previous thread. I have used a page break control that is made invisible (thereby inactive) at the top of each page, then made visible (thus active) when the required number of records for that page has been reached.

Reply | Quote