sorting report same as form access 2007

[Michel Peeters]

I am sorting a report the same as a form (both have the same records)
On opening of the report:
With Forms("MyForm")
If .OrderByOn Then
Me.Order = .OrderBy
Me.OrderByOn = True
End If
End With
This worked fine in Access 2000.
In 2007 the orderby string contains all the previous sortfields; the report
does not accept this.
Is there a way to avoid this?
Michel

 

[Allen Browne]

There's a couple of issues here.

Firstly, the sort order in reports is determined by what you place in its
Sorting And Grouping pane, so the OrderBy property may not give the results
you expect.

Secondly, Access does not maintain the report's OrderByOn property reliably.
Your code limits it to only where this property is set, which may be the
problem.

I tried something similar to what you have in A2007, and found that this
worked:

Private Sub Report_Open(Cancel As Integer)
If CurrentProject.AllForms("MyForm").IsLoaded Then
With Forms("MyForm")
If .OrderByOn Then
Me.OrderBy = .OrderBy
Me.OrderByOn = True
End If
End With
End If
End Sub

 

[Michel Peeters]

Allen, tks but this did not help.
Problem is since Access 2007 the orderby string from the form gets longer
every time the user clicks the sort button in the (sub)form: all previous
sorted fieldnames seperated with a comma.
Furthermore (this already happened in Access2000), if the report is opened
after the first sort click from the user, the orderby string starts with
the formname + a dot.
I solved it as follows and it works:

In Report Open event:
Dim strS As String
Dim intT As Integer
strS = Forms![frmMain]![FrmMainChild].Form.OrderBy
If Len(strS) > 1 And Left(strS, 4) = "[frm" Then '("frm" are the 3
first letters of the formname - if len(strs) = 0: the user did not choice a
sortorder in the form)
intT = InStr(strS, ".")
If intT > 0 Then
strS = Right(strS, Len(strS) - intT)
Else
strS = strS
End If
End If
intT = InStr(strS, ",")
If intT > 0 Then
strS = Left(strS, intT - 1)
End If
Me.OrderBy = strS
Me.OrderByOn = True

michel