Solved

Sort a query by different fields deepening on user input.

Posted on 2014-01-08
9
282 Views
Last Modified: 2014-01-08
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?
0
Comment
Question by:threeieng
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 39765256
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?
0
 

Author Comment

by:threeieng
ID: 39765316
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.
0
 

Author Comment

by:threeieng
ID: 39765473
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
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
ID: 39765611
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

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 61

Expert Comment

by:mbizup
ID: 39765618
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

0
 

Author Comment

by:threeieng
ID: 39765682
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?
0
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
ID: 39765685
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

0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39765707
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39766156
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.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

705 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

18 Experts available now in Live!

Get 1:1 Help Now