Sorting Continuous form

[sstiebinger]

I have a method I've been using to sort my forms, and it works well... but I'm having an error with one field so I'm not sure what I'm doing wrong...



The sorting code I'm using is:

Private Sub Toggle_Sort(sColumn As String)
    Me.Controls(sColumn).SetFocus
    If TempVars!varAcctSort = "Ascending" Then
        DoCmd.RunCommand acCmdSortAscending
        TempVars!varAcctSort = "Descending"
    Else
        DoCmd.RunCommand acCmdSortDescending
        TempVars!varAcctSort = "Ascending"
    End If
End Sub

Then I call it with the click event of the label for the columns like this:

Private Sub lblFName_Click()
    Toggle_Sort ("txtFName")
End Sub
Private Sub lblLName_Click()
    Toggle_Sort ("txtLName")
End Sub

I have a form with this code and all fields work EXCEPT for a text field containing email addresses... Whenever I attempt to click the email address label, I get a runtime error:

Run-time error '2046':
The command or action 'SortAscending' isn't available now.

It's only on that field... the only difference I can see between that field and any other is that its "Is Hyperlink" is set to yes, and that it contains email addresses....

Any ideas of what might be the issue?

[orange]

I found this in a microsoft forum

Hi,
you can create new calculated field in your query like
Left([Hyperlinkfield], 255) and sort by it

Hope it helps.

[sstiebinger]

Hmm... the table is storing the email as plain text; it's simply displayed as a hyperlink on the form.
I don't see how I could then sort this way.

Unless I have the exact same text box, not displayed as hyperlink, but bound to the same data and just make it visible = false and sort on that?
Seems kind of hacky... but any other ideas?

[orange]

How about posting a "dumbed down" copy of your database?
No private info, nothing confidential-- just enough data to show the issue.

[sstiebinger]

File size is over 500K (804K) so I can't attach directly...

https://dl.dropboxusercontent.com/u/...NewCRMUI.accdb

You can use:
Username: test
Password: test
on the login form.

The specific form I'm having the issue with is "sfrmContactsList"

Thanks.

[orange]

I'm not sure what I'm supposed to look at.
I see no relationships.
I do see some non normalized tables.

I added some sample data in your searchFName and searchLName.
I also added some debugging statements, and printed the filter

Comments in your code

Private Sub cmdFilterSelection_Click()
    Dim strWhere As String
    Dim lngLen As Long
    Const conjetdate = "\#mm\/dd\/yyyy\#"
    Debug.Print "Doing cmdFilterSelection "
    If Not IsNull(Me.txtSearchFName) Then
        strWhere = strWhere & "([ContactFName] LIKE ""*" & Me.txtSearchFName & "*"") AND "
        Debug.Print "FName  was not null" '************************************************************
    End If
    If Not IsNull(Me.txtSearchLName) Then
    Debug.Print "LName  was not null" '****************************************************************
        strWhere = strWhere & "([ContactLName] LIKE ""*" & Me.txtSearchLName & "*"") AND "
    End If
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
        MsgBox "No Criteria", vbInformation, "Nothing to filter."
    Else
        strWhere = Left$(strWhere, lngLen)
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
    Debug.Print "Filter is " & Me.FilterOn & "   and where clause is " & strWhere '*****************************
End Sub

Debugging Output:
Doing cmdFilterSelection
FName was not null
LName was not null
Filter is True and where clause is ([ContactFName] LIKE "*h*") AND ([ContactLName] LIKE "*fi*")

[CJ_London]

what happens if you right click on a field (not label) in the continuous form and sort from there? You will find it is greyed out, meaning you can't sort hyperlinks. Suggest you convert the hyperlink control to a textbox then format it to look like a hyperlink. If you need the user to be able to click on it to go somewhere, look at using the followhyperlink method - it is only one line of code.

[sstiebinger]

Thanks for the help... I'll just use a regular text box with formatting.