CESD123
asked on
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!
- 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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
Thanks for very clear and useful advice; this is an area not well covered in existing information about Access ADP's.
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.