How do I sort the results of an unbound form? 1

[Dophia]

Hello Everyone:

I have an unbound form which the user populates with data that they want to find, and it opens another form with the results. For example, they would enter "Smith" and "Third" to find people named Smith with the address of "Third" Street.

How can I get the results to be sorted alphabetically? Here is an excerpt of the code I have, but there are many more fields that can be searched.

Private Sub OK_Click()
Me.Visible = False
Dim strWhere As String
strWhere = "1=1 "


If Not IsNull(Me.txtLastName) Then
strWhere = strWhere & " And ([Last_Name] Like '" & _
Me.txtLastName & "*' OR [Spouse_CommonLaw_Last_Name] Like '" & _
Me.txtLastName & "*') "

End If

If Not IsNull(Me.txtFirstName) Then
strWhere = strWhere & " And ([First_Name] Like '" & _
Me.txtFirstName & "*' OR [Spouse_CommonLaw_First_Name] Like '" & _
Me.txtFirstName & "*') "

End If

If Not IsNull(Me.txtStreetAddress) Then
strWhere = strWhere & " And [Street_Address]='" & _
Me.txtStreetAddress & "' "

End If
DoCmd.OpenForm "LkkpPeople", acNormal, , strWhere
DoCmd.Close acForm, "FrmFindPeopleEntryForm"

Dim strSQL As String
End Sub
Sophia

 

[dhookom]

I would place some controls on the form LkkpPeople or simply use the build in sorting from the ribbon.

Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

Thanks for your help Duane.
I am using Access 2003, so I don't have that ability from the Ribbon.

I did put "sort" on fields in the query of the LkkpPeople, but it didn't make a difference in the results. Or do you mean something different when you say controls on the LkkpPeople?

Sophia

 

[dhookom]

Forms have an OrderBy property that can change the sorting of the records. You can use code to manipulate the OrderBy property as needed. You should also be able to right-click in fields and perform sorting and filtering.

Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

The only problem with your suggestion is that I can't change the order by , depending on the search results chosen. For example, if the order by is Last Name, then if I wanted to search by street and house number, in order to see the house number and street names sorted, I am still getting it sorted first by last name, which was not a chosen field to searched. I want the user to see whatever field they chose, to be sorted.

Any other suggestions would be appreciated.
Thanks, Sophia

 

[dhookom]

What is the SQL of the record source of the form?

Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

Here is the SQL
SELECT tblPeople.Last_Name, tblPeople.People_ID, tblPeople.First_Name, tblPeople.Spouse_CommonLaw_Last_Name, tblPeople.Spouse_CommonLaw_First_Name, tblPeople.House_No, tblPeople.Street_Address, tblPeople.Street_Suffix, tblPeople.Apartment_No, tblPeople.PO_Box, tblPeople.City, tblPeople.Province, tblPeople.Postal_Code, tblPeople.Home_Phone_No, tblPeople.Work_Phone_No, tblPeople.Cell_Phone_No, tblPeople.Pple_Flag, tblPeople.Pple_Flag_Reason
FROM tblPeople
ORDER BY tblPeople.Last_Name, tblPeople.First_Name, tblPeople.House_No, tblPeople.Street_Address, tblPeople.[N/S/E/W], tblPeople.City;

I think I see where you are going with this. I guess the form results called from the unbound form should be a separate sql and LkkpPeople form, depending on the fields chosen by the user?

Sophia

 

[dhookom]

You can use the OpenArgs to pass the fields to the new form and use code there to set the OrderBy property. You can also use DAO code to change the SQL of the saved query used as the form's record source.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

How can I get the results to be sorted alphabetically?

On which field order?
If the user did select "last", "First", "address" what order would you want it sorted?
last, first, address
first, last, address
address, first, last
address, last, first

Normally, with something like this my found results are displayed continuous form tabular view. I then make every label at the top of the form with a sortable label. Like your Outlook inbox. Click on a label and it sorts the list on that field. This takes little code and gives lots of functionality for searching. Then in the results you can click on address and sort by address or click on last name and sort by last name. Helps to search for things.

If not you will need a way on the main form to tell which order to sort the fields. If they pick 5 fields they probably want to sort on those fields, but that does not answer how to sort on those five fields. You would have to make some kind of control. I would do this with a list box and up and down arrows to move my choices up and down.

So your code would then would be

dim strOrder by string
....
'some code here to check the selected fields and build a strOrder string
'of the form field name seperated by a semicolon  'Last_Name';'First_Name';'Other_field'
DoCmd.OpenForm "LkkpPeople", acNormal, , strWhere
with forms("lkkpPeople")
  .orderbyon = false
  .orderby = strOrder
  .orderbyOn = true
end with

one way you could build strOrder would be to tag each of your searchable fields with a character like "?"
then

