Help with Sorting

[Big Pat]

I've got two buttons in the header of my form, to allow the user to toggle the sort order via various fields. This first one below uses a single field and works fine. The second one however, uses two fields and does not work

Private Sub cmdSortEmployer_Click()
Me.OrderByOn = True

If Me.OrderBy = "[Substantive Employer] DESC" Then
    Me.OrderBy = "[Substantive Employer] ASC"
Else
    Me.OrderBy = "[Substantive Employer] DESC"
End If

Me.Requery

End Sub

Private Sub cmdSortName_Click()
Me.OrderByOn = True

If Me.OrderBy = "[Surname],[First Name] DESC" Then
    Me.OrderBy = "[Surname],[First Name] ASC"
Else
    Me.OrderBy = "[Surname],[First Name] DESC"
End If

Me.Requery

End Sub

The second code DOES sort the records by Surname then First Name ascending, but when I click it the second time it does nothing. Or presumably just sorts it ascending again. It won't sort it descending.

Have I got something wrong with the brackets, comma or quotes in this case? I realise I probably shouldn't have a space in the [First Name] field, but this is data that I'm linking to form a SharePoint list so i can't change this.

Thanks,
Pat

 

[pr2-eugin]

I am not sure, but should Me.OrderByOn be after setting the Sort Order?

 

[Big Pat]

I wondered that too so I moved it but it's had no effect. And I moved it in the first block of code too but that STILL works, so it doesn't seem to matter.

 

[shutzy]

this is soem code that i use in a combo box to sort my date. im sorting a date field so it may be a little easier.

Private Sub comboSortOrder_AfterUpdate()
If Me.comboSortOrder = "Ascending" Then
Me.OrderBy = "DiaryDate ASC"
Else
Me.OrderBy = "DiaryDate DESC"
End If
End Sub

i have this on a continuous form.

hope this helps

 

[Big Pat]

Mine's on a continuous form too, though not in a combo. It's in the On Click event of a transparent button which I have placed over the Surname header. That's exactly what I've done with the Employer header and that works fine.

In this one however, I'm attempting to sort by Surname and by First Name and I didn't think that would be a problem.


Surname button ___________________Employer button
sorts ASC only_____________________works just fine

<Surname>_______First Name_______<Employer>
-----------------------------------------------
Adams____________John___________Big Firm
Brown____________Jane___________Small company
Crane____________Tom____________Another firm
etc
etc

 

[pr2-eugin]

Would it be possible for you to upload a Stripped DB version?

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[Big Pat]

Thanks Paul,

I'll give that a go this afternoon. I'll need to import the data from sharepoint into a table in the database, and I'm just off to a meeting soon so it may be a couple of hours before i get back to you.

Pat

 

[Big Pat]

Hi,

Stripped down copy attached. On the subform of the opening screen, click and re-click the Substantive Employer button and you'll see that it works fine. Now click and re-click the Surname button and it works only the first time.

Apologies for the frankly ludicrous field names!! I didn't create the sharepoint list and I'm just trying to give people a more user-friendly way of interacting with it.

Thanks

 

[pr2-eugin]

Well, that's something I did not know.. If you are sorting multiple fields then you need to put the Sort order on each field.. Well a lesson learnt..

Try this..

Private Sub cmdSortName_Click()
    If Me.OrderBy = "[Surname] DESC, [First Name] DESC" Then
        Me.OrderBy = "[Surname], [First Name]"
    Else
        Me.OrderBy = "[Surname] DESC, [First Name] DESC"
    End If
    Me.OrderByOn = True
    Me.Requery
End Sub

 

[Big Pat]

Ahhhh... Nicely done! Yes, that works perfectly now. And I see you don't even have to specify ASC for ascending as it takes that as the default.

Thank you so much, that helps out a lot!

Pat

 

[pr2-eugin]

Glad to help, thanks to you I have something to take away with me today.