Sort Report By Code

  • Thread starter Thread starter David Stumbles
  • Start date Start date
D

David Stumbles

G'day all,

I can't work out how to change the sort order of a report
using VBA code. Could someone please point me in the right
direction.

Thanks,

David.
 
These 3 procedures show a simple way to sort a form using "raised" field
name labels as if they were buttons. Labels have a Click event.

The first procedure shows how to call the generic procedure called ReSort.

The second procedure assumes there are 3 labels on the form that can be
clicked.
It changes the color of the clicked label to blue and sets the rest to
black.
A second click of the same label reverses the sort order.

The 3rd procedure is placed in a report's open event. It simply looks at the
open form and uses the same sort order.

Private Sub lblLastName_Click()
Call ReSort(Me![lblLastName])
End Sub

Private Sub ReSort(ctl As Control)
DoCmd.Hourglass True
Me![lblLastName].ForeColor = 0
Me![lblFirstName].ForeColor = 0
Me![lblMiddleName].ForeColor = 0
ctl.ForeColor = 16737843

Me.OrderByOn = True
If Me.OrderBy = Mid$(ctl.Name, 4) Then
Me.OrderBy = Mid$(ctl.Name, 4) & " Desc"
Else
Me.OrderBy = Mid$(ctl.Name, 4)
End If

DoCmd.Hourglass False
End Sub

Private Sub Report_Open(Cancel As Integer)
Me.OrderByOn = True
Me.OrderBy = Forms![FormName].OrderBy
End Sub
 
Many thanks Joe for your excellent and prompt reply.

David.
-----Original Message-----
These 3 procedures show a simple way to sort a form using "raised" field
name labels as if they were buttons. Labels have a Click event.

The first procedure shows how to call the generic procedure called ReSort.

The second procedure assumes there are 3 labels on the form that can be
clicked.
It changes the color of the clicked label to blue and sets the rest to
black.
A second click of the same label reverses the sort order.

The 3rd procedure is placed in a report's open event. It simply looks at the
open form and uses the same sort order.

Private Sub lblLastName_Click()
Call ReSort(Me![lblLastName])
End Sub

Private Sub ReSort(ctl As Control)
DoCmd.Hourglass True
Me![lblLastName].ForeColor = 0
Me![lblFirstName].ForeColor = 0
Me![lblMiddleName].ForeColor = 0
ctl.ForeColor = 16737843

Me.OrderByOn = True
If Me.OrderBy = Mid$(ctl.Name, 4) Then
Me.OrderBy = Mid$(ctl.Name, 4) & " Desc"
Else
Me.OrderBy = Mid$(ctl.Name, 4)
End If

DoCmd.Hourglass False
End Sub

Private Sub Report_Open(Cancel As Integer)
Me.OrderByOn = True
Me.OrderBy = Forms![FormName].OrderBy
End Sub

--
Joe Fallon
Access MVP



G'day all,

I can't work out how to change the sort order of a report
using VBA code. Could someone please point me in the right
direction.

Thanks,

David.
Click to expand...


.
Click to expand...
 
Glad to help!

I hope you got it working.
--
Joe Fallon
Access MVP



Many thanks Joe for your excellent and prompt reply.

David.
-----Original Message-----
These 3 procedures show a simple way to sort a form using "raised" field
name labels as if they were buttons. Labels have a Click event.

The first procedure shows how to call the generic procedure called ReSort.

The second procedure assumes there are 3 labels on the form that can be
clicked.
It changes the color of the clicked label to blue and sets the rest to
black.
A second click of the same label reverses the sort order.

The 3rd procedure is placed in a report's open event. It simply looks at the
open form and uses the same sort order.

Private Sub lblLastName_Click()
Call ReSort(Me![lblLastName])
End Sub

Private Sub ReSort(ctl As Control)
DoCmd.Hourglass True
Me![lblLastName].ForeColor = 0
Me![lblFirstName].ForeColor = 0
Me![lblMiddleName].ForeColor = 0
ctl.ForeColor = 16737843

Me.OrderByOn = True
If Me.OrderBy = Mid$(ctl.Name, 4) Then
Me.OrderBy = Mid$(ctl.Name, 4) & " Desc"
Else
Me.OrderBy = Mid$(ctl.Name, 4)
End If

DoCmd.Hourglass False
End Sub

Private Sub Report_Open(Cancel As Integer)
Me.OrderByOn = True
Me.OrderBy = Forms![FormName].OrderBy
End Sub

--
Joe Fallon
Access MVP



G'day all,

I can't work out how to change the sort order of a report
using VBA code. Could someone please point me in the right
direction.

Thanks,

David.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top