Sort Button 2

[cstuart79]

I have added a button to my main form "Clients" that should sort data on a subform "Contacts" according to field "Group Type" A-Z. Can anyone help me out with the code that should go in "On Click" event for button that will move focus to subform field and sort that field alphabetically?

 

[dhookom]

I generally set the Record Source of the subform to a SQL statement like:

  Dim strSQL as String
  strSQL = "SELECT .... FROM .... ORDER BY [Group Type];"
  Me.sfrmContacts.Form.RecordSource = strSQL

Duane
Hook'D on Access
MS Access MVP

 

[cstuart79]

i'm going to have various buttons that will sort data by various fields in the subform. shouldn't the vb code go on "on click" event for each button? would the sql statement work if populated in "on click" event for each button?

 

[dhookom]

For sorting, I use an option group. Put as many option buttons as needed and then use the After Update event of the group to call the code to change the Record Source.

Duane
Hook'D on Access
MS Access MVP

 

[cstuart79]

can you give an example of code and would "after update" event be on subform that needs focus?

 

[MajP]

If it is in continous view, I make all my subforms and forms look similar to Outlook where you can click any lable and sort on that field

in a standard module I put this which will allow me to sort any form in the database based on a string. The string is a list of field/s on which to sort.

Public Sub OrderForm(strOrder As String, frm As Access.Form)
  frm.OrderByOn = False
  frm.OrderBy = strOrder
  frm.OrderByOn = True
End Sub

Then to call the procedure

Private Sub lblProgram_Click()
  Call OrderForm("ProgramID, UserName, StartDate", Me)
End Sub

Private Sub lblStartDate_Click()
  Call OrderForm("StartDate, ProgramID, UserName, EndDate", Me)
End Sub

Private Sub lblTitle_Click()
  Call OrderForm("Title, ProgramID, UserName, StartDate", Me)
End Sub

So to call this sort from a button on the main form for a subform.

Private Sub cmdSort_Click()
Call OrderForm("yourSortfieldName, OptionalField2Name, optionalotherfields", Me.yoursubformcontrolname.form)
End Sub

Private Sub cmdSort_Click()
Call OrderForm("[Group Type]", Me.Clients.form)
End Sub

 

[dhookom]

I would use something like:

Dim strSQL as String
Dim str OrderBy as String  
strSQL = "SELECT .... FROM .... "
Select Case Me.grpSortOrder
    Case 1 'group type
        strOrderBy = " ORDER BY [Group Type]"
    Case 2 'State
        strOrderBy = " Order By [State]"
    Case 3 'LastName
        strOrderBy = " Order By [LastName], [FirstName]"
    Case Else
End Select
Me.sfrmContacts.Form.RecordSource = strSQL & strOrderBy

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Duane,
You seem to prefer to build a new recordsource instead of using the form's orderby property. Is that just personal preference, or is there some advantage/efficiency in doing it that way?

 

[dhookom]

For some reason, I just don't use either the Order By or the Filter properties of forms or reports. Typically, if I want to filter a continuous form, I will have both sorting and filtering controls in the form header. I then create sub that checks the filtering and sorting controls to dynamically build a record source SQL statement.

Duane
Hook'D on Access
MS Access MVP

 

[cstuart79]

where should this code go? event? main form? subform? button?

CODE
Public Sub OrderForm(strOrder As String, frm As Access.Form)
frm.OrderByOn = False
frm.OrderBy = strOrder
frm.OrderByOn = True
End Sub

where should this code go? and i don't understand what should populate "startdate, program ID, username, enddate"?

CODE
Private Sub lblProgram_Click()
Call OrderForm("ProgramID, UserName, StartDate", Me)
End Sub

Private Sub lblStartDate_Click()
Call OrderForm("StartDate, ProgramID, UserName, EndDate", Me)
End Sub

Private Sub lblTitle_Click()
Call OrderForm("Title, ProgramID, UserName, StartDate", Me)
End Sub

i added this code to "on click event" of button on main form:

Private Sub Command248_Click()
Call OrderForm("Group_Type", Me.CONTACTS.Form)
Call OrderForm("Group_Type", Me.Clients.Form)
End Sub

 

[MajP]

If you use Dhookum's code it goes on your Main Form.

If you use my code then this goes in a standard module:

Public Sub OrderForm(strOrder As String, frm As Access.Form)
frm.OrderByOn = False
frm.OrderBy = strOrder
frm.OrderByOn = True
End Sub

If your main form is called "Contacts" and your sub form control is called "Clients" and your button is on the main form. The code goes on the main form.

Private Sub Command248_Click()
'Sort the main form by GroupType
Call OrderForm("Group_Type", Me)
'Sort the subform by group type.
Call OrderForm("Group_Type", Me.Clients.Form)
End Sub

Although your subform may be called "Clients", the sub form control may be called something like "child1". You need to check the name and use the name of the subform control. Make sure the name of the subform control does not have spaces like "Child 1".

 

[cstuart79]

it is working, but i keep getting "add parameter value" pop up. something is just slightly off.

 

[cstuart79]

I keep getting this pop-up:

Enter Parameter Value
Group_Type

Also, do I need to enter code in any other event box to keep the data sorted (by group type for example) until a different sort button (by last name for example) is clicked?

 

[MajP]

Check your spelling. Normally this is means you have a field like "Grp_Type" and you tell it to sort by "Group_Type". Should require no additional code.

 

[cstuart79]

spelling is all accurate--it seems as though the error has to do with entry in "Order By" for Property Sheet. When I change this from "Group_Type" to something like "strOrder"
,then the error says "strOrder" parameter value instead of "Group_Type" parameter value. what should be populated in "order by" property sheet?

 

[PHV]

What is YOUR actual code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Isn't this the same question that was resolved in thread702-1578417?

Duane
Hook'D on Access
MS Access MVP

 

[cstuart79]

yes duane, it is the same as addressed in another post. i resolved the issue of getting the parameter error every time i selected a different client or clicked the sort button, but i still get a single error message upon opening up the database even though the sort button works perfectly and no other errors are generated any more.

Public Sub OrderForm(strOrder As String, frm As Access.Form)
frm.OrderByOn = False
frm.OrderBy = strOrder
frm.OrderByOn = True
End Sub
Private Sub Command248_Click()
Call OrderForm("Group_Type", Me.CONTACTS.Form)
End Sub

 

[dhookom]

error message upon opening up the database

Opening a database shouldn't give you an error. If this is actually from opening a form, then you should be more accurate with your descriptions.

What is the exact error and when does it occur?

I expect you have a property value saved in some form that doesn't make sense. review them and fix them.


Duane
Hook'D on Access
MS Access MVP

 

[cstuart79]

sorry duane--you are correct. anyways, i took your advice and after going through all property values in all forms, i figured out that it was simply OrderBy on main form was set to Order_Type when it should have been blank. That has seemed to resolve the message.