Create a query/form that can update a table

microsoft access

I want to know if it's possible to create a form that has read/write access to a very specific (filtered) record set of a table.

To achieve this, I:
1. Create a query which filters the records of a table.
2. Create a form with this query as its Record Source.

However, queries are not updateable and I can't update the original table's contents from the Form. Anyone knows how to get around this?

Here's a related article: http://www.fmsinc.com/Microsoftaccess/query/non-updateable/index.html

Thanks.

Best Answer

When I was heavily into Access development I hit this problem a few times. Most simple queries are updatable, but others are not. You can tell by trying to edit the values in the result set. I would try your query first before assuming you can't do it.

There is one technique I used which gets around this issue quite well if all else fails, but is a bit of a pain to set up and maintain -- What you do is turn the select query you have into an append query, and save the record IDs only into a temp table. You can do this as part of a form open event (from memory - might need to use the me.requery) or better yet as part of code from the menu/button that got you to the form. Then you use a MUCH simpler query as the forms recordsource, just the temp table joined to the original table via the ID.

This should work, but you now also have some other tasks like emptying the temp table each time before/after you use it and it is not really multi-user friendly. If you need multiple users using it you need to add some sort of USERID to the table too. I hope this makes sense!

Related Question