Can't navigate records in ADP form when Recordsource is filtered (error 2105)

I have a form in an Access 2003 ADP that is bound to a single table in a SQL 2005 database. The form's OnOpen event filters the form's recordsource. When I try to use the form on a login that has only Select/References rights in the underlying table, it doesn't let me navigate from one record to another, and acts as if the user is trying some kind of illegal update operation:
- Attempting to go to previous/next record triggers error 2105 ("You can't go to the specified record"); however, when I use the connection's Errors collection to get more info, there are no errors in the collection.
- When you close the form, Access displays an error dialog: "The UPDATE permission was denied on the object '(name of table bound to form)', database ... etc." - when in fact no update was attempted.
- This is followed by another Access dialog, "You can't save this record at this time... do you want to close this database object anyway"

If I remove the filtering, the problem goes away! The form behaves as one would expect, allowing the read-only user to navigate and view but not update. It appears that Access interprets a filter as an attempt to "update" the table, making the form unusable. I've tried creating the filter both by setting "me.filter" in the form's code, and by changing "me.recordsource" - the result is exactly the same either way. I need to be able to dynamically filter the form's data - any suggestions would be very appreciated. Thanks!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


Access does some things "behind the scene" you cannot debug using Access.
I would recommend to use the SQL Server Profiler to see what exactly happens in the background between Access and the server. Access often uses some own stored procedures to handle things, especially if you have selected a record source with the dropdown field in the form properties. To avoid this, use an own view or stored procedure and don't select it from the dropdown, enter an own SQL statement like "SELECT x,y,z FROM mytable WHERE..." in the record source or in case of stored procedure "EXEC mysp @myparam=...". On this way Access sends the command directly without any change to the server. Moreover it can help to disable the "fetch default" property and set the recordset to "snapshot" instead of "updateable snapshot".



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you using the ServerFilter property?

Are you 100% certain that you've properly set a Primary Key on the table(s)?

I would agree with Christian regarding the use of a Stored Proc to do this.
CESD123Author Commented:
Thank you both for your contributions. I think the key was the RecordsetType; I tried changing it from "updateable snapshot" to "snaphot" in the form's Load event, and that seems to mostly fix it, along with setting "Fetch Defaults" to No.

In the future I may try making the recordsource a stored procedure (it already was a SQL SELECT statement); hopefully that may simplify some issues around users' rights in the form.
CESD123Author Commented:
Thanks for very clear and useful advice; this is an area not well covered in existing information about Access ADP's.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.