Form - re-sort button

[Lanason]

whats the syntax to just resort the current records that are displayed on the form ???

ps the form can be filtered by a number of different queries and I need to automate the sorting without the pain of a load more queires

 

[WayneRyan]

Adrian,

Choose a field, Records --> Sort Ascending

Wayne

 

[Lanason]

Yep I know about that but I want to sort on more than one field and then by just clicking one automated button on the screen.

can I put the sort into the code for the button ?? If so what is the syntax for sorting ie.
something like sort [field1],[field2]

cheers

Adrian

 

[WayneRyan]

Adrian,

If you populate your form with dynamic SQL, then all you have to do is build the SQL statement.
You could have a few combo boxes to let the user select the fields.

Me.RecordSource = "Select * " & _
                  "From YourTable " & _
                  "Order By " & Me.Combo1 & ", " & Me.Combo2 & ", " & Me.Combo3
Me.Requery

Wayne

 

[Lanason]

thanks too comlicated just need the simple command for the sort


I want to give say 3 or 4 sort options with 3 or 4 buttons ......

 

[IMO]

Private Sub YourButton_Click()

If Me.OrderBy = "Column1" Then
    Me.OrderBy = "Column2"
    Me.YourButton.Caption = "Ordered by Column2"
    Me.YourButton.ForeColor = vbRed
    Me.OrderByOn = True
Else
        Me.OrderBy = "Column1"
        Me.YourButton.Caption = "Ordered by Column1"
        Me.YourButton.ForeColor = vbBlue
        Me.OrderByOn = True
End If

End Sub

IMO

 

[GregP]

This thread sounds very much like what I'm wanting to do, but I couldn't get my adapted version of the supplied code to do anything. I have a tabular form with column headings in the form header, and form information in the detail. I want to have a button next to a label in the form header to allow the user to sort by that field (exactly the same as you have in Windows Explorer for sorting views by filename etc). I can achieve exactly what I want by enabling and unlocking the field in question, clicking on it and then clicking the A-Z button, but I need the fields disabled and locked, and want a 'pretty' way of sorting (by default the form is sorted by another field, but I want to provide the option to sort by the field called User_MachineName).

My code currently is:

Private Sub Sort_Click()

Me.OrderBy = Me.User_MachineName

End Sub


but this didn't seem to do anything. What am I doing wrong, and how do I go about correcting it? Thanks!

 

[IMO]

Private Sub Sort_Click()

     If Me.OrderBy = "User_MachineName" Then
         Me.OrderBy = "AnotherColumnName"
         Me.Sort.Caption = "Ordered by AnotherColumnName"
         Me.Sort.ForeColor = vbRed
         Me.OrderByOn = True
     Else
         Me.OrderBy = "User_MachineName"
         Me.Sort.Caption = "Ordered by User_MachineName"
         Me.Sort.ForeColor = vbBlue
         Me.OrderByOn = True
     End If

End Sub

Change "AnotherColumnName" to the Name of a second column to Order By.

HTH
IMO

 

[Lanason]

and the answer is

Heres my code as helped with the guys on here - thanks

Private Sub Command46_Click()
On Error GoTo Err_Command46_Click

Me.OrderBy = "Posted"
Me.Command46.Caption = "Posted"
Me.Command46.ForeColor = vbBlue
Me.OrderByOn = True

Exit_Command46_Click:
Exit Sub

Err_Command46_Click:
MsgBox Err.Description
Resume Exit_Command46_Click

End Sub

 

[GregP]

Thankyou gents, worked a charm! All I need to know now is how to indicate *how* to sort, i.e. A-Z, Z-A. Sorry for the simple questions!

 

[Edski]

Me.OrderBy = "AnotherColumnNameb ASC"
Me.OrderBy = "AnotherColumnNameb DESC"

You could have a toggle button or change the caption each time the button is pressed.

 

[GregP]

Super, thanks!

 

[Lanason]

DATES

My code works fine for text columns but not for DATES - doesnt sort em ???

any ideas ??

 

[IMO]

It works fine for me. Post a stripped down version of your db and I'll take a look.

IMO

 

[Edski]

Yes, I have no trouble with sorting dates either. It must be something specific in your code or database design.