Friends, Romans & Experts - Still need URGENT help with code

[kgreen]

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.
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



[sig][/sig]

 

[THoey]

You'll have to excuse me, a) I have never tried to do this type of function and b) I am still suffering from last nights AC / DC concert. But I think I see something through the fog.

Prior to the line that is erroring, you build the query string with the ORDER BY clause in it.

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 & "]"

Then, when you modify the RecordSource, you append an ORDER BY clause to the end of that string (again).

Me.RecordSource = strRecSource & " order by [" & Artist & "] " &
strSortOrder

That would effectively make your query have two ORDER BY clauses which would cause Access to crash.

Shouldn't you create another string that contains the ORDER BY clause, change it as necessary, and then append it as you are doing?

Hope that helps. I think I'll go take some Tylenol...
[sig]<p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?[/sig]

 

[kgreen]

STOP THE PRESS - PROBLEM SOLVED

Guys n Gals, an &quot;out of the blue&quot; e-maqil solved my problem. A very kind person sent me the following generic code that not only allows me to click the two columns I wish to sort but ANY column on the form so I thought I'd share it with everyone.

Many many thanks to everyone who kindly replied to my post, I appreciate every reply.

Regards,

Kenny



Private Sub Form_Click()
Dim ctlCurrentControl As Control
Dim strControlName As String
Set ctlCurrentControl = Screen.ActiveControl
strControlName = ctlCurrentControl.Name
strFieldName = Me.Controls.Item(strControlName).ControlSource
ReSort CStr(strFieldName)

End Sub

Private Sub ReSort(strFieldName As String)
strFieldName = &quot;[&quot; & strFieldName & &quot;]&quot;
Me.OrderByOn = True
Select Case Me.OrderBy
Case strFieldName
Me.OrderBy = strFieldName & &quot; DESC&quot;
Case strFieldName & &quot; DESC&quot;
Me.OrderBy = strFieldName
Case Else
Me.OrderBy = strFieldName
End Select
Me.Requery
End Sub [sig][/sig]