Access 2003 FindNext - inconsistent and false results

[TheEnglishman]

Access 2003 FindNext - inconsistent and false results
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
Reply With Quote

 

[pcast01]

I recreated your issue with one of my tables and was able to get consistent results.

I created a function on the search dialog and used it on a number field and a string field. I would do the first search with a button then I put the find next on the toolbar and would click on it to scroll thru all the found records.

You might want to consider opening your own recordset in ADO and running your own Find code...



CHAOS, PANIC, & DISORDER - my work here is done.

 

[TheEnglishman]

Hi pcast01

I have no idea how to open a recordset in ADO but did try adding a findnext button to the form.

The findnext button seems to work although I will trash it a bit more to verify that. I do not know why a findnext button would work any differently to a module invoked by an autokey but it seems to.

Thanks