Dynamic Sorting in Report

V

Vivi

Can Access have dynamic Sorting in Report?

I am after the first report is generated, can the user click at the
different field, so the report will be resorted???
If not, how can I use "Form" to achieve this goal???

EX: the report read:
ID CustFirstName CustLastName State.....

By default, the report sorts by ID, but if customer "select"(by which
method?) State or CustFirstName or CustLastName, then the report will be
resorted!!!
 
A

Allen Browne

A report's sorting is determined by what is in its Sorting and Grouping box
(View menu.)

If there is nothing in that dialog, you can set the report's OrderBy
property after it is open, e.g.:
Reports!Report1.OrderBy = "CustLastName"
Reports!Report1.OrderByOn = True

It is also possible to programmatically change the order of the items in the
Sorting and Grouping box, by assigning the ControlSource of the GroupLevel
in the Open event of the report. For an example, see:
Sorting Records in a Report at run-time
at:
http://allenbrowne.com/ser-33.html
 
V

Vivi

Dear Allen:
Thank you very much. I read this article already.
If by default, this report is sorted by ID.

In your case provided, which event or method or trigger the " case 1" or
"case 2" so the report will be resorted. Take a look at the method you
provide, after the report is open, should another "Form" popup so the user
can select another sorting method???
Thanks!!!
 
A

Allen Browne

As stated, you can set the report's OrderBy while it is open, but you can
only use Report_Open to reassign the ControlSource of the GroupLevel.

So, to use the method in the webpage, the button on your form would need to
close the report and reopen it, so Report_Open can pick up the new setting
from your form:
Dim strDoc As String
strDoc = "Report1"
If IsLoaded(strDoc) Then
DoCmd.Close acReport, strDoc
DoCmd.OpenReport strDoc, acViewPreivew
End If
 
V

Vivi

Dear Allen: Still have questions. Sorry to bother you again.

Here is my design for your inspiration.

Assuming Radio butoon on form "Choose Sort" in option group "grpSort" with
values and captions:
1. ID (primary key) also default value for grpSort
2. Fst Name
3. Lst Name
Also one command button "pvwrpt" to open report "Myreport" in preview view.

Then, the question is: where should I add this following codes???
Select Case Forms!ChooseSort!grpSort
Case 1' ID
Me.GroupLevel(0).ControlSource ="[ID]"
Me.GroupLevel(1).ControlSource ="[Fst Name]"
Me.GroupLevel(2).ControlSource ="[Lst Name]"
Case 2 'Fst Name
Me.GroupLevel(0).ControlSource ="[Fst Name]"
Me.GroupLevel(1).ControlSource ="[ID]"
Me.GroupLevel(2).ControlSource ="[Lst Name]"
Case 3 'LstName
Me.GroupLevel(0).ControlSource ="[LstName]"
Me.GroupLevel(1).ControlSource ="[ID]"
Me.GroupLevel(2).ControlSource ="[Fst Name]"
End Select

Also if I want dynamic sorting, yousuggests me to use the following code
when Report "MyReport" is open:
Dim strDoc As String
strDoc="Myreport"
If IsLoaded(strDoc) Then
DoCmd.Close aReport, strDoc
DoCmd.OpenReport strDoc, acViewPreview
End If
How come IsLoaded is not predefined at the VB???
(the debugger is on)

Thank you for giving me more instructions.....
 
A

Allen Browne

Answer 1:
Put the code into the Open event procedure of the report.

Answer 2:
Copy the IsLoaded() function from the Northwind sample database, or replace
the line:
If IsLoaded(strDoc) Then
with:
If CurrentProject.AllReports(strDoc).IsLoaded Then
This alternative works only in Access 2000 and later.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Vivi said:
Dear Allen: Still have questions. Sorry to bother you again.

Here is my design for your inspiration.

Assuming Radio butoon on form "Choose Sort" in option group "grpSort" with
values and captions:
1. ID (primary key) also default value for grpSort
2. Fst Name
3. Lst Name
Also one command button "pvwrpt" to open report "Myreport" in preview
view.

Then, the question is: where should I add this following codes???
Select Case Forms!ChooseSort!grpSort
Case 1' ID
Me.GroupLevel(0).ControlSource ="[ID]"
Me.GroupLevel(1).ControlSource ="[Fst Name]"
Me.GroupLevel(2).ControlSource ="[Lst Name]"
Case 2 'Fst Name
Me.GroupLevel(0).ControlSource ="[Fst Name]"
Me.GroupLevel(1).ControlSource ="[ID]"
Me.GroupLevel(2).ControlSource ="[Lst Name]"
Case 3 'LstName
Me.GroupLevel(0).ControlSource ="[LstName]"
Me.GroupLevel(1).ControlSource ="[ID]"
Me.GroupLevel(2).ControlSource ="[Fst Name]"
End Select

Also if I want dynamic sorting, yousuggests me to use the following code
when Report "MyReport" is open:
Dim strDoc As String
strDoc="Myreport"
If IsLoaded(strDoc) Then
DoCmd.Close aReport, strDoc
DoCmd.OpenReport strDoc, acViewPreview
End If
How come IsLoaded is not predefined at the VB???
(the debugger is on)

