Using Filter and Order By when setting RecordSource as SQL

Posted on 2011-03-24
Last Modified: 2012-05-11
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?
Question by:rodneygray
  • 3
  • 2
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35208130

inter change the position  

   Me.Order = [Description]
   Me.OrderByOn = True
and this

   me.filter = "[Components] = True"
   me.filteron = true

Author Comment

ID: 35209845
The second recordsource statement is commented out. It shows how I got around the OrderBy problem. When I implement your fix, I get an error when the form comes up.
The dialog box states:
  Header: Enter parameter value
  Body: Salt (This is the first ingredient in the table)
  Then there is an unbounded box waiting for me to type something in.
  Buttons: OK and CANCEL

If I type in a value or enter a value and press the OK button, from appears with unsorted value. If I press the CANCEL button, I get another error dialog box that states.
  Runtime error 3709
  The search key was not found in any record and I can end or debug. If I debug the error is highlighted at Me.OrderByOn = True.
Private Sub Form_Load()
    Me.RecordSource = "SELECT tblIngredients.* FROM tblIngredients"
     'Me.RecordSource = "SELECT tblIngredients.* FROM tblIngredients ORDER BY Description"
    Me.OrderBy = [Description]
    Me.OrderByOn = True
End Sub
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 500 total points
ID: 35209928

Me.OrderBy = [Description]


Me.OrderBy = "[Description]"

Author Closing Comment

ID: 35210814
Thanks so much. I spent several hours on this just trying to figure out why it did not work. All because of two quotes! Do you feel that is the case with the filter also?
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35210895
if [Components] is TEXT data type

me.filter = "[Components] = 'switch'"
   me.filteron = true

if [Components] is Number data type

me.filter = "[Components] = 1234"
   me.filteron = true

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Office 365 home questions 7 65
Trying to get public variables with login info accessible across entire project. 2 16
Calculation in Access 5 22
backup programme - VBA 3 22
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now