Not really sure about this, but from my experience of other programming languages, it sounds like your overflowing the cache. So access is taking a 'snapshot' of the details, but not actually caching the records for them.
If you cache less records, does the 'wall' change? i.e. Do you always have the same number of accessible records for this table?
Yesterday, I applied the Cache parameter to a number significantly bigger than what I need (but still well within the computer, database and object capabilities) and persisted in seeing the same problem.
This morning, I switched to DAO recordsets for the form, which worked but introduced other challenges/limitations (such as requiring ODBC and PassThroughs).
I switched it back to ADO tried again (so I could test limiting the row count returned by the sp) and discovered it now works, correctly.
Should I rejoice because it works now or worry because I did not resolve why it wasn't working?
Aha! Right after I posted this, I went to do some final clean up and discovered the problem came back. This time, though, I only had about 4 small changes between working and not working so it was a lot easier to discover the cause.
An interesting feature/problem with ADODB recordsets:
When you CLONE a recordset and then apply a filter to the clone, it apparently also applies to the original. However, the form does not quite catch up.
I'm pretty sure that is not the case with DAO recordsets which allow your to filter the clone without affecting the original.
I have a function that uses filters on a clone of the form recordset to provide summary stats. What was happening was the last filter applied to the clone was also applied to the original rs. The form did not refresh and still showed all the records, but did not let me select the records not present in the filtered rs.
So, for the fix, I simply remove the filter from the clone before setting the clone equal to nothing.
Again, I can't speak for access, but in general, when altering the cache size of a running program, the changes will not take effect until either the program or the computer (or at least the OS) is restarted.
The Access cache is using physical memory, and memory has to be reserved on startup, more available memory being added at runtime is not usually possible (although exceptions do exist).
So it's possible that you increased the size, continued editting and getting a headache, went home, came back the next day, the PC and/or Access were restarted, and BAM! more cache memory available, and so no more issues.