Setting two sort levels in a subform

  • Thread starter Thread starter JohnB
  • Start date Start date
J

JohnB

Hi. I am using Access 2002 in XP. I have a subform set
into a form which sits on a Tab control page. I want to be
able to present the subform records sorted first by one
field and then within that, by another field. Both fields
are shown in the subform. Is this possible? Ive had a look
at OrderBy but I cant see how I could use it as some sort
of Event in this case. I believe my use of Tab Control
means that the form is opened only once per session, so
the OnOpen Event wouldnt be useful. Thanks JohnB
 
If you always want it sorted this way, create a query to use as the
RecordSource of the subform.

If you want it changed dynamically, you can name more than one field in its
OrderBy clause, and remember to set OrderByOn as well:
Me.OrderBy = "[Field1], [Field2]"
Me.OrderByOn = True
 
Thanks for this Allen. Where do I put the code mentioned
in your second paragraph? Do you mean in the queries SQL
code or in some sort of Event? I thought you could only
sort by one field in a query. Thanks again, JohnB

-----Original Message-----
If you always want it sorted this way, create a query to use as the
RecordSource of the subform.

If you want it changed dynamically, you can name more than one field in its
OrderBy clause, and remember to set OrderByOn as well:
Me.OrderBy = "[Field1], [Field2]"
Me.OrderByOn = True

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

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

Hi. I am using Access 2002 in XP. I have a subform set
into a form which sits on a Tab control page. I want to be
able to present the subform records sorted first by one
field and then within that, by another field. Both fields
are shown in the subform. Is this possible? Ive had a look
at OrderBy but I cant see how I could use it as some sort
of Event in this case. I believe my use of Tab Control
means that the form is opened only once per session, so
the OnOpen Event wouldnt be useful. Thanks JohnB
Click to expand...


.
Click to expand...
 
To sort by multiple fields in a query:
- Open the query in design view.
- Enter "Ascending" in the Sorting row under the first field.
- Enter Ascending under the 2nd and subsequent fields as well.
The main sort field must be to the left of the other one.

To change it through code, the Open event of the subform would do.

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

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

JohnB said:
Thanks for this Allen. Where do I put the code mentioned
in your second paragraph? Do you mean in the queries SQL
code or in some sort of Event? I thought you could only
sort by one field in a query. Thanks again, JohnB

-----Original Message-----
If you always want it sorted this way, create a query to use as the
RecordSource of the subform.

If you want it changed dynamically, you can name more than one field in its
OrderBy clause, and remember to set OrderByOn as well:
Me.OrderBy = "[Field1], [Field2]"
Me.OrderByOn = True

Hi. I am using Access 2002 in XP. I have a subform set
into a form which sits on a Tab control page. I want to be
able to present the subform records sorted first by one
field and then within that, by another field. Both fields
are shown in the subform. Is this possible? Ive had a look
at OrderBy but I cant see how I could use it as some sort
of Event in this case. I believe my use of Tab Control
means that the form is opened only once per session, so
the OnOpen Event wouldnt be useful. Thanks JohnB
Click to expand...
Click to expand...
Click to expand...
 
Thanks again Allen. Thats something new to me - I didnt
know you could sort by more than one field in a query.
Just one final point - because my subform is in a form on
a Tab Conrol, the subform only gets Opened once during a
session. If I wanted to use code to do this, what Event
should I use to get the code to run when the user clicks
on the Forms Tab after being in some other Tabs Form?
OnOpen would surely only run the code when the user first
opens the database. Thanks, JohnB

-----Original Message-----
To sort by multiple fields in a query:
- Open the query in design view.
- Enter "Ascending" in the Sorting row under the first field.
- Enter Ascending under the 2nd and subsequent fields as well.
The main sort field must be to the left of the other one.

To change it through code, the Open event of the subform would do.

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

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

Thanks for this Allen. Where do I put the code mentioned
in your second paragraph? Do you mean in the queries SQL
code or in some sort of Event? I thought you could only
sort by one field in a query. Thanks again, JohnB

-----Original Message-----
If you always want it sorted this way, create a query
Click to expand...
to
use as the
RecordSource of the subform.

If you want it changed dynamically, you can name more than one field in its
OrderBy clause, and remember to set OrderByOn as well:
Me.OrderBy = "[Field1], [Field2]"
Me.OrderByOn = True

Hi. I am using Access 2002 in XP. I have a subform set
into a form which sits on a Tab control page. I want
Click to expand...
to
be
able to present the subform records sorted first by one
field and then within that, by another field. Both fields
are shown in the subform. Is this possible? Ive had a look
at OrderBy but I cant see how I could use it as some sort
of Event in this case. I believe my use of Tab Control
means that the form is opened only once per session, so
the OnOpen Event wouldnt be useful. Thanks JohnB
Click to expand...
Click to expand...


