Sort subform by inputted column name

[Davie P]

Hi,

I have a form "Form1" with a subform "Subform1". On my form I have a command
button that will display an input box when pressed. The user is prompted to
input the field name to sort the subform by.

The problem I am encountering is that it is necessary to manually sort the
subform (i.e. press the built in Access buttons to sort Ascending or
Descending) before the command button will work.

I am using the following code with a hidden field "SortOrder" to maintain
the sort order of the subform:

Private Sub SortGrid_Click()

Dim strColumn As String
Dim strPrompt As String

Me.OrderByOn = True

If SortOrder = "DESC" Then
strPrompt = "Enter Sort Column:"
strColumn = InputBox(Prompt:=strPrompt, Title:="System Name")

Me.OrderByOn = True
Me!frmChangeNotificationSubformGrid.Form.OrderBy = strColumn & " ASC"
SortOrder.Value = "ASC"
ElseIf SortOrder = "ASC" Then
strPrompt = "Enter Sort Column:"
strColumn = InputBox(Prompt:=strPrompt, Title:="System Name")

Me.OrderByOn = True
Me!frmChangeNotificationSubformGrid.Form.OrderBy = strColumn & " DESC"
SortOrder.Value = "DESC"
End If
...


Thanks in advance,
Dave

 

[Roger Carlson]

On my website (see sig below) is a small sample database called
"ListBoxSorting.mdb" which illustrates how to sort a listbox OR A SUBFORM,
by clicking the column heading, similar to Outlook.