Sorting records in form after text search command

[pelicanstuff]

I found some code for text-searching in forms (using an unbound text box and a command button), and adapted it to look like this:

Private Sub Command83_Click()
Dim strPersonRef As String
    Dim strSearch As String
    
'Check txtSearch for Null value or Nill Entry first.

    If IsNull(Me![txtsearch]) Or (Me![txtsearch]) = "" Or (Me![txtsearch]) = "Surname" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
        Me![txtsearch].SetFocus
    Exit Sub
End If
        
'Performs the search using value entered into txtSearch
'and evaluates this against values in Surname
        
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("Surname")
    DoCmd.FindRecord Me!txtsearch
        
    Surname.SetFocus
    strPersonRef = Surname.Text
    txtsearch.SetFocus
    strSearch = txtsearch.Text
        
'If matching record found sets focus in Surname and shows msgbox
'and clears search control

    If Surname = strSearch Then
        MsgBox "Matches Found For: " & strSearch, , "Congratulations!"
        
        Surname.SetFocus
        txtsearch = ""
        
    'If value not found sets focus back to txtSearch and shows msgbox
        Else
           MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
            , "Invalid Search Criterion!"
            txtsearch.SetFocus
    End If

End Sub

The problem I now have is that the results are now sorted by the primary key of the table this form applies to, instead of alphabetically by surname, which I would like. This is also how I've set it to sort when the form opens. How can I make this bit of code re-sort the results?

[HiTechCoach]

Your code does not appear to do anything with the sort order. The form by default will sort by the primary key unless you change it.

You can just change the sort order in the form's design or add code to do it.

Example:

Me.OrderBy = "FieldName"
Me.OrderByOn = True

[pelicanstuff]

Your code does not appear to do anything with the sort order. The form by default will sort by the primary key unless you change it.

You can just change the sort order in the form's design or add code to do it.

Example:

Me.OrderBy = "FieldName"
Me.OrderByOn = True

That's the odd thing, I did already set Order By in the form design - running this code seems to somehow 'un-set' it.

I managed to re-sort it by putting those in after DoCmd.Showallre cords, and it seems to be working, cross fingers. I'm not so familiar with this stuff, and didn't know what the code for 'order by' was, so many thanks for the help.

[Jim Doherty]

I found some code for text-searching in forms (using an unbound text box and a command button), and adapted it to look like this:

Private Sub Command83_Click()
Dim strPersonRef As String
Dim strSearch As String
 
'Check txtSearch for Null value or Nill Entry first.
 
If IsNull(Me![txtsearch]) Or (Me![txtsearch]) = "" Or (Me![txtsearch]) = "Surname" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![txtsearch].SetFocus
Exit Sub
End If
 
'Performs the search using value entered into txtSearch
'and evaluates this against values in Surname
 
DoCmd.ShowAllRecords
DoCmd.GoToControl ("Surname")
DoCmd.FindRecord Me!txtsearch
 
Surname.SetFocus
strPersonRef = Surname.Text
txtsearch.SetFocus
strSearch = txtsearch.Text
 
'If matching record found sets focus in Surname and shows msgbox
'and clears search control
 
If Surname = strSearch Then
MsgBox "Matches Found For: " & strSearch, , "Congratulations!"
 
Surname.SetFocus
txtsearch = ""
 
'If value not found sets focus back to txtSearch and shows msgbox
Else
MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
, "Invalid Search Criterion!"
txtsearch.SetFocus
End If
 
End Sub

The problem I now have is that the results are now sorted by the primary key of the table this form applies to, instead of alphabetically by surname, which I would like. This is also how I've set it to sort when the form opens. How can I make this bit of code re-sort the results?

Just an observation :) Are you sure and happy that you are getting the full functionality you require overall? you have a bunch of code there that is just dealing with one specific element and that is surname. What about other fields ie Firstname date of birth etc etc. If you do not need that then ok... I merely illustrate a point... but consider this ...if you did need that you would have to repeat all of that all over again for each field.

In answer to your question....wha t is your form that provides the results basing itself on? a table? if so swap that to reference a query sorted by surname Also remember that the FindRecord method you use there finds the first match!! What do you if there is fifteen or sixteen 'Smiths' stacked and sorted together in single form view you wouldnt know 'how many' satisfy the search mechanism doing it that way.

If the form is is continuous view then ok you would see a stacked result sorted by surname but not the necessarily the number of? if you understand me.

A simple way of determining a result from a potential search like that would be to use a DCount function in an unbound textbox that references the unbound surname search field as its criteria and counts the relevant table for you based on the value you provided. That would be a visual thing straight away and could be used to dictate what happens next... so to speak.

Regards

Jim

[pelicanstuff]

Just an observation :) Are you sure and happy that you are getting the full functionality you require overall? you have a bunch of code there that is just dealing with one specific element and that is surname. What about other fields ie Firstname date of birth etc etc. If you do not need that then ok... I merely illustrate a point... but consider this ...if you did need that you would have to repeat all of that all over again for each field.

In answer to your question if I am interpreting correctly where you are at with this....what is your form that provides the results basing itself on? a table? if so swap that to reference a query sorted by surname Also remember that the FindRecord method you use there finds the first match!! What do you if there isfifteen or sixteen 'Smiths' stacked and sorted together in single form view you wouldnt know 'how many' satisfy the search mechanism doing it that way.

If the form is is continuous view then ok you would see a stacked result sorted by surname but not the necessarily the number of? if you understand me.

A simple way of determining a result from a potential search like that would be to use a DCount function in an unbound textbox that references the unbound surname search field as its criteria and counts the relevant table for you based on the value you provided. That would be a visual thing straight away and could be used to dictate what happens next... so to speak.

Regards

Jim :)

You're certainly right in that it could be far more functional, but this'll probably do for now - this is my main form (multi-page) and has a vast, vast number of subforms, so I want to keep things as easy as possible for now considering I'm not particularly au fait with coding.

Many thanks...