.
Click to expand...
 
If you want the subform to behave differently whenever the user changes to a
different page of the tab control in the main form, use the Change event of
the Tab control.

Compare the Value of the tab control to the PageIndex of the page.
This kind of thing:

Private Sub MyTab_Change()
With Me.MySub.Form
Select Case Me.MyTab.Value
Case Me.MyPage1.PageIndex
.OrderBy = "Field1, Field2"
.OrderByOn = True
Case Me.MyPage2.PageIndex
.OrderBy = "Field2, Field1"
.OrderByOn = True
Case Else
MsgBox "Huh?"
End Select
End With
End Sub

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

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

JohnB said:
Thanks again Allen. Thats something new to me - I didnt
know you could sort by more than one field in a query.
Just one final point - because my subform is in a form on
a Tab Conrol, the subform only gets Opened once during a
session. If I wanted to use code to do this, what Event
should I use to get the code to run when the user clicks
on the Forms Tab after being in some other Tabs Form?
OnOpen would surely only run the code when the user first
opens the database. Thanks, JohnB

-----Original Message-----
To sort by multiple fields in a query:
- Open the query in design view.
- Enter "Ascending" in the Sorting row under the first field.
- Enter Ascending under the 2nd and subsequent fields as well.
The main sort field must be to the left of the other one.

To change it through code, the Open event of the subform would do.


Thanks for this Allen. Where do I put the code mentioned
in your second paragraph? Do you mean in the queries SQL
code or in some sort of Event? I thought you could only
sort by one field in a query. Thanks again, JohnB


-----Original Message-----
If you always want it sorted this way, create a query to
use as the
RecordSource of the subform.

If you want it changed dynamically, you can name more
than one field in its
OrderBy clause, and remember to set OrderByOn as well:
Me.OrderBy = "[Field1], [Field2]"
Me.OrderByOn = True

message
Hi. I am using Access 2002 in XP. I have a subform set
into a form which sits on a Tab control page. I want to
be
able to present the subform records sorted first by one
field and then within that, by another field. Both
fields
are shown in the subform. Is this possible? Ive had a
look
at OrderBy but I cant see how I could use it as some
sort
of Event in this case. I believe my use of Tab Control
means that the form is opened only once per session, so
the OnOpen Event wouldnt be useful. Thanks JohnB
Click to expand...
Click to expand...
Click to expand...
 
OK Allen, Ill have a try with that. Thanks for sticking
with me on this one. Cheers, JohnB
-----Original Message-----
If you want the subform to behave differently whenever the user changes to a
different page of the tab control in the main form, use the Change event of
the Tab control.

Compare the Value of the tab control to the PageIndex of the page.
This kind of thing:

Private Sub MyTab_Change()
With Me.MySub.Form
Select Case Me.MyTab.Value
Case Me.MyPage1.PageIndex
.OrderBy = "Field1, Field2"
.OrderByOn = True
Case Me.MyPage2.PageIndex
.OrderBy = "Field2, Field1"
.OrderByOn = True
Case Else
MsgBox "Huh?"
End Select
End With
End Sub

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

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

Thanks again Allen. Thats something new to me - I didnt
know you could sort by more than one field in a query.
Just one final point - because my subform is in a form on
a Tab Conrol, the subform only gets Opened once during a
session. If I wanted to use code to do this, what Event
should I use to get the code to run when the user clicks
on the Forms Tab after being in some other Tabs Form?
OnOpen would surely only run the code when the user first
opens the database. Thanks, JohnB

-----Original Message-----
To sort by multiple fields in a query:
- Open the query in design view.
- Enter "Ascending" in the Sorting row under the first field.
- Enter Ascending under the 2nd and subsequent fields
Click to expand...
as
well.
The main sort field must be to the left of the other one.

To change it through code, the Open event of the
Click to expand...
subform
would do.
Thanks for this Allen. Where do I put the code mentioned
in your second paragraph? Do you mean in the queries SQL
code or in some sort of Event? I thought you could only
sort by one field in a query. Thanks again, JohnB


-----Original Message-----
If you always want it sorted this way, create a query to
use as the
RecordSource of the subform.

If you want it changed dynamically, you can name more
than one field in its
OrderBy clause, and remember to set OrderByOn as well:
Me.OrderBy = "[Field1], [Field2]"
Me.OrderByOn = True

message
Hi. I am using Access 2002 in XP. I have a subform set
into a form which sits on a Tab control page. I
Click to expand...
want
to
be
able to present the subform records sorted first by one
field and then within that, by another field. Both
fields
are shown in the subform. Is this possible? Ive had a
look
at OrderBy but I cant see how I could use it as some
sort
of Event in this case. I believe my use of Tab Control
means that the form is opened only once per
Click to expand...
session,
so
the OnOpen Event wouldnt be useful. Thanks JohnB
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top