Using Filter and Order By when setting RecordSource as SQL
Posted on 2011-03-24
Maybe someone can confirm an issue with Access Forms. I have a form that allows a user to select an ingredient. Because there are so many ingredients, I allow the user to enter a phrase in a search field. Then, based on their entry, I display records that contain that phrase. I do this by setting the recordsource to:
Me.RecordSource = "SELECT tblIngredients.* FROM tblIngredients WHERE Description Like '*" & Me.txtSearch & "*'"
However, first, lets look at the OnLoad event. In the OnLoad event I set the recordsource and order by options
Me.RecordSource = "SELECT tblIngredients.* FROM tblIngredients"
Me.OrderByOn = True
Me.Order = [Description]
Access ignores my OrderBy statements.
If I delete the RecordSource statement and just set the source on the form to tblIngredients, The OrderBy statements work.
Thus my question is, will the OrderBy statements NOT WORK when setting RecordSource equal to a SQL statement?
I have the same issue when I try to use the Filter commands. I have a button that the user can click. When they do so, only records that have a field in the table (Components) clicked (set to True). If, with RecordSource statement in effect, I use the filter commands in the following format:
me.filteron = true
me.filter = "[Components] = True"
This does not work with record source either.
Am I doing something wrong or is this true with Access?