Help with my search form

[NeilT123]

I have created a "search" facility on one of my forms. The main form is called frmIfTFertAppln and has an unbound text box called Search which has the following code

Private Sub Search_Change()
Dim vSearchString As String
 vSearchString = Search.Text
 Search2.Value = vSearchString
Me.frmIfFertAppln.Requery
End Sub

Search2 is also unbound.

The subform is called frmIfFertAppln and is a single form and is populated using the following code.

SELECT tblTEMPIfFertAppln.FieldCode, tblTEMPIfFertAppln.AccountName, tblTEMPIfFertAppln.FarmAccountNumber, tblTEMPIfFertAppln.FieldName, tblTEMPIfFertAppln.FieldComments, tblTEMPIfFertAppln.[SubFarm/FieldGroup], tblTEMPIfFertAppln.SoilTexture, tblTEMPIfFertAppln.NVZStatement, tblTEMPIfFertAppln.NoLongerCropped, tblTEMPIfFertAppln.DateOfAnalysis, tblTEMPIfFertAppln.PIndex, tblTEMPIfFertAppln.KIndex, tblTEMPIfFertAppln.MgIndex, tblTEMPIfFertAppln.pH, RemovePunc([tblTEMPIfFertAppln].[FieldName]) AS Expr1
FROM tblTEMPIfFertAppln
WHERE (((RemovePunc([tblTEMPIfFertAppln].[FieldName])) Like [Forms]![frmIfTFertAppln]![Search2] & "*"))
ORDER BY tblTEMPIfFertAppln.FieldName;

I want the search to work in two ways. I work in agriculture and so on this form I search by field name (as in farmers field not access field) so if the field name is Bratch I type that in, the record for Bratch comes up and this works how I want it to.

I also want to be able to search for "fields beginning with.." . The subform is a single form so if I type in "b" into the search then I get one of the fields beginning with B and I move around them using the next or previous buttons but I want the first record to show to be the first in the alphabet so Back field, then Bee Field then Bratch. At the moment I am getting all the records and if I navigate through the records they are in alphabetical order but the record that the form opens on might be "Bee Field" or "Bratch" and I want it to be "Back Field".

I have set the properties of the temporary table tblTEMPIfFertAppln to order by FieldName.

I hope that makes sense and I wonder if anyone has any ideas as to how I might be able to achieve this.

Thanks in advance

 

[NeilT123]

I have a bit more information as to what is happening with the search facility on my form but I don't know how to stop it so any help would be appreciated.

The first time I use the form and type in "b" to select all the fields beginning with B the form opens up on the correct i.e the 1st record.

If I then use the navigate buttons to move to say the 4th record beginning with "b" and then use the search button to search for all records starting with "p" the form opens on the 4th record beginning with P. If I use the navigate buttons to move to the 2nd record beginning with P and use the search button for "c" the 2nd record beginning with C opens.

If I close the form and reopen it and search then the 1st matching record is found and if I search again then the 1st record is found until I use the navigate buttons and then the above happens.

The code behind the navigate button is

Private Sub Next_Record_Selector_Click()
On Error GoTo Err_Next_Record_Selector_Click


    DoCmd.GoToRecord , , acNext

Exit_Next_Record_Selector_Click:
    Exit Sub

Err_Next_Record_Selector_Click:
    MsgBox Err.Description
    Resume Exit_Next_Record_Selector_Click
    
End Sub

I think I need to clear whatever is being saved before each new search, can anyone suggest how I might do this?

Thank you

Neil