Dynamic report sorting

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

Guest

Hello,

I have a report request that the client is asking to be sorted multiple ways
(by Study, by Patient ID, by Form Name). Rather than designing different
reports can I point to different queries based on which (check box?, radio
button?, etc.) is checked on a Switchboard-type form to generate the report
with the requested sort order?

I am looking for methods (and accompanying code) people have found useful in
creating reports that can be generated with different sorts.

Thank you!
 
Pat said:
I have a report request that the client is asking to be sorted multiple ways
(by Study, by Patient ID, by Form Name). Rather than designing different
reports can I point to different queries based on which (check box?, radio
button?, etc.) is checked on a Switchboard-type form to generate the report
with the requested sort order?

I am looking for methods (and accompanying code) people have found useful in
creating reports that can be generated with different sorts.
Click to expand...

Sorting a report's record source query is a waste of time.
Report sorting needs to be specified using the Sorting and
Grouping window (View menu).

Once you have a sort specified in Sorting and Grouping, you
can modify it in the report's Open event:

With Forms!theform
If .chkPatient <> False Then
Me.GroupLevel(0).ControlSource = "Patient ID"
ElseIf chkStudy <> False Then
Me.GroupLevel(0).ControlSource = "Study"
.. . .
End If
End With
 
Hi Marshall,

How do you over-ride the sort you have in the 'Sorting and Grouping'
property of the report? I have the following code in the report's 'On Open:'
Event:

Private Sub Report_Open(Cancel As Integer)
With Forms!fSwitchboardTEST
If CheckPatient <> False Then
Me.GroupLevel(0).ControlSource = "patient"
ElseIf CheckCRF <> False Then
Me.GroupLevel(0).ControlSource = "f1label"
End If
End With
End Sub

where 'CheckPatient' and 'CheckCRF' are check-boxes within an Option Group
on 'fSwitchboardTEST'. If 'CheckPatient' is checked, then the Option Group
value is '1', and if 'CheckCRF' is checked then the Option Group value is
'2'. Is it because the Option Group on my Switchboard is not tied to a Data
Source? I don't need to store what the user chooses, I just need the choice
to dictate how my report sorts when it opens. What am I missing here?
 
Pat said:
How do you over-ride the sort you have in the 'Sorting and Grouping'
property of the report? I have the following code in the report's 'On Open:'
Event:

Private Sub Report_Open(Cancel As Integer)
With Forms!fSwitchboardTEST
If CheckPatient <> False Then
Me.GroupLevel(0).ControlSource = "patient"
ElseIf CheckCRF <> False Then
Me.GroupLevel(0).ControlSource = "f1label"
End If
End With
End Sub

where 'CheckPatient' and 'CheckCRF' are check-boxes within an Option Group
on 'fSwitchboardTEST'. If 'CheckPatient' is checked, then the Option Group
value is '1', and if 'CheckCRF' is checked then the Option Group value is
'2'. Is it because the Option Group on my Switchboard is not tied to a Data
Source? I don't need to store what the user chooses, I just need the choice
to dictate how my report sorts when it opens. What am I missing here?
Click to expand...


Option groups are different from check boxes so the code
needs to get the value of the option frame:

Private Sub Report_Open(Cancel As Integer)
Select Case Forms!fSwitchboardTEST.optionframename
Case 1
Me.GroupLevel(0).ControlSource = "patient"
Case 2
Me.GroupLevel(0).ControlSource = "f1label"
End Select
End Sub
 
Back
Top