Help - Clicked col for sort installs header as order by?

[Rich]

Sorting a results set in datasheet view, installs the field header as the new
order by clause in design view (though it doesn't show up in SQL view as an
order by clause for some reason).

This is such an amazingly counter productive 'feature'. I'm using Access
2003, is there any way to turn this behavior off forever?

Regards,

Rich.

 

[Tom Wickerath]

Hi Rich,

I assume you are talking about the Order By property in queries. Is this
correct? If so, the answer is no. I agree that it is a bad feature for two
reasons:

1.) It will cause your query to be run two times, before the data is
displayed and
2.) Try renaming a field in a table, after a query has the same field
specified as the Order By property. Even with Name Autocorrupt enabled, you
should quickly discover how you've just produced a parameter prompt. (A saved
Order By in table design is updated correctly in this scenerio).

Reason number one is a pretty good reason to never expose queries directly
to users of your applications. You also lose the chance to do any form level
validation. So, if your users want to see a query-like output, use a
continuous form view instead. This way, you have all of the normal form
events available.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Ken Sheridan]

Rich:

I'd endorse everything Tom has said, but if you want to turn off the sort
order set by the OrderBy property, as distinct from the ORDER BY clause, for
all queries in a database you could do so by setting the OrderByOn property
of each to false like so:

Public Sub TurnOffQueryOrderBy()

Const PROPNOTFOUND = 3270
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb

For Each qdf In dbs.QueryDefs
On Error Resume Next
qdf.Properties("OrderByOn") = False
Select Case Err.Number
Case 0
'no error so do nothing
Case PROPNOTFOUND
' anicipated error so do nothing
Case Else
' unknown error so inform user
MsgBox Err.Description
End Select
Next qdf

End Sub

This won't stop it being reset to True by a user sorting a column in
datasheet view and then saving the query, but that really takes us back to
what Tom said about not exposing query results to users in raw datasheet
view.

 

[Tom Wickerath]

Hi Ken,

Very limited testing on my part so far, but this seems to work as well:

Public Sub TurnOffQueryOrderBy()

Const PROPNOTFOUND = 3270
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb

For Each qdf In dbs.QueryDefs
On Error Resume Next
If Len(qdf.Properties("OrderBy") & "") > 0 = True Then
qdf.Properties.Delete "OrderBy"
End If
Next qdf

MsgBox "All Done.", vbInformation, "Finished Resetting All Queries..."
End Sub


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Rich]

Thanks guys.

The user is me. I've got a bunch of machine data that once I get pulled,
just ad-hoc sort by things like response time, transaction count, etc.., then
back to a different LPar or CICS region, so the where clause changes, but
with changes to the query (calc 'd column taken out that WAS sort order), i'm
back into the process of getting to the order by property. Really annoying,
but I'll get over it.

Thanks again.

 

[Tom Wickerath]

Hi Rich,

You might want to try implementing the Query By Form (QBF) technique, where
one creates the WHERE clause of a SQL statement on-the-fly (I'm not talking
about the rather limp QBF that is built into Access). I recently presented
these methods to the Seattle Access User Group, during the January and
February meetings. You can get copies of the downloads from this site:

http://www.seattleaccess.org/downloads.htm

The February presentation builds upon the January presentation, so make sure
to try the earlier one out before the later one. You can even include the
ability to easily sort the datasheet by whatever field you want.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Rich]

Tom,

Checked the site and not sure which download to select to get the quick
overview. Though I can imagine what QBF would be. Do agree the QBE template
in Access is weak. Buried now, but have saved the site and will go back, in
what looks like about a week, based on my workload.

Of course, being an old mainframer, I'm, by definition, a little set in my
ways and am used to the wide open palette in QMF and SPUFI. It's never
failed me.

Regards,

Rich

 

[Tom Wickerath]

Hi Rich,

The February, 2008 download. You might also want to check out the January,
2008 download for an easier, but less powerful, method of QBF.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________