add report grouping progmatically

[nickjar2]

is it possible to start with a normal report without any sorting/grouping etc.

A user chooses a value from a combo box eg. 'Completed'.

Instead of having a select case saying:

case 'Completed'
docmd.openreport "rptCompleted" etc etc

Instead, on the On_Open of the ONE report add the grouping at run time.

Let me kmow if this doesn't make sense.

I am sure this can be done I just cannot seem to think of a way.

Cheers,

Nick

 

[sko]

You could use a select case to change the report's record source.

Select Case
Case 1
Me.RecordSource = "qryReportCompleted"
Case 2
Me.RecordSource = "qryReportInProgress"

 

[sko]

Another thought. If you don't want to design separate queries for each report, you could base your report on a parameter query. In the criteria section point to the combo box on your form.
[Forms]![NameOfYourCriteriaForm]![NameOfYourComboBox]
Add a button to open the report.

You could also add the value in the combo box to the title of your report by adding a text box in the report and setting the control source to
=[Forms]![NameOfYourCriteriaForm]![NameOfYourComboBox]

HTH

 

[nickjar2]

So if I stick
=[Forms]![NameOfYourCriteriaForm]![NameOfYourComboBox]

in the Group header of the report that should work?

So basically if the combo says 'surname' I want the report to group on Surname, if the combo says 'Status' I want the report to group on Status

Cheers

Nick

 

[sko]

Sorry, I didn't make myself to clear.
Let's skip that business about the title for a minute.

Is your report based on a query? Are you familiar with parameter queries?

Let's say I have a report that lists all maintenance work. Sometimes I want to filter the report. I may want to see only work "completed" or I may want to see work "in progress". The report design is the same but the records retrieved are different. Is this what you had in mind?

 

[nickjar2]

Not quite. What I have is 1 report. Say the report is based on a table which has 10 records. Every time the report is viewed, these 10 recs will show. But, the user can choose how to view them. Say the user wants to view the reocrds grouped on 'Date of completion', the user will choose this from the combo, and the report will open, with the records grouped on 'Date of completion'. Likewise the user can chosse to group the recs on 'Status' etc.

Hope this makes sense.

Nick

 

[sko]

>A user chooses a value from a combo box eg. 'Completed'

I misunderstood. From your example I thought you wanted to filter the report.

I don't think you can change a report's group by dynamically but you can change the sort order. Search the Microsoft Knowledge Base for article Q208532.

As an alternative, have you considered a data access page with a pivot table inserted?

I sometimes use a form for ad-hoc reporting. The form header includes combo boxes where the user selects query criteria. The results are in a sub-form that is set to datasheet view. This allows the user to rearrange the columns and sort order. He can also use the filter by selection button to further define the filter.