Expert help needed with code - Its driving me mad

[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]

 

[kctaylordotcodotuk]

Your explanation doesn't refer to the need to filter any of the records so I'm going to ignore the WHERE clause. If you need this then let me know and I'll amend the solution to suit.

Rather than tidy up the code you've pasted I offer a simpler solution. If it doesn't work as you wish then e-mail me your forms and table and I'll correct it in the way you want.

The following function should be in your main form and called from the two text boxes you mention by setting the OnClick property of both to

=ReOrder([Screen].[ActiveControl])


Public Function Reorder(txtField As String)
Dim txtOrder As String
txtOrder = Me![subFrmQrySelAllTracks2].Form.OrderBy
If Left(txtOrder, Len(txtField)) <> txtField Then
Me![subFrmQrySelAllTracks2].Form.OrderBy = txtField
ElseIf Right$(txtOrder, 5) = &quot; DESC&quot; Then
Me![subFrmQrySelAllTracks2].Form.OrderBy = txtField
Else
Me![subFrmQrySelAllTracks2].Form.OrderBy = txtField & &quot; DESC&quot;
End If

End Function

Strictly speaking the field name does not need to be passed to this function, as you could use the Screen.ActiveControl value directly in the function. This gives more flexibility as you can call the function with any fieldname, which could be different to the value of your text box.
[sig]<p>Keith C Taylor<br><a href=mailto:TekTips@kctaylor.co.uk>TekTips@kctaylor.co.uk</a><br><a href=

http://www.kctaylor.co.uk>The

Information Gardener</a><br>The C stands for Computer![/sig]

 

[kgreen]

Hi Keith,

First of all let me say thank you for your time and trouble, I really do appreciate it.

I followed your instructions to the letter and the &quot;Public Function Reorder(txtField As String)&quot; seems to work ok, well it compiles with out any problems anyway.

But I am now getting an error message with this next bit?
Error Message: The expression On Click you entered as the event property setting produced the following erroe - Ambiguous name detected: Artist_Click

Private Sub Artist_Click() <---In Debugger this is highlited in yellow
Reorder ([Screen].[ActiveControl]) <---This part of the line &quot;.[ActiveControl]&quot; is highlited in dark blue.
End Sub

Also this only seemed to work when a user clicks inside a text field on the sub Data sheet insted of on the column headings.

Any further suggestions ???

Regards,

Kenny
[sig][/sig]

 

[jimmythegeek]

An &quot;Ambiguous name detected&quot; error means you have that name in code more than once. Look around to make sure you don't have two &quot;Artist_Click&quot; procedures. If you do, delete one, and you shouldn't get the error again. [sig]<p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development[/sig]

 

[jagilman]

I created a sample form and subform to test some options for sorting the columns. In the subform header is a text box with a click event to toggle the sort order. when I open the main form and click in the subform header text box nothing happens, however, if I right click the column and change the sort somthing strange happens. The column sorts correctly but, now when I click in the text box it works just fine. I can toggle back and forth just fine. But it will only work after I use the little popup menu at least one time first?????????

John
[sig]<p>John A. Gilman<br><a href=mailto:gms@uslink.net>gms@uslink.net</a><br>[/sig]

 

[kctaylordotcodotuk]

There's no need to code the Click_Event. Just type =ReOrder([Screen].[ActiveControl]) in the OnClick property of the Column Heading field. The Column Heading field as I understand your first post, is a Text Field (let's say txtArtistHeading) and the ControlSource should be set to =&quot;Artist&quot;.
As with many things in Access, there are a few ways to get this to work. My example was aimed at what I understood to be the way you had designed your forms. If it still isn't working please let me know more detail about the field names (and data) you have used.

[sig]<p>Keith C Taylor<br><a href=mailto:TekTips@kctaylor.co.uk>TekTips@kctaylor.co.uk</a><br><a href=

http://www.kctaylor.co.uk>The

Information Gardener</a><br>The C stands for Computer![/sig]

 

[kctaylordotcodotuk]

jagilman,

That certainly is strange. The only thing I can think of off the top of my head is that the OrderByOn property has been set to False. Try adding

Me![subFrmQrySelAllTracks2].Form.OrderByOn = True

at the beginning of the function
[sig]<p>Keith C Taylor<br><a href=mailto:TekTips@kctaylor.co.uk>TekTips@kctaylor.co.uk</a><br><a href=

http://www.kctaylor.co.uk>The

Information Gardener</a><br>The C stands for Computer![/sig]

 

[jagilman]

Thank you Keith C Taylor
your suggestion worked!

the following code is very simple and works from the click event. All kgreen needs to do is check the form's orderby field after right clicking it and setting the order, then copy and use that value to set the click event for the text box. I dont see any need to try and build a query when access will do it for you.

Private Sub Form_Open(Cancel As Integer)
Me.OrderByOn = True
End Sub

Private Sub Text6_Click()
If text6OrderByOn Then
'Forms(&quot;Form2&quot(&quot;Form5&quot.Form.OrderBy = &quot;Table2.LName DESC&quot;
Me.OrderBy = &quot;Table2.LName DESC&quot;
text6OrderByOn = False
Me.Repaint
Else
'Forms(&quot;Form2&quot(&quot;Form5&quot.Form.OrderBy = &quot;Table2.LName&quot;
Me.OrderBy = &quot;Table2.LName&quot;
text6OrderByOn = True
Me.Repaint
End If

End Sub
[sig]<p>John A. Gilman<br><a href=mailto:gms@uslink.net>gms@uslink.net</a><br>[/sig]

 

[kgreen]

Hi John,

Many thanks for your reply and suggestion.

Just to explain, the original code that I am trying to adapt came free from the Aldex Software home page.

I really do feel very dumb at this point because despite everyones kind help I still cant get this to work, I know I am missing a point that is probibly very simple.

Below is your code but adapted to my db, any idea where I am going wrong???

Thank you again,

Kenny Green

****Code Start****
Private Sub Form_Open(Cancel As Integer)
Me.OrderByOn = True
End Sub

Private Sub Form_Click()
If FormOrderByOn Then
'Forms(&quot;Form2&quot(&quot;Form5&quot.Form.OrderBy = &quot;Table2.LName DESC&quot;
Me.OrderBy = &quot;SubFrmQrySelAllTracks2.[Artist] DESC&quot;
FormOrderByOn = False
Me.Repaint
Else
'Forms(&quot;Form2&quot(&quot;Form5&quot.Form.OrderBy = &quot;Table2.LName&quot;
Me.OrderBy = &quot;SubFrmQrySelAllTracks2.[Artist]&quot;
FormOrderByOn = True
Me.Repaint

End If

End Sub
****Code End**** [sig][/sig]

 

[kgreen]

ATTENTION EVERYBODY

May I just extend my grateful thanks to everyone who has kindly replied to my post so far.

I really do appreciate your time and effort.

Regards,

Kenny Green [sig][/sig]

 

[kgreen]

STOP THE PRESS - PROBLEM SOLVED

Guys n Gals, since my last post, an &quot;out of the blue&quot; e-mail 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]

 

[Guest_imported]

You are saying &quot;order by &quot; &amp; Artist, which means it will evaluate to &quot;order by boy george&quot; or &quot;order by metallica&quot; but these columns don't exist.

You need to specify a column name, so &quot;order by artist&quot; instead.

hope this helps