Dynamic Order By

[majaro]

I cant seem to wrap my head around where to place it in this code. This code filters a report based on user selections in a list box. Never have done a dynamic ORDER BY before so thought I might ask you guys.

Private Sub cmdPreviewDept_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.

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.
strDoc = "SummaryReport"
'Loop through the ItemsSelected in the list box.
'With Me.lstDept
' 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
With Me.lstDept
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & "'" & .ItemData(varItem) & "'" & ","
'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 = "[PIDDept] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "PIDDept: " & Left$(strDescrip, lngLen)
End If
End If

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

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

[akn112]

i always have the sql source of the listbox recreated each time a selection of a checkbox (or any other control sources) have been selected. example:

public sub populatelistbox()
gSQLStatement = ... & "ORDER BY " & fDetermineOrderBy
'Populate ur list box with SQL statement here
end sub

public function fDetermineOrderBy() as string
if checkbox1=true then
fdetermineOrderBy = fDetermineOrderBy & "Field1 AND "
end if
if checkbox2=true then
fDetermineOrderBy = fDetermineOrderBy & "Field 2 AND "
end if
...

if len(fDetermineOrderBy)=0 then fdeterminOrderby= yourdefaultsort
if right(fdetermineOrderBy,4)= "AND " then
fdetermineorderby = left(fdetermineorderby,len(fdetermineorderby)-4)
end if
end function

private sub checkbox1_click()
populatelistbox
end sub

[majaro]

Thanks Ill give it a shot

[Trevor]

put the code in after update of the list/text box that drives your dependent list/textbox