dim ctrl as access.control
for each ctrl in me.controls
  if ctrl.tag = "?"
   if strOrder = "" then
    strOrder = "[" & ctrl.controlsource & "]"
   else
     strOrder = strOrder & ";" & "[" & ctrl.controlsource & "]"
   end if
  end if
next ctrl

This would at least make the fields you selected the top fields, but in no specific order.

 

[Dophia]

It sounds complicated. I tried to set an Orderby in the original code I posted, but it didn't work. Do you think that can be done? Maybe it was just my code that was wrong?
Sophia

 

[Dophia]

Thanks for your help too MajP. My "sounds complicated" was referring to Duane's last post. I am still digesting what you wrote and will reply shortly.
But to answer your question, I want to sort alphabetically. There are the following chooses on the unbound form...Last Name, First Name, Street Name, City, Phone Number. But if someone chooses the street name "Third", I would want them to see it sorted by that, but including the house number.

Sophia.

 

[Dophia]

I think it would be best if I could do what you wrote here

"Normally, with something like this my found results are displayed continuous form tabular view. I then make every label at the top of the form with a sortable label. Like your Outlook inbox. Click on a label and it sorts the list on that field. This takes little code and gives lots of functionality for searching. Then in the results you can click on address and sort by address or click on last name and sort by last name. Helps to search for things."

What kind of code?

For the other options, I am not able to fully understand what you wrote.

Thanks Sophia

 

[MajP]

I do not know how many more fields you have, but maybe something like this.

dim strOrder as string
'If address is selected than sort on that then last, first
if Not IsNull(Me.txtStreetAddress) then
 strOrder = "Street_Address" & ";" & "Last_Name" & ";" & " First_Name"
else
 strOrder = "Last_Name" & ";" & " First_Name"
end if
'maybe some other rules based on what is selected.

Again, I prefer to build a solution that can be sorted in any way possible. Does not take a lot of code to do that.

 

[Dophia]

Where exactly would you put this code? I tried a command button on the form, but didn't do anything.
Sophia

 

[dhookom]

I just landed (been on flights past few hours). I always place code in the form that I want it to change. It doesn't make much sense to me to add code to one form that changes properties in another form.

Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

I am confused. Do you mean, why do I have an unbound form to display a form with the results, Duane?
Sophia

 

[dhookom]

You asked where the code that MajP suggested should go. I was stating it should be in the form that was opened.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

To make a sortable tabular form.

Private Sub OrderID_Label_Click()
  SortForm "OrderID, CustomerID"
End Sub

Private Sub ShipCity_Label_Click()
  SortForm "shipcity"
End Sub
Public Sub SortForm(strSort As String)
  Me.OrderBy = ""
  Me.OrderByOn = False
  Me.OrderBy = strSort
  Me.OrderByOn = True
End Sub

On your tabular form you can add the above routine called SortForm. Then in the click event for each heading label, pass the sort order that you would like when they click that label.
So if I click the ShipCity label I pass it to sort by ShipCity
If I click the Order ID label then I want it to sort first by order ID then by customer ID.
Some times it makes sense to pass in multiple orders. If I click the last name label then I would probably pass
sortForm "lastName, FirstName, MI"
If I hit the first name label I would pass
sortForm "firstName, LastName, MI"

This is nothing fancy. More advance would be to have a ascending descending capability if you hit the label twice in a row, and add an up down image to show that.

 

[MajP]

Here is an example of sorting a tabular form by clicking on the header label. In this case you click it a second time and it switches from ascending to descending

Private Sub lblProductID_Click()
  Static ASC As Boolean
    If ASC Then
      SortForm "ProductID Desc"
    Else
      SortForm "ProductID"
    End If
  ASC = Not ASC
End Sub

Private Sub lblProductName_Click()
  Static ASC As Boolean
    If ASC Then
      SortForm "ProductName Desc"
    Else
      SortForm "ProductName"
    End If
  ASC = Not ASC
End Sub

Private Sub lblQuantity_Click()
    Static ASC As Boolean
    If ASC Then
      SortForm "QuantityPerUnit Desc, ProductName"
    Else
      SortForm "QuantityPerUnit, ProductName"
    End If
    ASC = Not ASC
End Sub

Private Sub lblUnitPrice_Click()
    Static ASC As Boolean
    If ASC Then
      SortForm "UnitPrice Desc, ProductName"
    Else
      SortForm "UnitPrice, ProductName"
    End If
    ASC = Not ASC
End Sub

Public Sub SortForm(strSort As String)
  Me.OrderBy = ""
  Me.OrderByOn = False
  Me.OrderBy = strSort
  Me.OrderByOn = True
End Sub

As you can see the code if very simple just do a cut and paste for each label and change the sort string. Now the only thing left is to add an up and down error like you would have in outlook. Or some way to show which field and direction it is sorted.

 

[Dophia]

Thank you MajP. I appreciate the time you took to help me. I love how this works!

Sophia