"This Recordset is not updateable" after sort or filter

[Martin J.]

Hello,

I've got a database with several linked tables (Foreign-Keys with
Autonnumber-fields). As recommended I have an ID-Field in every table.
For editing directly in the database (-tables) I use the look-up
function for getting a meaningful string instead of ID-Numbers of the
foreign-keys in this table. I can edit/or add new data via drop-down
menus in such tables. But after pressing the sort or filter button for
a column which uses the look-up functionality of access I can't edit
this table any more. I have to close the table and open it again,
without saving the changes. If I did save the changes I have to remove
manually the filter or sort criteria. Can anybody tell me what's the
reason for this behaviour?

 

[John Vinson]

Hello,

I've got a database with several linked tables (Foreign-Keys with
Autonnumber-fields). As recommended I have an ID-Field in every table.
For editing directly in the database (-tables) I use the look-up
function for getting a meaningful string instead of ID-Numbers of the
foreign-keys in this table. I can edit/or add new data via drop-down
menus in such tables. But after pressing the sort or filter button for
a column which uses the look-up functionality of access I can't edit
this table any more. I have to close the table and open it again,
without saving the changes. If I did save the changes I have to remove
manually the filter or sort criteria. Can anybody tell me what's the
reason for this behaviour?

Well... for one thing, Table datasheets aren't really designed or
appropriate for entering data; and Lookup fields are full of problems.
See http://www.mvps.org/access/lookupfields.htm for a critique. In
particular, if you Filter by a lookup field, you're going to be
misled; the table contains a numeric ID, and if you filter for records
containing text you won't get any!

Consider using a Form (with Combo Boxes on it for lookups) instead.
This form can do everything that your table can do, with more control.

John W. Vinson[MVP]

 

[Martin J.]

Thank you very much for your hints. After seeing that lookupfields are
a problem in general I tryed to use forms instead to enter, delete or
edit data.
Now I can build tables where I link all ID-Fields to a new query. These
foreign IDs show their related name, so I can sort/filter these columns
and I have all fields of the original table in the query too. If I want
to enter new data in the table I use the fields from the originial
data. If I want to sort or filter I use the related columns of the
foreign tables. So that works great.

thx again

Martin Jung