Report is not sorting correctly (1 Viewer)

[mariaanthony50]

I am using this code from a cmd on my form to run a report. The report is running correctly and pulling the data but it is not sorting correctly. Can you help me edit this code to make it sort ascending.
Private Sub cmdPreviewCategory_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
'Author: Allen J Browne, 2004.
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type. See note 1.
strDoc = "Problem_Record"
'Loop through the ItemsSelected in the list box.
With Me.lstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
'Build up the description from the text in the visible column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "

End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[CategoryID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Category: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreviewCategory_Click"
End If
Resume Exit_Handler
End Sub

 

[sneuberg]

I believe it would be easier to do the sorting in the report rather than trying to modify this code to put an Order By in the query.

 

[mariaanthony50]

I read through some other posts and I am not sure I can use the Group and Sort. This report is pulled and run each time a different cmd button is selected. For example, I have a cmdManager, cmdCOD, cmdFisalYear etc, All of which run the report from the cmd button.

 

[pbaldy]

As far as I know, the only reliable way to get the desired sorting is by using Group & Sort in the report. You can modify it in the Open event:

Me.GroupLevel(0).ControlSource = "FieldName"

In your situation, you could use a form control or OpenArgs to fill in the field you want to sort on.

 

[mariaanthony50]

Paul
I like this.

Where in the code would I place this statement?

 

[pbaldy]

It would not be in the code you posted, it would be in the open event of the report. You could pass the field name to be sorted on in OpenArgs and have:

Me.GroupLevel(0).ControlSource = Me.OpenArgs

 

[mariaanthony50]

Can I use this code as provided or do I need to add a fieldname to it?

 

[pbaldy]

Depends on how you want to do it. I just noticed you're already using OpenArgs, so you can add to it or use a form control or a global variable.

 

[mariaanthony50]

Paul
I have been playing around with this but I am not familiar enough adding multiple arguments to the OpenArgs. Can you provide the exact syntax.
Sorry but I am very new to this!

 

[pbaldy]

I happen to have a sample db:

http://www.baldyweb.com/OpenArgs.htm