Sort a Report from a Pop-up Form

[Guest]

I combined the Microsoft KB 146310 & 147143 (How to sort a report from a
Pop-up form & How to Filter a report from a pop-up form) to create a dynamic
report.

The filtering options are working perfectly but the sort is not. I came
accross my code many time and I don't see what's wrong. Here is my code.

-------------
Private Sub SetSortBtn_Click()
Dim strSQL As String
Dim iCounter As Integer

For iCounter = 1 To 8
If Me("Sort" & iCounter) <> "" Then
Select Case Me("Sort" & iCounter)
Case "PO Number":
strSQL = strSQL & "[PONumber]"
Case "PO Item Number":
strSQL = strSQL & "[POItemNumber]"
Case "PO Schedule Date":
strSQL = strSQL & "[POItemSchedDate]"
Case "PR Number":
strSQL = strSQL & "[PRNumber]"
Case "PR Item Number":
strSQL = strSQL & "[PRItemNumber]"
Case "PR Status":
strSQL = strSQL & "[PRItemStatus]"
Case "PR Schedule Date":
strSQL = strSQL & "[PRItemSchedDate]"
Case "Part Number":
strSQL = strSQL & "[PRPartNumber]"
End Select

If Abs(Me("Check" & iCounter)) = 1 Then
strSQL = strSQL & " DESC"
End If
strSQL = strSQL & ", "
End If
Next iCounter

If strSQL <> "" Then
strSQL = Left(strSQL, (Len(strSQL) - 2))

Reports![DynamicReport-Aging].OrderBy = strSQL
Reports![DynamicReport-Aging].OrderByOn = True
End If
End Sub
-----------------

When I click my Set Sort button, the progress bar appears in the lower-left
corner of access (the usual progress bar that should means it's working) but
once it stop processing the sort order is not changing on the report.

Any ideas why it is not working?

Yanick

 

[Marshall Barton]

I combined the Microsoft KB 146310 & 147143 (How to sort a report from a
Pop-up form & How to Filter a report from a pop-up form) to create a dynamic
report.

The filtering options are working perfectly but the sort is not. I came
accross my code many time and I don't see what's wrong. Here is my code.

-------------
Private Sub SetSortBtn_Click()
Dim strSQL As String
Dim iCounter As Integer

For iCounter = 1 To 8
If Me("Sort" & iCounter) <> "" Then
Select Case Me("Sort" & iCounter)
Case "PO Number":
strSQL = strSQL & "[PONumber]"
Case "PO Item Number":
strSQL = strSQL & "[POItemNumber]"
Case "PO Schedule Date":
strSQL = strSQL & "[POItemSchedDate]"
Case "PR Number":
strSQL = strSQL & "[PRNumber]"
Case "PR Item Number":
strSQL = strSQL & "[PRItemNumber]"
Case "PR Status":
strSQL = strSQL & "[PRItemStatus]"
Case "PR Schedule Date":
strSQL = strSQL & "[PRItemSchedDate]"
Case "Part Number":
strSQL = strSQL & "[PRPartNumber]"
End Select

If Abs(Me("Check" & iCounter)) = 1 Then
strSQL = strSQL & " DESC"
End If
strSQL = strSQL & ", "
End If
Next iCounter

If strSQL <> "" Then
strSQL = Left(strSQL, (Len(strSQL) - 2))

Reports![DynamicReport-Aging].OrderBy = strSQL
Reports![DynamicReport-Aging].OrderByOn = True
End If
End Sub
-----------------

When I click my Set Sort button, the progress bar appears in the lower-left
corner of access (the usual progress bar that should means it's working) but
once it stop processing the sort order is not changing on the report.

The Order By clause in the report's record source query only
works in the most trivial reports. The only safe way to
sort a report is to use Sorting and Grouping in the report's
design.

To do this dynamically, you need to first manually create
enough sort levels. Then you can use the report's Open
event to change the various GroupLevels' ControlSource
property. Here's a brief article on this subject:
http://allenbrowne.com/ser-33.html