I might be missing something, but I'm finding it very difficult to work with reports in Access. I have searched extensively, but found no clear answer.

I need to be able to change Sort/ Grouping Order dynamically, but it seems that if I try to do this through VBA, it won't let me do so before I open the report because its not open. If I do it after it is open, then its too late. There seems to a be possibility of opening it in design mode, but that seems irrelevant to the user.

I would also need the option of Print Preview and Print. The only way I seem to be able to do this is to have separate command buttons for each which call separate code. I seem unable to bring up the preview and then if the user wishes, then print.

Any helpful pointers would be warmly welcomed.

Thanks,

Chris.

Try putting the Sorting/Grouping VBA code into the form's Open event.

You need to change the report sorting/grouping in the report's On Open Event. In this earlier post is an example of how (amonst other things!) to amend the grouping of a report.

hth

Edit: Beat me to it Doug - by a whisker - Cheers!

Hi, welcome to UA.

Reports are indeed a little tricky.

First let's take a look at Previews vs. Print. You can open a report in Preview mode, and the RightClick menu can be used to print it from there (you can use your own menu as well if you don't like all the options they provide).

As for opening in design mode, this is usually done hidden, before the report is previewed. Open hidden in design mode, make your changes, save and close, then open in preview mode. Something like this:

(not sure if the argument names/positions are right, but it'll give a general idea)


Personally I tend to do this more for printer settings etc rather than groupings or sortings, but you should be accomplish those tasks simlarly.

Hope this helps,

edit: well, disregard my post, these guys obviously know their way around reports more than I!

what i do, is have a column in the query that determines the sort order, based on a function, and use that as the default sort column for the report.

with regard to printing from a preview - among other ways, just right-click the previwed report to see a print option.

Thanks everyone.

Changing the sort order within the Report's Open event is now working....

If Me.Parent.Name = "frmFormABC" Then
Select Case Forms!frmFormABC.optGrpOverdues.Value
Case 1 'Days
Me.GroupLevel(0).ControlSource = "Delay"
Me.GroupLevel(0).SortOrder = True ' Descending
Case 2 'Job
Me.GroupLevel(0).ControlSource = "Job"
Me.GroupLevel(0).SortOrder = False ' Ascending
End Select
Else
Me.GroupLevel(0).ControlSource = "Delay"
Me.GroupLevel(0).SortOrder = True ' Descending
End If

The only trouble I'm having now is that I call it from two different forms. If its not FormABC, then I don't want to do the Select as Forms!frmFormABC.optGrpOverdues.Value won't be open.

The Me.Parent name seems like the way forward but keeps coming up with invalid expression. I can't see why.

On the subject of print from print preview, my users don't like the right click option and would like a button. Access seems to basically say that a Report is like paper - it can't interact.

Thanks,

Chris.

Hi Chris

The 'Me.' expression refers to the report or form that the code is 'on' while Me.parent would be the parent of the subreport or subform the code is on but does not reference the calling form.

The easiest way to do what you want is to reference the form that opens the report in the OpenReport command's OpenArgs argument

DoCmd.OpenReport "ReportName", acViewPreview, , , , Me.Name

Then in the Reports Open Event

If Me.OpenArgs = "frmFormABC" Then

hth

Thanks very Bernie.

So I understand that "Me.parent would be the parent of the subreport or subform the code is on but does not reference the calling form", although I'm not sure why. Am I missing something.

It sounds like the OpenArgs route is the way to go, although I might actually pass the sort order in it.

Thanks again - I'm amazed at the speed of response,

Chris.

Hi Chris

>>but does not reference the calling form", although I'm not sure why<<
I can't give any particular reason - except that it doesn't ! The form and report are completely independent Access objects and you have to specifically pass that information to the report.

You may be able to pass the sort order once the report is open

Though I've never tried sorting like this. You could alternatively pass the sort order in the OpenArgs as well as the source form



Then these can be split in the Report Open Event

>>I'm amazed at the speed of response<< No problem


Edit: Corrected code error