Expert HELP with code farse - I am on my knee's begging

[kgreen]

HELP - I have been trying to cure this problem for a week or so now and
its driving me mad. Forgive me for going into detail but I want to make
sure 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 you
want 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 am
making 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 the
contents of a table [tblFrmQrySelAllTracks]. The 2 fields or columns
(not sure of correct name) in the subform [subFrmQrySelAllTracks2] that
I 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 click
on a column heading I get the following error message:
run time error '3138' syntax error in Order By clause. I have marked
the actual line of code below that debug highlites as being wrong.

PLEASE, if you can tell me where I am going wrong, I would really
appreciate it.

Thank you in advance,

Roger.


In the original code in the On Click event of a text box placed above
each column you this subroutine with the Value argument set to the name
of the appropriate field (ie the name of the field in the underlying
table/query). The use of a subroutine is ment to make it a generic
solution for the form so you don't have code each column's sort
independently.

The use of code like this (as compared to a forms sort order)is that
you are ment to be to be able to add other functionality (such as
making it re-sort in reverse order) when you click on the same heading
again.

****(Code Start)****
Public Sub ReOrder(Artist)
Dim strRecSource As String, strSortOrder As String

'The value for strRecSource will obviously be different in your
application.
'strRecSource = "Select * from TblQrySelAllTracks_TEMP WHERE
TblQrySelAllTracks_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].[CD
Number], [TblQrySelAllTracks_TEMP].[No], [TblQrySelAllTracks_TEMP].
[Length], [TblQrySelAllTracks_TEMP].[Composer],
[TblQrySelAllTracks_TEMP].[Publisher] from TblQrySelAllTracks_TEMP
WHERE TblQrySelAllTracks_TEMP.Artist = '" & Artist & "' order by [" &
Artist & "]"
strSortOrder = ""

'If the current RecordSource is already set to 'Artist' then reverse
the sort order
If InStr(1, Me.RecordSource, Artist, vbTextCompare) > 1 And InStr(1,
Me.RecordSource, " DESC", 0) = 0 Then
strSortOrder = ""
End If


THIS IS THE LINE GENERATIONG A ERROR MESSAGE
'Now apply the modified RecordSource (and sort order if appropriate)
Me.RecordSource = strRecSource & " order by [" & Artist & "] " &
strSortOrder

End Sub
****Code End****


[sig][/sig]

 

[PaulF]

wouldn't it just be a lot easier to use the Sort Options provided by the Right Mouse Click Shortcut Menu ?

PaulF [sig][/sig]

 

[kgreen]

Hi Paul,

Many thanks for your reply.

Hi Paul,

Many thanks for your reply.

Well yes it would be easyier to do the "Easy way" Mouse right click thing but that isn't the point is it.

I am teaching myself VD-A/Access design and I want to do it the way I have asked because I KNOW it can be done and I want to learn the correct codeing so I can apply it to future projects. After all, If a client asks a db designer for the kind of function I am asking help with, then the db designer should be able to give the client what he want's. I thought the whole point was to design db's with more functions and flexability than the standard methods offer.

Thank you again for your reply,

K Green [sig][/sig]