sort when click on column title

[jennyha23]

Hello friends:
I created a form and I want to do SORTING every time I click on Column title.
Thank you very much for reading.
Jenny.

 

[The_Doc_Man]

This is not an automatic property of a form. Only a query/table datasheet.

To do this in a columnar form, you would have to put an OnClick event to reset the overall form's properties to include the new ORDER BY clause for that column, replacing any prior ORDER BY.

Then I think you would need to REQUERY the form to have the new ORDER BY take effect.

 

[jennyha23]

How do I do it?

Can you please show me in detailed how to do it?
Thanks.

 

[ray705]

Set the Click event of the column heading:

Private Sub lblEmpl_Click()
   OrderBy = "Employees.EmplNr"
Exit Sub
End Sub

Private Sub lblJob_Click()
   OrderBy = "Employees.JobCode"
End Sub

 

[Peter D]

Hello friends:
I created a form and I want to do SORTING every time I click on Column title.
Thank you very much for reading.
Jenny.

Here's an example that may help with listbox sorting: http://www.peterssoftware.com/sl.htm

Hope this helps,

 

[jennyha23]

It doesn't work

Hello ray:
This is what i am looking for to do but it doesn't work.
I have a form, then I added one text field and button for Column title, then I used your code, but when I view the form, and clicked on either the text column title or button column title, nothing happened.
Can you please help me?
Thank.
Jenny.

 

[ray705]

I assumed that you were using a continuous form and wanted to sort on the column heading for the fields. It ought to work for a single form but I haven’t tried that.
Take a look at the attached db for an example.

 

[jennyha23]

This is exactly what I am looking for.
I created a form based on a few tables (query), I tried the same way as you said, but it doesn't work.
don't know why.
Jenny.

 

[Ukraine82]

Post your database. Tell me what field(s) you want to be in ascanding order.

Michael

 

[ray705]

I recall having a problem one time getting it to work, never figured out why.
Are you able to post a sample of your db for me to play with?

 

[Ukraine82]

Jenny,

Make a blank command button. Paste the code On Click.


YourFieldname.SetFocus
DoCmd.RunCommand acCmdSortAscending

hth,

Michael

 

[jennyha23]

Hello Ray,
Attached is my db. Please click on Employee2 form and look at LName, I entered your code, but it doesn' happy of anything.
Thanks.

 

[Ukraine82]

Made few adjustment. It should work now.

Michael

 

[jennyha23]

Thanks guys.
It worked.
However, it modified the query somehow. I don't know why?
Jenny.

 

[ray705]

I don't see any changes to the query. All I notice is the change to the form's Order By parameter.
What do you see changed in the query?

 

[jennyha23]

Ray, you are right. It worked with that db.
But when I applied the code to my real db, with a few tables and queries to inner join are put in the Record Source, something changed. I really don't know whether it is query or parameter changes.
Can you look into the code below? (the last few lines), it showed error right at Employer.SetFocus

Option Compare Database

Private Sub Command9_Click()
Call Employee_Modules.open_window(Me, "Emp_Window", "[Emp ID]", Me![Dept ID])
End Sub

Private Sub Contract_Number_Enter()
Me.Command9.SetFocus
End Sub

Private Sub Delivery_Order_Number_Enter()
Me.Command9.SetFocus
End Sub
Private Sub Command10_Click()
On Error GoTo Err_Command10_Click


DoCmd.close

Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click

End Sub

Private Sub Form_Open(Cancel As Integer)

Me.Command18.Visible = False
Me.Command17.Visible = True

On Error GoTo Err_form_open
Me.Text11.value = "ACTIVE EMP for " & [Forms]![Employer List]![Employer]
Me.Text19.value = "EMP for " & [Forms]![Employer List]![Employer]
Me.FilterOn = True
Exit_Form_Open:
Exit Sub

Err_form_open:
Me.Text11.value = "ACTIVE EMP"
Me.Text19.value = "EMPLOYEES"
Me.FilterOn = True
Resume Exit_Form_Open


End Sub
Private Sub Command14_Click()
Call FACTS_Modules.open_window(Me, "Emp_Window", "", "")
DoCmd.GoToRecord , , acNewRec
End Sub
Private Sub Command18_Click()


Dim temp As String
temp = Me.Filter
Me.Filter = Nz(Me.Text16, "")
Me.Text16 = temp
Me.FilterOn = True
Me.Command10.SetFocus

Me.Command18.Visible = False
Me.Command17.Visible = True

Me.Text11 = "ACTIVE " & Me.Text19.value


End Sub
Private Sub Command17_Click()
Dim temp As String
temp = Me.Filter
Me.Filter = Nz(Me.Text16, "")
Me.Text16 = temp
Me.FilterOn = True
Me.Command10.SetFocus

Me.Command17.Visible = False
Me.Command18.Visible = True

Me.Text11 = "ALL " & Me.Text19.value

End Sub

Private Sub Label29_Click()
[Emp Number].SetFocus
DoCmd.RunCommand acCmdSortAscending

End Sub

Private Sub lblEmployer_Click()
Employer.SetFocus
DoCmd.RunCommand acCmdSortAscending
End Sub

 

[ray705]

Looks OK.
Perhaps simply qualifying it with Me.Employer.SetFocus would fix it. I always make a habit of fully qualifying field names.