Need to add sort criteria to report

[pellissier]

I used PBaldy's excellent code to pass multivalue list criteria (department) from a form to my report. Now I need to allow the user to also sort by one of 3 fields. I put the 3 sort options in an option group on my form but can't figure out how to pass this to the report in addition to the selected departments. Can anyone help?

Here is the code on the Preview button of my form:
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.ListFilter.ItemsSelected.Count = 0 Then


MsgBox "Must select at least 1 employee"
Exit Sub
End If
'add selected values to string
Set ctl = Me.ListFilter
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
DoCmd.OpenReport "rptCDMWorksheet", acPreview, , "[ID] IN(" & strWhere & ")"
Exit_cmdOpenReport_Click:
Exit Sub
Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click
End Sub

[pbaldy]

I'm just on an iPad right now, so don't have code, but I use code in the open event of the report that looks at the option group and sets the sorting and grouping options appropriately.

[pellissier]

I'm just on an iPad right now, so don't have code, but I use code in the open event of the report that looks at the option group and sets the sorting and grouping options appropriately.

Thank you - that is what I eventually stumbled in to. I just have one more question - my report is grouping correctly - but it's repeating the group header for each detail in the group. Do you know what I have done wrong?

[pbaldy]

You don't have it in both sections, do you? Can you post the db here, or at least a screen shot of the report in design view?

[pellissier]

You don't have it in both sections, do you? Can you post the db here, or at least a screen shot of the report in design view?

No - it turned out that the way I had my sort set up was interfering. Once I fixed that all was well. Thank you so much for your help - and your excellent code. Part of the battle with finding help on stuff like this is figuring out how to google the correct terms. Once I had that figured out I quickly found your reply to the question at hand. Thank you for sharing your expertise with those of us who are learning!

[pbaldy]

No problem, and welcome to the site by the way!

[pellissier]

I spoke too soon - I am not sure what is happening or what I have done wrong. I have a form where the user chooses a department (or multiple departments) which is the group on the report. I also have an option group for sorting that same report. When I choose to sort the report I get the report - with the department chosen - but if there are duplicate codes in the sorted field it does not show the duplicate codes. IE if the department report is not sorted there are 30 records on the report - all shows correctly. But if I choose to sort the records by a code field I get 24 records on the same report. The 6 that are missing happen to be duplicate codes though they belong to records with different PK's. I can't figure out why sorting should affect this. I have Department field in the Group Header and all other fields in the Detail section. All I am trying to do is sort - not re-group. When I open the report and look at the data properties the FilterBy is correct but the OrderBy has nothing though the OrderByOnLoad=Yes. I don't think I have the sort order being sent correctly to the report. You said something about code in the On Open for the report so here is what I have in case you can see something I am missing?

Private Sub Report_Open(Cancel As Integer)
Select Case Forms!WorksheetCriteria!SortOptions
Case 1 'srtCPT
Me.GroupLevel(0).ControlSource = "CPT Code"

Case 2 'srtChg
Me.GroupLevel(0).ControlSource = "ChargeCode"

Case 3 'srtChgDesc
Me.GroupLevel(0).ControlSource = "Charge Description"

End Select
Me.OrderByOn = True
End Sub

Thank you for your help with this!

[pbaldy]

Well, here's code from an app of mine. Note that I not only set the sorting and grouping option, but the control source of the field in the group header.

Private Sub Report_Open(Cancel As Integer)
  Select Case Forms!frmReports.fraGrouping
    Case 1 'group on car type
      Me.GroupLevel(0).ControlSource = "CarType"
      Me.txtGroup.ControlSource = "CarDesc"
    Case 2 'group on company
      Me.GroupLevel(0).ControlSource = "Company"
      Me.txtGroup.ControlSource = "Company"
    Case 3 'sort on date/time, no grouping
      Me.GroupLevel(0).ControlSource = "DispDateTime"
      Me.txtGroup.ControlSource = "DispDateTime"
      Me.GroupHeader0.Visible = False
  End Select
End Sub

[pellissier]

I will try this - thank you so much! Will let you know if I find success -

[pellissier]

Paul - this works but I am back to my problem with my Dept (the group - always) showing a group header for every record that the report is sorted by. I have done reports for years and I am stumped as to why - I'm pretty comfortable with sorting and grouping in the report - any thoughts why this might still be happening? I need to keep the report grouped by Department - there are 78 departments and 16000 records. My client doesn't always need to print every department - but the ones they do need to print vary by their client needs.

[pbaldy]

I'm back to can you post the db here, or at least a screen shot of the report in design view?

[pellissier]

Hopefully it is attached....

[pellissier]

Here it is - thank you for your help with this!

[pellissier]

I finally figured this out - the OnOpen code that I had on my report was incorrect. All is now well! Thank you for your willingness to help me!