Print Preview Requries before displaying

[GriffinW]

I am using Access 2010, SP2. Both I and all users are using Windows XP. (yeah, I know...)

I've got an unbound form that has a subform bound to a query. The subform is a continuous form with a dozen or so columns. There are some filter selections (comboBoxes) on the Main form that filters the data in the subform. I do this by changing the QueryDef for the query based on the combobox items the user selects, and requery the subform whenever the user changes one of the comboBoxes. So far, everything works great. When I print the report, it comes out the same as the form.

(More info in case it's relevant: The query is now actually a Stored Procedure on a SQL Server, but the process is still the same.)

I want to use Print Preview instead of Print so that the user can see the report first, adjust margins, scaling, etc. based on the printer where the report is going to print. This also gives them the option to print it to a PDF. All of this is built in to the Access Print Preview ribbon.

So, here's the problem: Unlike the print function, Print preview does a requery on the subform before it pulls up the preview. This is only annoying in most cases, as most users using this project are remote to the server, and the requery means it is slower than the print function. But, in additon to the filters (comboboxes) I have provided, users can use the built-in filters that Access provides (right-click on the column) to further filter or sort a particular column to get just the data they need. But, when Print Preview requeries, it does not use the filters/sort the user has applied, only the QueryDef as it was when I set it based on the main form's comboBoxes. Typically, using the ComboBoxes, they may filter the data they need down to 100 records or so, but then they can filter to some very specific records, say 5-10 using the Access filters on the columns, sorted however they want, but when they choose Print Preview (from a button I have on the Main form), it requeries from the QueryDef, ignores their filters/sorts and shows the full 100 records, sorted in the default order. Going right to Print doesn't do this, but also doesn't give them the options that Print Preview gives them.

Perhaps this is normal behavior, but it is making it very hard to allow the user to customize the report. Right now, most are taking screenshots of the form and using that, but that doesn't give them the other options that Preview gives them (and frankly it's kind of tacky to have to do that). I don't want to have to write VBA code to try to replicate all the options that the built-in Access filters provide.

I have tried to set the Print and Print Preview buttons using a macro, and with VBA, the results are the same. The VBA code I am using for Print Preview is:

DoCmd.RunCommand acCmdPrintPreview

Is there some way to stop Print Preview from requerying, or at least to use any filters or sorts the user has used in the form before doing the Print Preview?

My company will be upgrading everyone to Access 2013 and Windows 7 over the next couple of months, if this issue is resolved there, perhaps we can live with it until then, but I don't have a copy of 2013 yet (or Windows 7 on my work PC) to test.

Thanks for all help.

Griffin

 

[MarkK]

Hello and welcome to the forum

Unlike the print function, Print preview does a requery on the subform before it pulls up the preview.

Are you printing the Form? How does preview on a report requery a subform? Is that a typo?

If not, you should be printing a report, not a form.

 

[GriffinW]

Re: Hello and welcome to the forum

Are you printing the Form? How does preview on a report requery a subform? Is that a typo?

If not, you should be printing a report, not a form.

Thanks for your response.

This is not a report, it is a form. Yes, I have reports to print this info, and I never intended this selection list (it is designed to show a list of items, then the user clicks on the item to see full detail in another form) but some users want a report that looks exactly like the selection list (subform), including the ability to use Access filters and sorts on the continuous form, and then print it out, without having to take a screenshot of it.

If there is a way to take the results of the users' custom filters and sorts from this subform, and save that in a query to use for a report, then that would work too, but I was hoping there was a way to just have the preview NOT requery the form, the way that regular Print does not requery the form - that wold be a lot simpler.

I do have reports that first go to a custom form where the users can make selections, that builds the SQL query from that info, and uses it to print the report, but it is not as flexible as the selection criteria that they can use on a continuous form. They wanted the report to have the same selection and sort capabilties as the form, so I thought, Just print out the form, no problem, and going right to print does that, but Print Preview gives them more control of the print process, and lets them preview the report first (obviously). But Print Preview first dumps any filters and sorts the user applied to the form, and prints the entire list from the original query used on the subform.

I'm trying not to attempt to write a bunch of VBA to do the same thing that Access provides automatically on continuous forms.

Thanks again for your comments.

Griffin

 

[GriffinW]

One more thing to add to this: The users that are using this Access database have been putting this data in Excel Spreadsheets for years and are used to being able to use the filters and sorts in Excel to customize the data, and they want to have the same capabilities in this project. The took right to the Access filters and sort (right-click on a continuous form) capabilities, but when they went to print it, it wouldn't keep their selections. So I am just trying to give them that capability.

Any alternate suggestions on ways to do this are welcome!

Griffin

 

[sumox]

HEy sorry to disturb you

I am beginner so my solutions are kiddie type but
you may find interesting.
You should create New Query for Report copying the Query of the form
which u use in Mainform ComboxBox

Now In Deisgn view of Query(for report copy version) choose a field which
u think is common/unique for printing report based on this field/ report will group/collect

Now below this Unique field there are filter textboxes type -
Forms!MyUnboundform!DeComboBox

Now save it name as- "QRYforReport"

Now goto Reports-- Create report by wizard

Choose your QRYforReport query and then complete the operation

After this Place a Button on MyUnboundMain form in design mode
- from toolbar select Cmd button icon
Report Operati.---> Preview Report
Select QRYforReport Report named or anywhat named by you
Complete the process

Now File--Save.

Now see in form view

Now do what you want.....

Now Click on Command button newly created

are you satisfied?

 

[Uncle Gizmo]

HEy sorry to disturb you

Due to a bug in the forum software this message was "unapproved" (hidden) for some considerable time. I have just approved it. I hope no one has been inconvenience too much! The new forum software no longer has this bug, so this problem should not reoccur.