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....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 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