The RecordSource query should always contain an order by. Sometimes you might want to support additional sort options or if you use a DS view subform, you have lots of built in options. Just make sure you have the form's properties set to not sort on open so you don't override your query.
The RecordSource query should always contain an order by. Sometimes you might want to support additional sort options or if you use a DS view subform, you have lots of built in options. Just make sure you have the form's properties set to not sort on open so you don't override your query.
Thank you Pat Hartman for your suggestion.
We have tried sorting in both of the queries, without any success.
Would you have an other suggestion to try?
In the DB you posted, the TransactionDate field is a date/time and the TransactionNumb field is text (digits) string with explicit leading zeros. You have one query that sorts the date field first (descending), then the number field. Here is the question: What order did you really want, and when you open the query with the ORDER BY clause, does it present the records in the order you want? If not, what order DOES it produce?
By the way, it would have been nice to identify which form is the one that misbehaves so that we don't have to guess.
You have the sort order defined in the subform (data tab), and that will take precedence over the sort order declared in the query.
Delete that sort order, and then the sort order will comply with the query.
However, you have to be careful, as when you close and save the form, you might save a new sort order with the form, and then you have the problem again. I also don't think you can manually set the sort order as Column A descending, Column B ascending (as you can do in the query)
If you release the database as an accde, then any temporary sort order won't be saved, as users can't save changes like that.
You might add a button to re-assert the default sort order, by deleting the current sort order.
I added a button at the bottom called btndefault and tried the code below. This works, but although it temporarily removes the sort order, it's not saving the subform with a blank sort order. I'm not sure why, and it would be a pain to have to keep doing this.
I'm actually struggling to find the right code to remove the sort order set in the form (subform) and save the change without a sort order.
Code:
Private Sub btnDefault_Click()
Me.OrderBy = ""
Me.Requery
End Sub