Thank you for giving me more instructions.....


Allen Browne said:
As stated, you can set the report's OrderBy while it is open, but you can
only use Report_Open to reassign the ControlSource of the GroupLevel.

So, to use the method in the webpage, the button on your form would need
to
close the report and reopen it, so Report_Open can pick up the new
setting
from your form:
Dim strDoc As String
strDoc = "Report1"
If IsLoaded(strDoc) Then
DoCmd.Close acReport, strDoc
DoCmd.OpenReport strDoc, acViewPreivew
End If
Click to expand...
Click to expand...
 
V

Vivi

Dear Allen:

It works finally!!! Thank you very much to spend your time to solve the
questions.

p.s. remark the "second and the rest of the grouplevel " I believe in the
original code , case 1, we sort ID first and then fst name and last name.
Why it happens? I just temporarly remark all of them.

Allen Browne said:
Answer 1:
Put the code into the Open event procedure of the report.

Answer 2:
Copy the IsLoaded() function from the Northwind sample database, or replace
the line:
If IsLoaded(strDoc) Then
with:
If CurrentProject.AllReports(strDoc).IsLoaded Then
This alternative works only in Access 2000 and later.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Vivi said:
Dear Allen: Still have questions. Sorry to bother you again.

Here is my design for your inspiration.

Assuming Radio butoon on form "Choose Sort" in option group "grpSort" with
values and captions:
1. ID (primary key) also default value for grpSort
2. Fst Name
3. Lst Name
Also one command button "pvwrpt" to open report "Myreport" in preview
view.

Then, the question is: where should I add this following codes???
Select Case Forms!ChooseSort!grpSort
Case 1' ID
Me.GroupLevel(0).ControlSource ="[ID]"
Me.GroupLevel(1).ControlSource ="[Fst Name]"
Me.GroupLevel(2).ControlSource ="[Lst Name]"
Case 2 'Fst Name
Me.GroupLevel(0).ControlSource ="[Fst Name]"
Me.GroupLevel(1).ControlSource ="[ID]"
Me.GroupLevel(2).ControlSource ="[Lst Name]"
Case 3 'LstName
Me.GroupLevel(0).ControlSource ="[LstName]"
Me.GroupLevel(1).ControlSource ="[ID]"
Me.GroupLevel(2).ControlSource ="[Fst Name]"
End Select

Also if I want dynamic sorting, yousuggests me to use the following code
when Report "MyReport" is open:
Dim strDoc As String
strDoc="Myreport"
If IsLoaded(strDoc) Then
DoCmd.Close aReport, strDoc
DoCmd.OpenReport strDoc, acViewPreview
End If
How come IsLoaded is not predefined at the VB???
(the debugger is on)

Thank you for giving me more instructions.....


Allen Browne said:
As stated, you can set the report's OrderBy while it is open, but you can
only use Report_Open to reassign the ControlSource of the GroupLevel.

So, to use the method in the webpage, the button on your form would need
to
close the report and reopen it, so Report_Open can pick up the new
setting
from your form:
Dim strDoc As String
strDoc = "Report1"
If IsLoaded(strDoc) Then
DoCmd.Close acReport, strDoc
DoCmd.OpenReport strDoc, acViewPreivew
End If

Dear Allen:
Thank you very much. I read this article already.
If by default, this report is sorted by ID.

In your case provided, which event or method or trigger the " case 1"
or
"case 2" so the report will be resorted. Take a look at the method you
provide, after the report is open, should another "Form" popup so the
user
can select another sorting method???
Thanks!!!

:

A report's sorting is determined by what is in its Sorting and
Grouping
box
(View menu.)

If there is nothing in that dialog, you can set the report's OrderBy
property after it is open, e.g.:
Reports!Report1.OrderBy = "CustLastName"
Reports!Report1.OrderByOn = True

It is also possible to programmatically change the order of the items
in
the
Sorting and Grouping box, by assigning the ControlSource of the
GroupLevel
in the Open event of the report. For an example, see:
Sorting Records in a Report at run-time
at:
http://allenbrowne.com/ser-33.html

Can Access have dynamic Sorting in Report?

I am after the first report is generated, can the user click at the
different field, so the report will be resorted???
If not, how can I use "Form" to achieve this goal???

EX: the report read:
ID CustFirstName CustLastName State.....

By default, the report sorts by ID, but if customer "select"(by
which
method?) State or CustFirstName or CustLastName, then the report
will
be
resorted!!!
Click to expand...
Click to expand...
Click to expand...
 

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

Weekly Report 2
Cross Tab Report Vs Dynamic coulmns 0
Trying to create a report that will list unmatched records by Ticket Primary Key 0
Average cases handled per agent in Sorting and Grouping report 0
Automate shape report 0
dynamic report 4
Count funtion in report-I'm too rusty! 1
Access Report With Portrait and Landscape Settings 0

Top