Solved

Sort a query by different fields deepening on user input.

Posted on 2014-01-08
9
285 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
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…
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.

867 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

20 Experts available now in Live!

Get 1:1 Help Now