Solved

vba order by

Posted on 2014-02-02
8
237 Views
Last Modified: 2014-02-02
I have the following code:
Me.OrderBy = "person_id"
Me.OrderByOn = True

where person_id is the form field's control source. However, I wish to use its name instead. Is there a way to do this.

So, for clarity, in the form design, the field on the form has a control source of "person_id" (from the "data" tab), but its name is "field_1" (from the "other" tab).
0
Comment
Question by:rick_danger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 10

Expert Comment

by:acbxyz
ID: 39827563
Not as a string, but you can use field_1.ControlSource without quotes. field_1 is the object of your control element and its property ControlSource contains the string.

Me.OrderBy = field_1.ControlSource
0
 
LVL 85
ID: 39827594
Why not just:

OrderBy = "Field_1"

The OrderBy property tells Access how you'd like to Order your records (obviously). It expects a Field name as the setting.
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39827838
Why would you care?  And why would you use meaningless values as the Name for a control?  When you use the wizard or when you drag fields from the field list, Access assigns the bound field name as the control name.  When you click on a control in the ribbon and draw it on the form/report, Access assigns an arbitrary name.  You should then change that to something meaningful.  Who wants to look at code that references text038 and chk120?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 75
ID: 39828053
" I wish to use its name instead. "
The OrderBy property requires the name of a Field(s) in the underlying Recordsource of the Form, not the Name of a Control.

mx
0
 

Author Comment

by:rick_danger
ID: 39828103
acbxyz - thanks for your answer, It works, so I'l happily give you the points, but how would I write this code if I also wanted to choose to sort ascending or descending?
0
 
LVL 10

Accepted Solution

by:
acbxyz earned 500 total points
ID: 39828127
field_1.ControlSource contains a string. You can concat this with other (maybe static) strings using the ampersand:

Me.OrderBy = field_1.ControlSource & " DESC"
0
 

Author Comment

by:rick_danger
ID: 39828145
That's brilliant. Thanks.
0
 

Author Closing Comment

by:rick_danger
ID: 39828147
Excellent solution, thanks very much.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

728 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