Requery not working after changing ORDER by in query SQL string

[John F]

Access 2003

I have a form, frmExample that has a query, qryTestQuery, as the Record
Source.

I copied the SQL from the SQL view into a string:

strTAFormQuery = "SELECT ….. “ _
& "FROM …" _
& "ORDER BY "

Then in respond to a command button on the form, I want to sort by a
different field:

CurrentDb.QueryDefs("qryTestQuery").SQL = strTAFormQuery & "tblTAData.chrIPT;"

Me.Requery


The problem is Me.Requery has no effect. I think the query is being
resorted because if I open the query directly, it shows the updated sort, but
the records on frmExample are not resorted.

What should I do to make the records on the form reflect the new sort?

------------------

I also use the filter property of the form. In case that was interfering
with the above, I tested this possibility by turning off the filter, but that
had no effect. Is it ok to continue to use the filter property instead of
including a WHERE clause in the SQL string? It would be more convenient for
me as there are several conditions setting the filter and I would rather
leave that alone.

------------------

Another question, if I want to sort by another field that has specific text
values, e.g. “Excellentâ€, “Goodâ€, “Fairâ€, “Poor†is there a way to define the
order of sorting, versus alphabetic?

Thanks,

John

 

[Allen Browne]

Might as well do the whole thing in your command button:

Private Sub Command1_Click()
Dim bFilterWasOn as Boolean
Private Const mstrcStub = "SELECT ... FROM ... "

If Me.Dirty Then Me.Dirty = False 'save any edits
bFilterWasOn = Me.FilterOn
Me.RecordSource = mstrcStub & " ORDER BY tblTAData.chrIPT;"
If bFilterWasOn Then Me.FilterOn = True
End Sub

For your final question, you need a lookup table, with a text field (for the
word) as a number field (to define the sorting.) You can then include the
lookup table in your query, and sort by the number.

Unless the text field is marked as required in your existing table, you want
to use an outer join here. Explanation in:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

 

[John F]

Thanks very much. If the form filter is always on, can I delete the lines
commented out?


Private Sub Command1_Click()
'Dim bFilterWasOn as Boolean
Private Const mstrcStub = "SELECT ... FROM ... " 'select does not have
WHERE because filter

'takes care of this

If Me.Dirty Then Me.Dirty = False 'save any edits
'bFilterWasOn = Me.FilterOn
Me.RecordSource = mstrcStub & " ORDER BY tblTAData.chrIPT;"
'If bFilterWasOn Then Me.FilterOn = True
End Sub

 

[Allen Browne]

Try it with your version of Access, and see.