Sort a query by different fields deepening on user input.

I have a query that has fields A, B, C, and D.  The query populates with data when opened and sorts by field A in ascending order.  I have an option box that has two buttons on the form.  When the user click B i want the query to update and sort ascending on field B.  Or if they desire they can pick the A button to sort ascending on field A.  What is the best way to accomplish this?
threeiengAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
1. Users should never be given naked queries.  Always present the query in a form so you can have event processing available to edit updates.  Use DataSheet view if the users want something that looks like a spreadsheet.
2. Right click in any field to bring up a menu that offers sorting.

If you want to alter the sorting by using a button, then you will need to recreate the SQL since the Order By clause is an integral part of the query and you can't parameterize it.  Why not just use the "free" built in feature?
threeiengAuthor Commented:
They aren't given a naked query at all.  The form loads and contains a custom view that populates with data from a query.  At the bottom of the form I created toggle buttons to change the way the query is sorted.  I don't want it to look anything like a spreadsheet.  What is the "free" built in feature I'm not using?

The query and form is much more complex than the example I provided.  Just trying to simplify things.
threeiengAuthor Commented:
I have attached a screen shot of my form.  You will see what I am trying to do.  It normally sorts by the number on the left hand side.  But there is a Customer name under the job number that they want the ability to sort by as well based on which button is selected on the bottom left side of the form.
Screen-Shot-2014-01-08-at-9.42.5.png
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Dale FyeOwner, Dev-Soln LLCCommented:
If your option group is name og_SortBy, you would use the AfterUpdate event of that frame/option group and do something like:

Private Sub og_SortBy_AfterUpdate

    if me.og_SortBy = 1 Then
        'sort by column A
        me.OrderBy = "[A]"
   Else
        me.OrderBy = "[B]"
   End if
   me.OrderByOn = true

End Sub

Open in new window

mbizupNerdCommented:
You can use an Option Group as mentioned in your original post to change your form's Order By Property.  In the Option group's after update event:

Dim strOrder as string

Select case Me.MyOptionGroupName
     Case 1:
          strOrder = "Field1"
    Case 2:
          strOrder = "Field2"
    Case 3:
          strOrder = "Field3"
' etc
End Select

Me.OrderBy = strOrder
Me.OrderByOn = true

Open in new window

threeiengAuthor Commented:
I didn't mention that the form where the data populates is a subform on the main form where the option buttons reside.  I can't get the orderby to work.  Can I use orderby on a subform?
mbizupNerdCommented:
The syntax would be a little different.

If the code is on the mainform:


Me.MysubformControlName.Form.OrderBy = ... etc...
Me.MysubformControlName.Form.OrderByON  = true

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
Right Click Menu I attached a picture of the right click menu.  Notice the sort and filter options.  This is available on all bound forms unless you turn it off.  The only downside is you can only sort on one column at a time.  If you need a compound sort, you will need to code it yourself.

If you are having trouble implementing the suggested code, it is probably because you are trying to do it from the parent form.  That means you need to reference the subform.  You can't just use "Me." since that refers to the active form.

Me.mysubform.Form.Orderby = strOrder

"mysubform" is the name of the CONTROL which may or may not be the same name as the form.  Open the mainform in design view.  Click on the subform control.  The Name property on the Other tab is the name you need to use.
Dale FyeOwner, Dev-Soln LLCCommented:
It appears that another comment I made did not get posted.

I wrote published an article about a month or so ago about a sorting technique I recently implemented for a client.  This allows you to sort on multiple fields, identify field priority in the sort, and determine whether to sort ascending or descending on any of those fields.  Check out the article here.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.