Two Reports same Information 1

[cbiker]

I have two reports built that show the same Information just grouped differently. is there anyway to just have one report and change the grouping and group header by picking the the way that you group from a combo box. The two fields that I Group by are SIC and NAICS.

Any help would be much appreciated.

 

[dhookom]

Allen Browne has code at his site that explains how to change the ControlSource property of the grouping levels at run time.

http://users.bigpond.net.au/abrowne1/ser-33.html

Duane
MS Access MVP

 

[THWatson]

Just to add to the mix, below is an excerpt from Access Archon Column #41, by Helen Fedema
Tom

Sometimes you want more flexibility – say you have a table of customers, with their names, addresses, phones, and information about the last order date and the balance due on their accounts. You might want to sort a customer report on any of those pieces of information, and you don't really want to create a dozen reports, each with a different sort.
To make it easier to select a sort field on-the-fly, create a dialog form with a combo box for selecting the sort field. Select Field List as the RowSourceType property for the combo box, and the report's record source for the RowSource property. The following procedure (running from a Print command button) opens a report and sorts it by the field selected in the combo box:

Private Sub cmdPrint_Click()

On Error GoTo cmdPrint_ClickError

Dim strValue As String
Dim strFilter As String
Dim rpt As Report
Dim strReportName As String
Dim strFieldName As String

strReportName = "rptCustomerData"
strFieldName = Nz(Me![cboSelectField])
If strFieldName = "" Then
MsgBox "Please select a field"
Me![strFieldName].SetFocus
End If

DoCmd.OpenReport strReportName, acViewPreview
Set rpt = Reports(strReportName)
rpt.OrderByOn = True
rpt.OrderBy = strFieldName

cmdPrint_ClickExit:
Exit Sub

cmdPrint_ClickError:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume cmdPrint_ClickExit

End Sub

 

[dhookom]

My experience is that if you have ANY sorting and grouping levels set, the sorting and grouping will always over-ride the reports OrderBy property.

Duane
MS Access MVP

 

[THWatson]

Duane
I hope you don't mind my asking a couple of questions about these different processes...

1. Using the Allen Browne approach, you put the fields you want to order by in the report's Sorting and Grouping dialogue box. The frmChooseSort then uses them to select the order.
I have a report that I want to order by either "CamperLast" or "SiteNbr." The SiteNbr field is numeric. If I place the "CamperLast" first in the report's Sorting and Grouping box, and the "SiteNbr" second, and then in frmChooseSort choose to order by SiteNbr, the sort ends up as if it were text (1 through 49, 5, 501, 502,52, 53 etc.)
However, if I place the "SiteNbr" first in the report's Sorting and Grouping box, it works out properly.
Either way, sorts by "CamperLast" are always right.

Why does the "SiteNbr" react as text if it's placed second in the Sorting and Grouping box?

2. Helen Fedema's approach doesn't require establishing any levels in the report's Sorting and Grouping. On the other hand, her code lacks some flexibility in fanciness, due to the fact that the form's combo box generates a field list, which leaves you stuck with the names that are in the table or query that is the report's record source.
Since Allen Browne's frmChooseSort is not bound to a record source, you are left free to put in a Value List as the RowSourceType, and then use a Value such as "Camper's Last Name" rather than "CamperLast" or "Site #" rather than "SiteNbr", and then tie the actual field names to those values in the report code.

Tom

 

[dhookom]

I'm not sure why your numeric value would be sorted as if it was text. You may need to set the control source to
=Val([SiteNbr])

I don't like Helen's solution since it will fail if there is a sorting and grouping set. I always set some sorting for every report.

I have used a third approach which doesn't use any code. Create a new column in the record source/query
SortBy: Choose(Forms!frmChooseSort!grpSort,[CamperLast],Val([SiteNbr]))
Then set the 1st level of sorting and grouping to SortBy.

Duane
MS Access MVP

 

[THWatson]

Thanks, Duane.
I appreciate your insights.

Tom