Dynamic Sorting in Report

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Vivi" wrote in message
...
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!!!

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

"Allen Browne" wrote:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Vivi" wrote in message
...
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!!!

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
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Vivi" wrote in message
...
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!!!

"Allen Browne" wrote:

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

"Vivi" wrote in message
...
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!!!

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" wrote:

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
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Vivi" wrote in message
...
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!!!

"Allen Browne" wrote:

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

"Vivi" wrote in message
...
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!!!

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Vivi" wrote in message
...
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" wrote:

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

"Vivi" wrote in message
...
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!!!

"Allen Browne" wrote:

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

"Vivi" wrote in message
...
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!!!

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" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Vivi" wrote in message
...
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" wrote:

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

"Vivi" wrote in message
...
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!!!

"Allen Browne" wrote:

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

"Vivi" wrote in message
...
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!!!