Sorting on the fly

[dbrooks]

I have a form that has a query subform inside of it. Most of the people who will be using this database won't know how to right click on the column headers and choose to sort accending or whatever. So my goal is to have a command button under the query that says something like "Sort by Column1". Is this possible to do with VBA?

Thanks -db

 

[Sharon Williams]

I'm not going to be any help to you, but I just wanted everyone to know that I have exactly the same question. I would like to put command buttons on my form to sort the subform by particular columns.

Thanks in advance.

Sharon:

 

[ChrisO]

This is only one way to do it.

On the Main Form with a SubForm called frmCustomersSubform try this: -

Private Function ResortSubFormBy(ByVal strFieldName As String)
    Dim strSQL As String
    
    strSQL = "SELECT DISTINCTROW ID, CompanyName, ContactName, ContactTitle, Address " & _
             "FROM Customers " & _
             "ORDER BY " & strFieldName
    
    Me.frmCustomersSubform.Form.RecordSource = strSQL
    
End Function

This Function can then be called from the Command Buttons OnClick like this: -

for the Company name
=ResortSubFormBy("CompanyName")
or for the Address
=ResortSubFormBy("Address")

Obversely you will need to change the names for your circumstances.

Hope that helps.

Regards
Chris

 

[Sharon Williams]

Thanks for your help. I have been trying this code, but with no luck yet. I get a message "cannot find input table or query" Have also got message "cannot find data member" It seems to be having the most trouble with: Me.frmCustomers Subform.Form.Record Source = strSQL

Information about my form and subform -

1. Form and subform has fields from three tables - all is working well.

2. I would like to include command buttons for the user to sort fields on the subform.

Questions -

1. In the code you included, Chris, is "Customers" the name of the Main Form?

2. Is this code written for command buttons on the Main form?

3. How would it work to put the command buttons on the bottom of the subform as "db" asks?

Have you tried Chris' code, db?

Any help much appreciated. If there are other ways to do this, let me know - I really need to do this soon. Thanks.

Sharon

 

[Rich]

MyField.SetFocus
DoCmd.RunCommand acCmdSortAscending or Desc as the case may be
but then it's much easier to use the control tip text and tell users to right click the relevant field

 

[ChrisO]

Sharon

As Rich has suggested there are many ways to do this.
Which way you go depends on many things such as security and aesthetics.

The attached demo may give you some ideas.

Here's a couple of ways to sort using both buttons on the Main Form as well as clicking the Headers on the Sub Form.
There's a lot more formatting in here because it is something I have been playing with for a while.

Have a play with it and see if it does what you want. If you choose either of these methods you would need to change quite a bit of the code for your circumstances. I doubt if I could help you without seeing the structure of your Form and Sub Form. So if you can could you please zip up a copy and post it as an attachment in version 97.

Regards
Chris

 

[Sharon Williams]

Still not working!

Chris -

Thanks for your kind help... sorry I was so long getting back to you.

I looked at your attached database and tried to apply it to mine, but can't get it to work. It is exactly what I want to do tho. Any help would be appreciated. I am attaching the file...


Sharon

 

[ChrisO]

Hi Sharon

First up you were quite close to the calling on the Function but were not passing the second argument when sorting ascending.

Also, you should use Option Explicit in your Modules.
I didn't include it in the attachment because it gives too many errors.
Also you will need to compile because there's a Me.Recordset.Clone reference that will be picked up.

Anyhow apart from that I copied the SubForm Query in to the Function and removed what I could.

Private Function ResortSubFormBy(ByVal strFieldName As String, _
                                 ByVal strDesc As String)
    Dim strSQL As String
    
    strSQL = "SELECT tblClassDetails.DrawOrder, " & _
                    "tblEntries.BackNumber, " & _
                    "tblShowDetails.ClassID, " & _
                    "tblEntries.HorseID AS Horse, " & _
                    "tblClassDetails.Rider, " & _
                    "tblShowDetails.ShowID, " & _
                    "tblClassDetails.Score1stGo, " & _
                    "tblClassDetails.Score2ndGo, " & _
                    "tblClassDetails.Place1stGo, " & _
                    "tblClassDetails.Place2ndGo, " & _
                    "tblClassDetails.DrawID, " & _
                    "tblShowDetails.Numberofgoes " & _
             "FROM tblEntries " & _
             "INNER JOIN (tblShowDetails " & _
             "INNER JOIN tblClassDetails " & _
             "ON (tblShowDetails.ClassID = tblClassDetails.ClassID) " & _
             "AND (tblShowDetails.ShowID = tblClassDetails.ShowID)) " & _
             "ON (tblEntries.ShowID = tblClassDetails.ShowID) " & _
             "AND (tblEntries.EntryID = tblClassDetails.EntryID) " & _
             "ORDER BY " & strFieldName & " " & strDesc

    Me.sfrmEnterScoresClasses.Form.RecordSource = strSQL
    
End Function

Hope that helps.

Regards
Chris

 

[Sharon Williams]

Thank you!

Chris -

Thank you so much for your help. It's exactly what I need and I should be able to go ahead from here.

Sharon

 

[ChrisO]

Hi Sharon

Had a bit of a play with the looks of the sorting and moved it all to the Sub Form.

I'm building something else at the moment and this is, I think, the way I'll go.

Thanks for the original question and if you like it let me know if you want any help with it.

Regards
Chris