Access 2007 to 2010 OrderBy in VBA Not Working

[jjlogan]

I created an Access report 4 years ago using Access 2003 that is not bound to a query but using VBA to create SQL record source and also perform sorting - all based on user-selected fields and values they type into my dialog form (e.g. Accounting key contains ....) and Sort By ... (combobox). This report has been used very successfully in Access 2003 and 2007. But now our group is getting Office 2010 (32-bit) and the vba OrderBy and OrderByOn properties of the report are not working. The VBA statements are like this:
Me.OrderBy = "TotalTrans." & strSort1 & ", TotalTrans." & strSort2
Me.OrderByOn = True

I've looked on web and read about "Changes in Access 2010". The only change that seems relevant is the Registry has dropped VBA 6.0 and now includes only VBA 7.0.
Can anyone shed some light on why I can't sort (order) my records using VBA code that was used in 2007 version?
Thanks in advance for any help.
Jeff

 

[dhookom]

Can you share your more complete code? In all previous versions of Access, I would create some sorting and grouping levels in the design view and then use code to change their Control Source.

Duane
Hook'D on Access
MS Access MVP

 

[jjlogan]

A little more clarification, then will show some more code: I can open this Access file (.mdb), which is on our group's network drive, from my computer with Access 2007 and from my new computer with Access 2010. The sorting runs perfectly when I use Access 2007 to open the file, but no sorting when use Access 2010 version.
My code consists of an SQL string that 1) begins with Select clause, 2) then program goes through all fields on dialog form where user specified Where attributes, 3) and finally, last part of code is the Sorting portion that is based again on dialog form where user specificed what field(s) to sort on. The report runs in both 2010 and prior versions, except that sorting does not take in 2010 version:
1) Select Clause: (variables have already been dimensioned above this code)
strSQL = "SELECT TotalTrans.NC, TotalTrans.RESP, TotalTrans.NCRESP, " _
& " TotalTrans.RevItm as RevItm, TotalTrans.AMOUNT, TotalTrans.AbsAmt," _
& " TotalTrans.DATE, TotalTrans.PERIOD, TotalTrans.JOB, TotalTrans.SUB, TotalTrans.JOBSUB, " _
& " TotalTrans.[OR Ref #]," _
& " TotalTrans.OFC, TotalTrans.CO, TotalTrans.SOURCE_REF, TotalTrans.LINE_ITEM, " _
& " TotalTrans.SRC_CD, GroupPool.Group, GroupPool.Pool," _
& " SourceCodeDesc.SRC_DESC, TotalTrans.[Other Amt], TotalTrans.[Summary Analysis]," _
& " TotalTrans.[Date Reviewed], TotalTrans.[OtherCause],TotalTrans.[Reviewed By], " _
& " TotalTrans.Reviewed, TotalTrans.MemoAdjAmt," _
& " [Joblist].JOB_DESC, [Joblist].SJOB_DESC, NCLookupWithSummary.NC_Description, " _
& " GroupPool.Group, GroupPool.Pool, GroupPool.EX " _
& " FROM (((((TotalTrans LEFT JOIN [Joblist] ON TotalTrans.JOBSUB = [Joblist].JOBSUB)" _
& " LEFT JOIN SourceCodeDesc ON TotalTrans.SRC_CD = SourceCodeDesc.SRC_CD)" _
& " LEFT JOIN NCLookupWithSummary ON TotalTrans.NC = NCLookupWithSummary.NC)" _
& " LEFT JOIN PriorityCd ON TotalTrans.NCRESP = PriorityCd.NCRESP) " _
& " LEFT JOIN GroupPool ON TotalTrans.Jobsub = GroupPool.Jobsub) "

2) Where clause is built up as vba goes through each field on dialog form and based on null or not null, updates Where clause
3) Sort code: I generally use two levels of sort (cmbSort1 and cmbSort2) but even with one level version 2010 does not sort correctly.
' ----------------------------- Begin Sort Order -----------------------------
intSort = 0
If Forms![DesignYourOwnReportDialog].cmbSort1 <> "" Then intSort = intSort + 1
If Forms![DesignYourOwnReportDialog].cmbSort2 <> "" Then intSort = intSort + 2
If Forms![DesignYourOwnReportDialog].cmbSort3 <> "" Then intSort = intSort + 4

