Sorting records in a Forms Subform

[Noel]

Hi. Im using Access 2002 on XP. I had this working but
now its stopped for some reason. I wonder if anyone can
see the problem, or a better way to do things.

I am using a Tab Control form. One Tab calls up a form
called Students BEd Year 1 with a subform. On the subform
I have a Combo called cboPlacementStage. Each of the 8
entries in the cbo has a number assigned, 1 to 8. I have
code that ensures that, when a user choses a combo entry,
the corresponding number is put in
txtPlacementStageOrder. In the On Open Event of the
subform I have the following code - as suggested on this
newsgroup

Private Sub Form_Open(Cancel As Integer)
Me.OrderBy = "[txtPlacementStageOrder] Desc"
Me.OrderByOn = True
End Sub

This is supposed to order the subform records with , say,
4 at the top, then 3 then 2 etc. This was working, as I
say, but now its coming out exactly the opposite - in
Ascending order. This only happens when I open the form
from the Tab Control Form. If I open the form directly,
it works OK. I also have code in the Tab Forms On Change
Event as follows and when I comment out the top line, it
works!

If Me!TabCtl1 = Me![Bed Year 1].PageIndex Then Me!
[Students BEd Year 1].Requery
If Me!TabCtl1 = Me![Bed Year 1].PageIndex Then Me!
[Students BEd Year 1]![cboSubject].Requery
If Me!TabCtl1 = Me![Bed Year 1].PageIndex Then Me!
[Students BEd Year 1]![Placements Subform]!
[SchoolName].Requery
If Me!TabCtl1 = Me![Bed Year 1].PageIndex Then Me!
[Students BEd Year 1]![Placements Subform]![Associate
Tutor (Mentor) second].Requery
If Me!TabCtl1 = Me![Bed Year 1].PageIndex Then Me!
[Students BEd Year 1]![Placements Subform]!
[RolleTutorSecond].Requery

Obviously I must have changed something somewhere along
the line but I cant see it. I need the Tabs form to
requery each time the Tab is clicked but this seems to be
overriding the subforms On Open Event. Can anyone see
whats wrong here? Thanks, Noel

 

[Paul]

I think that the requery is overriding your sort order (the sort order of
the underlying query/table is being used instead). Try adding the code to
sort after your code to requery.

 

[Noel]

Paul. I hope you get this. I didnt get a reply to my post
for a while so I assumed no one was interested. I looked
today out of curiosity. Thanks for your comments. OK Ill
have a go at that and will repost if I still have
problems. Cheers, Noel

-----Original Message-----
I think that the requery is overriding your sort order (the sort order of
the underlying query/table is being used instead). Try adding the code to
sort after your code to requery.

Hi. Im using Access 2002 on XP. I had this working but
now its stopped for some reason. I wonder if anyone can
see the problem, or a better way to do things.

I am using a Tab Control form. One Tab calls up a form
called Students BEd Year 1 with a subform. On the subform
I have a Combo called cboPlacementStage. Each of the 8
entries in the cbo has a number assigned, 1 to 8. I have
code that ensures that, when a user choses a combo entry,
the corresponding number is put in
txtPlacementStageOrder. In the On Open Event of the
subform I have the following code - as suggested on this
newsgroup

Private Sub Form_Open(Cancel As Integer)
Me.OrderBy = "[txtPlacementStageOrder] Desc"
Me.OrderByOn = True
End Sub

This is supposed to order the subform records with , say,
4 at the top, then 3 then 2 etc. This was working, as I
say, but now its coming out exactly the opposite - in
Ascending order. This only happens when I open the form
from the Tab Control Form. If I open the form directly,
it works OK. I also have code in the Tab Forms On Change
Event as follows and when I comment out the top line, it
works!

If Me!TabCtl1 = Me![Bed Year 1].PageIndex Then Me!
[Students BEd Year 1].Requery
If Me!TabCtl1 = Me![Bed Year 1].PageIndex Then Me!
[Students BEd Year 1]![cboSubject].Requery
If Me!TabCtl1 = Me![Bed Year 1].PageIndex Then Me!
[Students BEd Year 1]![Placements Subform]!
[SchoolName].Requery
If Me!TabCtl1 = Me![Bed Year 1].PageIndex Then Me!
[Students BEd Year 1]![Placements Subform]![Associate
Tutor (Mentor) second].Requery
If Me!TabCtl1 = Me![Bed Year 1].PageIndex Then Me!
[Students BEd Year 1]![Placements Subform]!
[RolleTutorSecond].Requery

Obviously I must have changed something somewhere along
the line but I cant see it. I need the Tabs form to
requery each time the Tab is clicked but this seems to be
overriding the subforms On Open Event. Can anyone see
whats wrong here? Thanks, Noel

.