Change Sort Criteria for a query that is the source for an unbound control

Question

Access 2013 Form with an unbound control that displays the results of a query based on selection criteria entered on the form - after a Requery.

The sort criteria are currently hard coded in the query. I would like to change the sort criteria based on entered sort options.

Is there a way this can be done via VBA?

Answer 1

What type of control?  As the control 'displays the results of a query' I would assume it is a list box, in which case you would need to build and assign an SQL statement to the control's RowSource property on the basis of values entered or selected in other controls.

---

Ken Sheridan, Stafford, England

Answer 2

Hi,

Yes, there is. Actually, there's more than one way. However, can you tell us exactly how you're binding an unbound control to a query? Is it a Listbox? Or, is it a subform? Or, maybe a combobox? The method to apply may differ depending on the type of control used. Also, it might be possible to change the SQL statement for the control rather than simply bind it to the query. When you assign a dynamic SQL statement, then you can easily modify the sorting order.

Just my 2 cents...

Answer 3

It is a Listbox.

The query has several selection criteria tied to Global Functions that are based on Global variables determined by other controls on the form.

I have never created a SQL statement.

Answer 4

It is a Listbox.

The query has several selection criteria tied to Global Functions that are based on Global variables determined by other controls on the form.

I have never created a SQL statement.

Hi,

A listbox would have a Row Source property. In you case, it might have a name of a query. But if you want to modify the sorting order of the listbox, you might be better off using a SQL statement to replace the name of the query in code. For example, let's say the query's name is MyQuery, and the SQL statement for it was something simple like:

SELECT * FROM MyTable

Then, after you had the user decide which field to use to sort the listbox, you might execute the following code:

Me.ListboxName.RowSource = "SELECT * FROM MyTable ORDER BY " & Me.ControlName

Hope it helps...

Answer 5

I have never created a SQL statement.

Hopefully theDBguy's example has pointed you in the right direction, but if you have any problems then, in query design view, open the query in which you earlier said you'd hard coded the sort order.  Then switch it to SQL view and copy the SQL statement and paste it into a reply here.  If you can then describe which controls in your form you want to use to change the sort order, we'll be in a better position to give you chapter and verse on the VBA code you'd need to write.

---

Ken Sheridan, Stafford, England

Answer 6

SELECT Clients.ID, Household.Last_Name AS [Last Name], Household.First_Name AS [First Name], Clients.Date_Last_Seen AS [Last Seen], Household.DOB, Clients.Inactive, Clients.Primary_Phone AS Phone, Clients.Address_1 AS Address
FROM Clients INNER JOIN Household ON Clients.ID = Household.HH_ID
WHERE (((Household.Last_Name) Like Client_Last_Name("x")) AND ((Household.First_Name) Like Client_First_Name("x")) AND ((Household.DOB) Like Client_DOB("x")) AND ((Clients.Address_1) Like Client_Address("x")) AND ((Household.Relationship_to_Client)="Self")) OR (((Household.Last_Name) Like Client_Last_Name("x")) AND ((Household.First_Name) Like Client_First_Name("x")) AND ((Clients.Address_1) Like Client_Address("x")) AND ((Household.Relationship_to_Client)="Self") AND ((Client_DOB("x"))=0))
ORDER BY Household.Last_Name, Household.First_Name;

Currently sorted by Last Name and First Name. Two tables joined on a one-to-many relationship. I would like to be able to change the sort to Address_1 or DOB based on a control on the main form.

Answer 7

Currently sorted by Last Name and First Name. Two tables joined on a one-to-many relationship. I would like to be able to change the sort to Address_1 or DOB based on a control on the main form.

Hi BVitter,

It is a little bit of string manipulation:

    Me!Listboxname.RowSource = Split(Me!Listboxname.RowSource," ORDER BY")(0) & " ORDER BY " & fieldname

 You can place this in the Click event of the control.

Imb.

Answer 8

You haven't said what type of control you are using to selected the sort order, so for this example I'm assuming it's an option group with values 1, 2 or 3 for the three options, in which case the code for it's AfterUpdate event procedure would be like this:

    Dim strSQL As String
    Dim intOrderByStart As Integer
        
    strSQL = NameOfListBox.RowSource
    
    With Me.ActiveControl
        Select Case .Value
            Case 1
            intOrderByStart = InStr(strSQL, "ORDER BY")
            strSQL = Left(strSQL, intOrderByStart) & _
                "ORDER BY LastName,FirstName;"
            NameOfListBox.RowSource = strSQL
            Case 2
            intOrderByStart = InStr(strSQL, "ORDER BY")
            strSQL = Left(strSQL, intOrderByStart) & _
                "ORDER BY Address_1;"
            NameOfListBox.RowSource = strSQL
            Case 3
            intOrderByStart = InStr(strSQL, "ORDER BY")
            strSQL = Left(strSQL, intOrderByStart) & _
            "ORDER BY DoB;"
             NameOfListBox.RowSource = strSQL
        End Select
    End With

However, I note that you say 'based on a control on the main form'.  That suggests to ne that the list box might be in a subform.  If so instead of referencing it as:

    Me.NameOfListBox

You would have to do so via the Form property of the subform control:

    Me.NameOfSubformControl.Form.NameOfListBox

NameOfSubformControl is the name of the control in the parent form which houses the subform.  This might or might not be the same as its source form object.

---

Ken Sheridan, Stafford, England

Answer 9

Thank you, this is exactly what I needed to make the search work