form filter

[smk23]

Is there a way to make a column header on a subform in Datasheet view perform
similar to Outlook with sort of that column when the user clicks it?

Thanks,
Sam

 

[Allen Browne]

Function SortForm(frm As Form, ByVal sOrderBy As String) As Boolean
On Error GoTo Err_SortForm
'Purpose: Set a form's OrderBy to the string. Reverse if already set.
'Return: True if success.
'Usage: Label/button above a column in a continuous form:
' Call SortForm(Me, "MyField")

If Len(sOrderBy) > 0 Then
' Reverse the order if already sorted this way.
If frm.OrderByOn And (frm.OrderBy = sOrderBy) Then
sOrderBy = sOrderBy & " DESC"
End If
frm.OrderBy = sOrderBy
frm.OrderByOn = True
' Succeeded.
SortForm = True
End If

Exit_SortForm:
Exit Function

Err_SortForm:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_SortForm
End Function

 

[Klatuu]

Yes. Here is an example where I do exactly that:

Private Function SetFilters()
Dim strFilter As String 'Used by Form Filtering procedures

On Error GoTo SetFilters_Error

With Me

'Build The Filter String
If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigDate <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseTarget],
""yyyy-mm"") = """ & _
.cboOrigDate & """"
End If

If .cboCurrDate <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CurrentReleaseTarget] = " & .cboCurrDate
End If

If .cboInitStatus <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

If Not IsNull(.txtDescrSearch) Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitShortDescr] Like ""*" &
Me.txtDescrSearch & "*"""
End If

.subInitiative.Form.Filter = strFilter
.subInitiative.Form.FilterOn = True

End With 'Me


SetFilters_Exit:
On Error GoTo 0
Exit Function

SetFilters_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure SetFilters of VBA Document Form_frmStartForm"
GoTo SetFilters_Exit

End Function

Then in the After Update event of each of the combo boxes, put in
=SetFilters()

This doesn't do it with the column headers, but what I do is position each
combo just above its column.