Solved

vba order by

Posted on 2014-02-02
8
229 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
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 84
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 34

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
 
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

746 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

12 Experts available now in Live!

Get 1:1 Help Now