User Defined Sorting in Form

[sparlaman]

I have a form with tabs and subforms and such to be used to lookup and add/edit employee data as needed. I want the users to be able to sort the records by any of the visible fields. Like LastName or EmpID or whatever.



I have no clue how to program this. I've found the following code in another post on this site but I don't really understand it so don't know what to change to make it work for me. I'm a novice when it comes to VBA. Any help is GREATLY appreciated.

 
Private Sub Report_Open(Cancel As Integer)
  Select Case Forms!frmReports.fraGrouping
    Case 1 'sort/group on car type
      Me.GroupLevel(0).ControlSource = "CarType"
      Me.txtGroup.ControlSource = "CarDesc"
    Case 2 'sort/group on company
      Me.GroupLevel(0).ControlSource = "Company"
      Me.txtGroup.ControlSource = "Company"
    Case 3 'sort on date/time, no grouping
      Me.GroupLevel(0).ControlSource = "DispDateTime"
      Me.txtGroup.ControlSource = "DispDateTime"
      Me.GroupHeader0.Visible = False
  End Select
End Sub

thanks
sparlaman

[kennejd]

The user can already do this...if they right-click on any column and select 'sort'.

For vba code, you could add an orderby method to a button.

[sparlaman]

The form isn't datasheet view so there are not columns. I thought this was already possible too but and when I click inside a field and hit sort A-Z or Z-A nothing happens...

[NTC]

mmmm - I don't think the approach works. Since you have subforms, you are in single form view. So a sort really isn't in play, conceptually speaking. Because it is for a visual display i.e. continuous records in columns.

I think really you should approach it with comboboxes. Put them in the header of the form and set up several - by Name, by ID, etc. I think this will work fine.

Hope it helps.

[sparlaman]

I don't quite understand what you are saying about adding combo boxes. I also don't quite agree about sorting not really being in play with single form view. If you apply a sort to the underlying query it is applied in that the order in which you go from record to record within the form matches the sort order. That is what I am looking for. My only problem is I don't know how to program an object that would allow the user to choose that underlying sort order, if that is at all possible. I can't imagine it wouldn't be.

[NTC]

well one can apply a dynamic sort if one is into constructing a custom sql statement on the fly....kind of advanced.

I rarely see people needing to step thru records sequentially. But I often see them needing the selection sorted in different ways - - and so multiple combo/list boxes in the header that offer the user various sorted ways to select a record works quite well.

[sparlaman]

This is all I needed. And I figured it out all by myself.

So, I created an unbound combo box on my form with a value list of the field names that I wanted folks to be able to sort by. Then I included the code below in the after update event of that field.

(I'm sure I could have put more effort into this and created a dynamic field that would collect the field names, but that wasn't necessary for this application)

Private Sub txtSortBy_AfterUpdate()
Me.OrderBy = [txtSortBy]
End Sub

Thanks for all suggestions and help.