Form Recordset Find method limitations (1 Viewer)

[VilaRestal]

There is a common structure in access database I build where there is continuous form (for example I'll call it frmList) showing a list of records with a button on each record to open the record's detail in anoterh form designed for that purpose (for example I'll call that frmDetail).
Changes made on frmDetail might affect the data shown for that record on frmList. It is essential (and should be straightforward but I'm finding it isn't) to 'refresh' the data behind frmList so it is uptodate.
Obviously, a simple Me.Requery statement will move back to the first record, which is intollerable.

The only way I've found to do this is from the cmdOpenDetail button's click event something like this:

Private Sub cmdOpenDetail_Click()
    Dim iR As Long
    iR = Me!recordID
    DoCmd.OpenForm "frmDetail", , , "recordID = " & iR, , acDialog
    'Sub resumes after form is closed (it was opened as a dialog):
    Me.Requery
    Dim rsMe As Recordset
    Set rsMe = Me.Recordset
    rsMe.Find "recordID = " & iR
    Set rsMe = Nothing
End Sub

Note: This is an an adp (frmList uses a not complex sql server view as its recordsource)

This works fine until about the 4,500th record (on a 5,000 record datasource - I haven't checked the figure [by trial and error: the only way to discover it ] for other recordset sizes) after which it simply doesn't find the record. I.e. doesn't move to it but the record is there and shows in the form if you scroll to it.

I tried using a recordsetclone (where it does Find the record) and getting the AbsolutePosition of the record in that and then using GoToRecord or rsMe.Move to that position but neither of those worked either (were less reliable and the code gets very clunky)

I've also tried to use the Resync method but can't make head nor tail of that: I could get it do nothing, or give errors or seem to hang the screen with redraws but never what it needs to do.

So, my questions are these:

1. Is there a better (easy, reliable) way of achieving the above (requery the record that was current in frmList before the frmDetail version of it was opened to reflect any changes made in frmDetail and keep it current in frmList)?
   
2. Is anyone else aware of that seemingly arbitrary number limitation to the Find method on a form's recordset and, if so, is there a way round it? A setting somewhere perhaps? (Not the forms max records btw, that's set to 100,000!)

If anyone could answer either of those (the first especially) I'd be immensely grateful.

Thanks in advance but I'm not optimistic

 

[VilaRestal]

To be more precise the it fails to work after the 4,700th record!?
(The code works fine on record 4,700 but fails to find it on record 4,701)

This method produces exactly the same results (works fine until the 4,701st record):

Private Sub cmdOpen_Click()
    Dim iR As Long
    iR = Me!recordID
    DoCmd.OpenForm "frmDetail", , , "recordID = " & iR, , acDialog
    Me.Requery
    Dim rsMe, rsMeClone As Recordset
    Set rsMe = Me.Recordset
    Set rsMeClone = Me.RecordsetClone
    rsMeClone.Find "recordID = " & iR
    rsMe.Bookmark = rsMeClone.Bookmark
    Set rsMe = Nothing
    Set rsMeClone = Nothing
End Sub

It seems the Find and Bookmark methods of a form's recordset suffer from the same limitation.

 

[spikepl]

A wild stab in the dark: try using .MoveLast before the search

 

[VilaRestal]

Thanks, but yes I have tried that.
And
DoCmd.GoToRecord acDataForm, Me.Name, acLast
too

It has no effect on the problem (but does force the scrollbar to update so I've left it in).

 

[Galaxiom]

Try using the FindFirst Method.

 

[gemma-the-husky]

try me.refresh, which should refresh the current record, without querying the entire recordset.

just thinking about it - it may be that the recordset cursor is not being moved each time you reopen it. so the find, is searching from the current position. Maybe there is something about the order orf the recordset that means the cursor has already gone PAST the record you are now trying to find. Not certain, just a thought.

Try adding rsme.movefirst before the find.

ON FIRTHER REFLECTION, I am also not sure whether having the dim statement INSIDE the loop is eaxactly correct. Does that mean access will initiallise thousands of recordset pointers? In which case maybe there is some memory issue.

Dim rsMe As Recordset 'move this???
Set rsMe = Me.Recordset
rsme.movefirst
rsMe.Find "recordID = " & iR
Set rsMe = Nothing

 

[VilaRestal]

Me.Refresh has pretty much the same effect as Me.Requery: The cursor goes back to the first record after it so you still have to Find the original record

And as for the second point the cursor does go to record 1 after Me.Requery (and Me.Refresh).

And, like I say, using Find after that works fine up to the 4,700th record when it suddenly seems to not be able to Find. (The code examples I gave both work unless the record is beyond the 4,700th. It happens in a split second - there's no indication lots of resources are being used to do it on the 4,700th record or for that matter on the 4,701st)

And yes I've tried various combinations of movefirst, movelast before the Find

Lastly there is no loop. It's just a single pass through the sub

Thanks for thinking about it though.

I'm starting to think it's just another one of Access's bizarre, irritating, arbitrary limitations.

I suppose Microsoft would say 4,700 records is more than enough to be displaying in a list but I would disagree. It's not very many. 470,000 is too many. 47,000 is probably too many but not 4,700.

I'm not totally happy with the method anyway (even for recordsets with 100 records). I'd much rather just requery the individual record than the whole recordset but I can't find a way to do that.