Select Case intSort
Case 0
MsgBox "Sort criteria not specified; therefore " & vbCrLf & "report to be sorted AbsAmt / Date.", vbInformation, "Alert"
Me.OrderBy = "TotalTrans.AbsAmt DESC, TotalTrans.DATE"
strSQL = strSQL & " ORDER BY TotalTrans.AbsAmt DESC, TotalTrans.DATE;"
strSortBy = "AbsAmt DESC / Date"
Case 1
strSort1 = Forms![DesignYourOwnReportDialog].cmbSort1
Me.OrderBy = "TotalTrans." & strSort1
strSQL = strSQL & " ORDER BY TotalTrans." & strSort1 & ";"
strSortBy = strSort1
Case 3
strSort1 = Forms![DesignYourOwnReportDialog].cmbSort1
strSort2 = Forms![DesignYourOwnReportDialog].cmbSort2
Me.OrderBy = "TotalTrans." & strSort1 & ", TotalTrans." & strSort2
strSQL = strSQL & " ORDER BY TotalTrans." & strSort1 & ", TotalTrans." & strSort2 _
& ";"
strSortBy = strSort1 & " / " & strSort2
Case 7
strSort1 = Forms![DesignYourOwnReportDialog].cmbSort1
strSort2 = Forms![DesignYourOwnReportDialog].cmbSort2
strSort3 = Forms![DesignYourOwnReportDialog].cmbSort3
Me.OrderBy = "TotalTrans." & strSort1 & ", TotalTrans." & strSort2 _
& ", Totaltrans." & strSort3
strSQL = strSQL & " ORDER BY TotalTrans." & strSort1 & ", TotalTrans." & strSort2 _
& ", Totaltrans." & strSort3 & ";"
strSortBy = strSort1 & " / " & strSort2 & " / " & strSort3
Case Else
MsgBox "Sort criteria must start w/ Box 1, then 2, etc." & vbCrLf & "Report to be sorted AbsAmt / Date.", vbInformation, "Alert"
Me.OrderBy = "TotalTrans.AbsAmt DESC, TotalTrans.DATE"
strSQL = strSQL & " ORDER BY TotalTrans.AbsAmt DESC , TotalTrans.DATE;"
strSortBy = "AbsAmt DESC / Date"
End Select

' Debug.Print "strSql" & vbCrLf & strSql 'For Testing
Me.RecordSource = strSQL

Me.OrderByOn = True
DoCmd.Hourglass False
'Set public variable to False, signifying that Open event is finished.
blnOpening = False

End Sub

 

[dhookom]

I'm not sure this will help but these are my first glance recommendations:

intSort = 0
[COLOR=#4E9A06]'avoid issues with nulls which are unknown values[/color]
If Forms![DesignYourOwnReportDialog].cmbSort1 & "" <> "" Then intSort = intSort + 1
If Forms![DesignYourOwnReportDialog].cmbSort2 & "" <> "" Then intSort = intSort + 2
If Forms![DesignYourOwnReportDialog].cmbSort3 & "" <> "" Then intSort = intSort + 4

Remove all ORDER BY from strSQL.

Remove the table name from the Order By property of the report and place []s around reserved words/function names. For instance:

[COLOR=#4E9A06]'Me.OrderBy = "TotalTrans.AbsAmt DESC, TotalTrans.DATE"[/color]
Me.OrderBy = "AbsAmt DESC, [DATE]"

Duane
Hook'D on Access
MS Access MVP

 

[jjlogan]

Unfortunately, these changes did not cause the 2010 version of Access to sort. I again went to my 2007 version on another computer and ran the same report in the same Access file on network - and it sorts just fine. [my selected sort was by job code, then by a field for absolute value of line item amount].
I will continue to look into this and if you get some ideas, I'm always happy to hear back.
Jeff

 

[dhookom]

I never use "Me.RecordSource = strSQL" with reports. I always base the report on a saved query. I then use something like:

'
   Dim strSQL as String
   [COLOR=#4E9A06]' code to set the string[/color] 
   Currentdb.QueryDefs("NameOfMyReportQuery").SQL = strSQL
   DoCmd.OpenReport ...

Duane
Hook'D on Access
MS Access MVP

 

[jjlogan]

I will try that, but will have to wait a few days as have other top priority work scheduled. What I see in this problem is that 2010 version is not working the same as 2007 version (which does the sorting). So, your suggestion here may be what 2010 version needs in order to sort.
Thanks. I will get back to you.