Solved

Sort a query by different fields deepening on user input.

Posted on 2014-01-08
9
290 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 37

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 37

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

739 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