Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sort a query by different fields deepening on user input.

Posted on 2014-01-08
9
Medium Priority
?
297 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 39

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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 1000 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 1000 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 39

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 48

Expert Comment

by:Dale Fye
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

704 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