Sort data on a continuous form 2

[fdalmoro]

I have a help desk database that has a form with a summary view. The form looks something like this,

WO# - Date - Requested By - Summary - Tech Assigned
123 - 06/27/03 - Pancho - Test - Larry
124 - 06/26/03 - Ed - test 2 - Curly

etc.

What I want to do is be able to click on WO# for example and have all of the wo sorted, or click on Tech assigned and sort it by tech.

I can probably do something like
On Click of WO# Close form and Open form again with such and such filter that sorts everything with wo#. But I would like to be able to do this just with code and not using any queries. I think I can get it working with queries fairly easy but just wondering if there is a way to do it without. Thanks

 

[LarryDeLaruelle]

feross101:

Here is the text of a handout I use in a class.

Setting sort order command buttons on Continuous Display forms.

For each field to be sorted, add a command button to the form header band.

Provide an appropriate caption
Provide an appropriate name
In the On_Click event for each button add this code:

strSortField = “FieldName”
Call SortOrder

In the General Declarations section of the form’s module add this:

Dim strSortfield As String

Add a sub procedure (private) to the form’s module named SortOrder; enter this code:

Private Sub SortOrder()
Dim bolOrder As Boolean
Dim strOrder As String

Screen.ActiveForm.AllowAdditions = False
DoCmd.GoToControl strSortField

strOrder = InputBox("Enter A for Ascending; D for Descending", "Sort Order", "A&quot

If strOrder = "A" Then
bolOrder = True
ElseIf strOrder = "d" Then
bolOrder = False
Else
MsgBox "Invalid Selection", vbOKOnly + vbCritical, "Sort Order"
Exit Sub
End If

If bolOrder Then
DoCmd.RunCommand acCmdSortAscending
Else
DoCmd.RunCommand acCmdSortDescending
End If

End Sub

HTH

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[fdalmoro]

That works like a charm. Question, a window appears when I click on the title asking for Sort Order A. How can I make it the default so that the window won't pop up? Also, on one section I didn't know if I filled it in right. The code works but maybe it would not give me the window if I fixed this part right.

Here is the line.

strOrder = InputBox("A", "Sort Order", "A")

Thanks

 

[LarryDeLaruelle]

feross101:

Glad it worked for you.

That line opens an input dialog and offers the user the option of sorting the column in A(scending) or D(escending) order with the default set to A(scending).

If you want to set a default just change the line to:

strOrder = "A".

This will always give an ascending sort order or if you use "D" in descending.

You can then eliminate the If . . Then section of code.

If you want to make it even fancier, you can use label controls instead of command buttons. This allows you to play with back and foreground colors.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org