Dynamic report sorting

P

Pat Dools

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!
 
M

Marshall Barton

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.

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
 
P

Pat Dools

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?
 
M

Marshall Barton

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?


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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top