OrderBy toggle issue.

MainForm source is employee table and Subform's source is a query based on
employee table that does a propercase format in the seperate name fields
[namesort] was added to sort these since the seperate last, sfx, first and
middle were not referenced in the query.
Data is in the table in SSN order (default from the load method used)

MainForm shows detail for a single record (Single form view)
SubFrom shows all rows (name & title in Continuous form view)
I am using "DoCmd.GoToRecord" to change the mainform record displayed to be
the same record number as the selected sub-form record.

Using this event to toggle the sort of my data's record source...
lblName is a form header field in the SUBForm. So Me.Parent is a reference
to the MainForm.

Private Sub lblName_Click()
If Me.OrderByOn Then
Me.OrderBy = ""
Me.OrderByOn = False
Me.Parent.OrderBy = ""
Me.Parent.OrderByOn = False
Else
Me.OrderBy = "[namesort]" '<-same order as l,s,f,m would be
Me.OrderByOn = True
Me.Parent.OrderBy = "[Last Name], [name suffix], [first name], [middle
name]"
Me.Parent.OrderByOn = True
End If
Me.Requery
Me.Parent.Requery
End Sub

Initially the data looks like (indicating record number.name displayed)...
Sub Main
1.C 1.C
2.D
3.B
4.A

1.C is first because C's SSN is the lowest # stored in the table and IS
record #1

Clicking on the name label (sets and turns on the orderby options):
1.A 1.A
2.B
3.C
4.D

Note that the subform and mainform stay in sync as a result.

Clicking on the name label again (un-sets and turns off the orderby options):
But the data on the Mainform remains in the sorted order.

Sub Main
1.C 1.A <-Main sort does not change back WHY?
2.D
3.B
4.A

Me.Requery and Me.Parent.Requery: These stmts did not effect the way the
routine works. I added them to try and force the new orderby settings to
take effect. But it works the same with or without them.

--
Regards,
John

Problem solved...

I decided to change the code to set the parent form order(s) first and that
worked.

--
Regards,
John


"John Keith" wrote:

> MainForm source is employee table and Subform's source is a query based on
> employee table that does a propercase format in the seperate name fields
> [namesort] was added to sort these since the seperate last, sfx, first and
> middle were not referenced in the query.
> Data is in the table in SSN order (default from the load method used)
>
> MainForm shows detail for a single record (Single form view)
> SubFrom shows all rows (name & title in Continuous form view)
> I am using "DoCmd.GoToRecord" to change the mainform record displayed to be
> the same record number as the selected sub-form record.
>
> Using this event to toggle the sort of my data's record source...
> lblName is a form header field in the SUBForm. So Me.Parent is a reference
> to the MainForm.
>
> Private Sub lblName_Click()
> If Me.OrderByOn Then
> Me.OrderBy = ""
> Me.OrderByOn = False
> Me.Parent.OrderBy = ""
> Me.Parent.OrderByOn = False
> Else
> Me.OrderBy = "[namesort]" '<-same order as l,s,f,m would be
> Me.OrderByOn = True
> Me.Parent.OrderBy = "[Last Name], [name suffix], [first name], [middle
> name]"
> Me.Parent.OrderByOn = True
> End If
> Me.Requery
> Me.Parent.Requery
> End Sub
>
> Initially the data looks like (indicating record number.name displayed)...
> Sub Main
> 1.C 1.C
> 2.D
> 3.B
> 4.A
>
> 1.C is first because C's SSN is the lowest # stored in the table and IS
> record #1
>
> Clicking on the name label (sets and turns on the orderby options):
> 1.A 1.A
> 2.B
> 3.C
> 4.D
>
> Note that the subform and mainform stay in sync as a result.
>
> Clicking on the name label again (un-sets and turns off the orderby options):
> But the data on the Mainform remains in the sorted order.
>
> Sub Main
> 1.C 1.A <-Main sort does not change back WHY?
> 2.D
> 3.B
> 4.A
>
> Me.Requery and Me.Parent.Requery: These stmts did not effect the way the
> routine works. I added them to try and force the new orderby settings to
> take effect. But it works the same with or without them.
>
> --
> Regards,
> John