Continuous Form not allowing edits

Question

I need a continuous form to allow edits on one column, although I will take any at this point.

The form level settings are set as follows: AllowEdits: Yes; AllowDeletes: Yes; AllowAdditions: Yes;

The form controls settings: Enabled: Yes; Locked: No;

The Record Source is a query with these settings: Record Locks: No Locks; Recordset Type: Dynaset;

The query pulls from a MS SQL server table that is set up in MS Access as a linked table, and I have admin rights on the SQL server.

I have attempted remaking the a simplified version of the form, and it still does not allow edits. I have other continuous forms sourced from different tables that can be edited, but whenever I try on the form in question, nothing happens. It seems like it would be at the table or query level. Is there a setting I am missing?

Edit: The query is pulling from two tables -

dbo_tbl_controls (PK StatOrig - Text) and

dbo_tbl_statspkgdata (PK Stat - Text; VType - Text; Yr - Number and others)

The relationship is one to many on StatOrig = Stat. I need the controls table for the sort order.

Do you have a unique index? You may have to create one on the link if it is to a view. It can be easier to link the tables and create the query in MS Access. You will still need indexes. — Fionnuala
Sounds like the query is not updateable. Does the query use only the single SQL Server table? Can you post the SQL of the query? — Andre
@Fionnuala The SQL table has an Identity column "ID" and the query pulls results if you simply execute it. — ProgrammingAccountant
@Andre451 I actually have an old version of the database in which the query is identical, and the form is editable. In that version, there is a switchboard. I removed the switchboard in favor of blank form that I added several buttons to. Could that be the issue? — ProgrammingAccountant

Fionnuala Fionnuala · Accepted Answer · 2015-09-16T16:00:44

With an SQL Server back-end it is essential that you have relevant unique indexes on any table or view that you wish to edit. Check all indexes to make sure they are appropriate.

You can create an index for a view in VBA like so:

db.Execute "CREATE UNIQUE INDEX uiId ON someview (Id) WITH PRIMARY"

Continuous Form not allowing edits

1 Answers