Sort Button on Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, this seems like such a simple question, but I can’t seem to figure it
out. I have a form in continuous view and I'd like to place two command
buttons on the form which will allow the user to sort the records from A-Z or
Z-A. Anyone have any suggestion about how I would do this?

Thanks,
Manuel
 
Do you only have one field in the form's record source? Which field did you
want to sort on?

I usually create a SQL statement with an Order By clause and apply it to the
form's Record Source property.
 
I have done this and what I did was create an Event Procdure for the
"column" label, a single click would sort asc, and a double would sort desc.
I would also change the color of the label so you could quickly tell which
"column" was sort and how it was sorted.

'***************************************************************************
********
This is the code I used for the labels. I pass the field I want to sort on,
how to sort, and the name of the label.
'***************************************************************************
********
Private Sub lblFrequency_Click()
Call mySortBy("ORD_FREQUENCY", "", "lblFrequency")
End Sub

Private Sub lblFrequency_DblClick(Cancel As Integer)
Call mySortBy("ORD_FREQUENCY", "DESC", "lblFrequency")
End Sub

'***************************************************************************
********
Her is the function I wrote to do the sort.
'***************************************************************************
********

Function mySortBy(myField, myOrder, myLabel)
Dim mySQL, myFormName, myLabelName, myLabelAppend
Dim C As Control
mySQL = "SELECT qry_EMPLOYEE_NAMES.LAST_FIRST, ORDERS.*, [PAT_LAST_NAME] &
', ' & [PAT_FIRST_NAME] AS PATIENT_NAME, PATIENTS.PAT_LAST_NAME,
PATIENTS.PAT_FIRST_NAME, [ROOMS].[ROO_NUMBER] & ' - ' & [ROOMS].[ROO_BED] AS
CURRENT_ROOM, ROOMS_1.ROO_NUMBER & ' - ' & ROOMS_1.ROO_BED AS PREVIOUS_ROOM,
qry_EMPLOYEE_NAMES.FIRST_LAST, PATIENTS.PAT_DISCHARGE_DATE,
qry_EMPLOYEE_NAMES.EMP_ID, ORDERS.ORD_INACTIVE FROM (((ORDERS LEFT JOIN
PATIENTS ON ORDERS.ORD_PAT_ID = PATIENTS.PAT_ID) LEFT JOIN ROOMS ON
PATIENTS.[PAT_ROO-ID_CURR] = ROOMS.ROO_ID) "LEFT JOIN ROOMS AS ROOMS_1 ON
PATIENTS.PAT_ROO_ID_PREV = ROOMS_1.ROO_ID) LEFT JOIN qry_EMPLOYEE_NAMES ON
ORDERS.ORD_EMP_ID = qry_EMPLOYEE_NAMES.EMP_ID WHERE
(((PATIENTS.PAT_DISCHARGE_DATE) Is Null) And ((ORDERS.ORD_INACTIVE) = 0))
ORDER BY " & myField & " " & myOrder & ";"

Me.RecordSource = mySQL
Me.Refresh

myFormName = "frm_Therapist"

If myOrder = "DESC" Then
colorOn = 128
Else
colorOn = 16384
End If

colorOff = 8388608

For Each C In Forms(myFormName)
If TypeOf C Is Label Then
myLabelName = Trim(C.Caption)
If Mid(C.Name, 1, 4) = "lbl_" Then
Else
If C.Name = myLabel Then
With C
.ForeColor = colorOn
End With
Else
With C
.ForeColor = colorOff
End With
End If
End If
End If
Next C
End Function

'***************************************************************************
********
 
I have 6 fields, and would like the user to be able to sort by each feid,
depending on where the cursor is.

Thanks for your help.
Manuel
 
Back
Top