Solved

Using Filter and Order By when setting RecordSource as SQL

Posted on 2011-03-24
5
727 Views
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?
0
Comment
Question by:rodneygray
  • 3
  • 2
5 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility

inter change the position  


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


   me.filter = "[Components] = True"
   me.filteron = true
0
 
LVL 1

Author Comment

by:rodneygray
Comment Utility
capricorn1
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
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
change

Me.OrderBy = [Description]

with

Me.OrderBy = "[Description]"
0
 
LVL 1

Author Closing Comment

by:rodneygray
Comment Utility
Capricorn1,
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?
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

743 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

17 Experts available now in Live!

Get 1:1 Help Now