Recordset is not updateable

[tpearo]

I have a form based on a query and I would like to be able to edit the data in the underlying table. Everytime I try to change or edit the data I get this message "This Recordset is not updateable." I need to base the form on the query so that the operator can only get to that specific record of the database. Does anyone know how to make the data updateable? Help

 

[albywalt]

Try changing the Recordset Type property of the form to "Dynaset (Inconsistent Updates)"

Jeff

 

[wemeier]

Any query that uses the GROUP BY option or that joins multiple tables that result in the data from the "main" table being replicated in multiple records will be "non-updateable". This is an Access constraint with queries.

If you want to limit the user to a selected record using a query, yet still let him/her update the data, why don't you NOT bind the form to the query (i.e. get rid of the form's RecordSource) and instead populate the fields on the form directly from the selected record on the table using VBA coding.

Then, when the user updates the (now unbound) fields on the form, you can put coding behind the form to reacquire the record, update the table fields from the form and update the table.

 

[mac318]

Shot in the dark: have you tried using the recordset methods - .edit and .update?

With rst
.edit
![FieldName] = something
.update
End With

 

[jacksonmacd]

Here's a technique I've used in similar situations:

Make a copy of the query so you won't damage the original
Run the query -- try adding a record -- confirm that you get the "Can't add record" message.
Design the query -- delete one table.
Run the query -- see if the problem disappears. If not, repeat until you find the offending table.

If you have used outer joins, you can often create an un-updatable query. Once you have found the offending table, see how you can rework the query to avoid outer joins.