HELP - I have been trying to cure this problem for a week or so now andits driving me mad. Forgive me for going into detail but I want to makesure I state all the correct information.I have asked in the relivant news groups but so far my post was either not asnwered or the ***TWO*** answers I did get, did not work. If any of the experts in here can help then I will be in your debt.This subroutine below was written for use in continuous forms where youwant to sort and then re-sort the columns by clicking on their headings(as in Windows Explorer)and came free from the Aldex software site.I am trying to adapt it for my own use and failing terribly.I am trying to this function to a form based on a table called[TblQrySelAllTracks] and displayed as a datasheet. To be honest, I ammaking a right mess of it. If you can help then I would be in your debt.I have a form [FrmQrySelAllTracks] and a sub form[subFrmQrySelAllTracks2]. The sub form is a datasheet that displays thecontents of a table [tblFrmQrySelAllTracks]. The 2 fields or columns(not sure of correct name) in the subform [subFrmQrySelAllTracks2] thatI wish to sort are [txtArtist] & [txtTitle].I know it can from the menu bars but when we compile the final *.mda,we want to remove the menu bars???I have changed the code as best I can to suit my needs but when I clickon a column heading I get the following error message:run time error '3138' syntax error in Order By clause. I have markedthe actual line of code below that debug highlites as being wrong.PLEASE, if you can tell me where I am going wrong, I would reallyappreciate it.Thank you in advance,Roger.In the original code in the On Click event of a text box placed aboveeach column you this subroutine with the Value argument set to the nameof the appropriate field (ie the name of the field in the underlyingtable/query). The use of a subroutine is ment to make it a genericsolution for the form so you don't have code each column's sortindependently.The use of code like this (as compared to a forms sort order)is thatyou are ment to be to be able to add other functionality (such asmaking it re-sort in reverse order) when you click on the same headingagain.****(Code Start)****Public Sub ReOrder(Artist)Dim strRecSource As String, strSortOrder As String'The value for strRecSource will obviously be different in yourapplication. 'strRecSource = "Select * from TblQrySelAllTracks_TEMP WHERETblQrySelAllTracks_TEMP.Artist = '" & Me.Artist & "' order by [" &Artist & "]" 'strSortOrder = ""strRecSource = "SELECT [TblQrySelAllTracks_TEMP].[TrackID],[TblQrySelAllTracks_TEMP].[Library No], [TblQrySelAllTracks_TEMP].[Artist], [TblQrySelAllTracks_TEMP].[Title], [TblQrySelAllTracks_TEMP].[Mix], [TblQrySelAllTracks_TEMP].[BPM], [TblQrySelAllTracks_TEMP].[CDNumber], [TblQrySelAllTracks_TEMP].[No], [TblQrySelAllTracks_TEMP].[Length], [TblQrySelAllTracks_TEMP].[Composer],[TblQrySelAllTracks_TEMP].[Publisher] from TblQrySelAllTracks_TEMPWHERE TblQrySelAllTracks_TEMP.Artist = '" & Artist & "' order by [" &Artist & "]"strSortOrder = ""'If the current RecordSource is already set to 'Artist' then reversethe sort orderIf InStr(1, Me.RecordSource, Artist, vbTextCompare) > 1 And InStr(1,Me.RecordSource, " DESC", 0) = 0 ThenstrSortOrder = ""End IfTHIS IS THE LINE GENERATIONG A ERROR MESSAGE'Now apply the modified RecordSource (and sort order if appropriate)Me.RecordSource = strRecSource & " order by [" & Artist & "] " &strSortOrderEnd Sub****Code End****
[sig][/sig]
[sig][/sig]
