Access 2003 FindNext - inconsistent and false results

[Robert.Finch]

I have an access 2003 database split front & back end. And have an issue with the findnext macro and vb function providing inconsistent results.

I have a form that show 650k records. I have F8 autokey bring-up a (dialog) form with a text box where uses enter the first few characters of a search string; on exit from the text box the search form is hidden and the docmd.findrecord line searches for the string on the hidden form.

I have F6 assigned as an autokey that calls the macro function or the Vb Function FindNext, I have tried both options.

In my test I am searching for a string starting abcd, I know there are 10 records matching that criteria.

The fist record is always found and 9 further records are usually found.

If I run the query with same search string again I might get 10 matching records, I might get as few as 3. Each time I run the search I get a different number of returned matches.

This is driving me nuts, any suggestions please.

Code below.


frmParts is the main form I want to search
frmFindinParts is the small search form that gets hidden.


Function FindPartNumbersStart()
On Error GoTo FindPartNumbersStart_Err

DoCmd.OpenForm "frmParts", acNormal, "", "", , acNormal
DoCmd.GoToControl "Part Number"
DoCmd.OpenForm "frmFindinParts", acNormal, "", "", , acDialog

‘The user types a string in FindThis, then on exit frmFindinParts is hidden

DoCmd.FindRecord Forms!frmFindinParts!FindThis, acStart, False, , False, acCurrent, True


DoCmd.RepaintObject , ""

FindPartNumbersStart_Exit:
Exit Function
FindPartNumbersStart_Err:
MsgBox Error$
Resume FindPartNumbersStart_Exit
End Function