vba order by

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).
rick_dangerAsked:
Who is Participating?
 
acbxyzConnect With a Mentor Commented:
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
 
acbxyzCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
PatHartmanCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
" 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
 
rick_dangerAuthor Commented:
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
 
rick_dangerAuthor Commented:
That's brilliant. Thanks.
0
 
rick_dangerAuthor Commented:
Excellent solution, thanks very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.