Hello all,
I have a rather baffling problem with a report in Access 2013. I work for a small fashion house in England, and I mainly work with SQL Server to collect data for the company websites. However, in this instance, I am working with Access on an internal report for our Production and Buying department.
The report that exhibits the strange printing behaviour is one I built. It displays pictures for items sold, and for each it lists number sold, number returned, percent of overall sales for season, etc. It is populated by a SQL Server stored procedure, and runs through an .accdb program that contains just a parameter screen and the one report.
The parameter screen has combo boxes for Season (Autumn Winter 2014 is the current default), Department, Category, Supplier, Sort by (Revenue, Units Sold, Style/Colour), Story (theme for the season), and number of Pages to Show (‘ALL’, or ‘1’ through to ‘10’). There are also date pickers for Season Start and End dates, and Reporting Period Start and End dates. When I press the View Report button at the bottom of the parameter screen, the values in the combo boxes and date pickers are collected and passed through to the SQL Server stored procedure. This then returns the data and populates the report. So far so good!
The report was built a couple of months ago, and worked well until the customer decided it was taking too long to run sometimes, and noticed that if the report ran to 100 pages say, then on the last few pages the pictures didn't show. We attributed this to a lack of memory, so I added the Pages to Show parameter as a way of speeding the running of the report, and hopefully preventing the situation of pictures failing to appear. Furthermore, the customer only wanted to see the first couple of pages most of the time anyway, so it was decided to allow them to decide on the number of records to display in the report.
Regarding printing behaviour, prior to adding the new parameter the report printed correctly. When it was open, if I pressed the Print button at the top left of the screen the print dialog was displayed, and once I had selected the number of pages to print, they printed, no problem!
Since adding the new parameter however, things have changed somewhat, but only under certain circumstances. Inside the stored procedure, if 'ALL' is passed in then it is ignored and no limit is placed on the number of records returned. However, if the value passed in is a numeric string between '1' and '10', then it's converted from a string to a number and used to limit the number of records returned. We display nine records per page in the report, so whatever number the user selects is multiplied by nine in the stored procedure, and that is the limit placed on the number of records returned, e.g. user selects ‘3’, so 27 records are returned, thus exactly filling 3 pages.
This works well when run in SQL Server, and it has proven to be accurate and reliable. It also works well in the Access report in that it reduces the time taken to run the report, and accurately limits the number of pages displayed. However, since adding this extra parameter to the parameter screen, the printing function has exhibited very strange behaviour.
If I leave the Pages to Show at its default setting of 'ALL' and run the report, when the report is open, if I press the Print button at the top left of the screen, the print dialog opens, and after I have selected the number of pages to print, they print as expected. However, if I change the value in the Pages to Show combo box then something very odd happens. The report opens with the correct number of pages according to the number selected in the Pages to Show combo box, but if I press the Print button at the top left of the screen, the print dialog opens for me to select the number of pages as usual, and once I have selected this and pressed the Print button at the bottom of the dialog, it tries to print a screenshot of the parameter screen!! ?????
I have never heard of this behaviour before, and I’ve tried all sorts of things to overcome it. I wondered if it had something to do with having ‘ALL’ as the default value for the Pages to Show. So I altered it to have a default of ‘1’, but it made no difference. As long as I don’t change the default value in that combo box then the report runs and prints as normal, but if I change the value in the Pages to Show combo box, then the program tries to print the parameter screen.
I tried setting the report to be modal and a pop up, but this didn't do any good either, and I even tried unloading the parameter screen while the report is open, but if I do that, then when I navigate away from the first page, various bits of information that were displayed in the page header section disappear, leaving '#name?' in their place. So the parameter screen has to stay loaded while the report is open in order to continue showing the header information from page to page.
I can't think of anything else I could do, and it's driving me nuts! :banghead:
So my question is this, has anyone else ever come across this before, and if so do you know of a resolution for the problem, or a workaround that would allow the report to print instead of the parameter screen?
Best regards,
BobHUK
I have a rather baffling problem with a report in Access 2013. I work for a small fashion house in England, and I mainly work with SQL Server to collect data for the company websites. However, in this instance, I am working with Access on an internal report for our Production and Buying department.
The report that exhibits the strange printing behaviour is one I built. It displays pictures for items sold, and for each it lists number sold, number returned, percent of overall sales for season, etc. It is populated by a SQL Server stored procedure, and runs through an .accdb program that contains just a parameter screen and the one report.
The parameter screen has combo boxes for Season (Autumn Winter 2014 is the current default), Department, Category, Supplier, Sort by (Revenue, Units Sold, Style/Colour), Story (theme for the season), and number of Pages to Show (‘ALL’, or ‘1’ through to ‘10’). There are also date pickers for Season Start and End dates, and Reporting Period Start and End dates. When I press the View Report button at the bottom of the parameter screen, the values in the combo boxes and date pickers are collected and passed through to the SQL Server stored procedure. This then returns the data and populates the report. So far so good!
The report was built a couple of months ago, and worked well until the customer decided it was taking too long to run sometimes, and noticed that if the report ran to 100 pages say, then on the last few pages the pictures didn't show. We attributed this to a lack of memory, so I added the Pages to Show parameter as a way of speeding the running of the report, and hopefully preventing the situation of pictures failing to appear. Furthermore, the customer only wanted to see the first couple of pages most of the time anyway, so it was decided to allow them to decide on the number of records to display in the report.
Regarding printing behaviour, prior to adding the new parameter the report printed correctly. When it was open, if I pressed the Print button at the top left of the screen the print dialog was displayed, and once I had selected the number of pages to print, they printed, no problem!
Since adding the new parameter however, things have changed somewhat, but only under certain circumstances. Inside the stored procedure, if 'ALL' is passed in then it is ignored and no limit is placed on the number of records returned. However, if the value passed in is a numeric string between '1' and '10', then it's converted from a string to a number and used to limit the number of records returned. We display nine records per page in the report, so whatever number the user selects is multiplied by nine in the stored procedure, and that is the limit placed on the number of records returned, e.g. user selects ‘3’, so 27 records are returned, thus exactly filling 3 pages.
This works well when run in SQL Server, and it has proven to be accurate and reliable. It also works well in the Access report in that it reduces the time taken to run the report, and accurately limits the number of pages displayed. However, since adding this extra parameter to the parameter screen, the printing function has exhibited very strange behaviour.
If I leave the Pages to Show at its default setting of 'ALL' and run the report, when the report is open, if I press the Print button at the top left of the screen, the print dialog opens, and after I have selected the number of pages to print, they print as expected. However, if I change the value in the Pages to Show combo box then something very odd happens. The report opens with the correct number of pages according to the number selected in the Pages to Show combo box, but if I press the Print button at the top left of the screen, the print dialog opens for me to select the number of pages as usual, and once I have selected this and pressed the Print button at the bottom of the dialog, it tries to print a screenshot of the parameter screen!! ?????
I have never heard of this behaviour before, and I’ve tried all sorts of things to overcome it. I wondered if it had something to do with having ‘ALL’ as the default value for the Pages to Show. So I altered it to have a default of ‘1’, but it made no difference. As long as I don’t change the default value in that combo box then the report runs and prints as normal, but if I change the value in the Pages to Show combo box, then the program tries to print the parameter screen.
I tried setting the report to be modal and a pop up, but this didn't do any good either, and I even tried unloading the parameter screen while the report is open, but if I do that, then when I navigate away from the first page, various bits of information that were displayed in the page header section disappear, leaving '#name?' in their place. So the parameter screen has to stay loaded while the report is open in order to continue showing the header information from page to page.
I can't think of anything else I could do, and it's driving me nuts! :banghead:
So my question is this, has anyone else ever come across this before, and if so do you know of a resolution for the problem, or a workaround that would allow the report to print instead of the parameter screen?
Best regards,
BobHUK
