Addnew won't work on joined tables

[dkaplan]

I'm trying to add new records to a recordset, based on a query which joins two tables. The recordset is updatable, as I can add new records by typing directly into the add row of the query. But when I try to do it from the form, I receive the error: "Field cannot be updated."

My code is:
Set rs = db.OpenRecordset(strSQL, adOpenDynamic, adLockOptimistic)
With rs
.AddNew
![EFirstName] = Me!EFirstName
......
.update
end with

Thanks.

Dennis

 

[cmmrfrds]

On the data tab in your Form, tell Access which unique table is updatable. Normally SQL doesn't allow update/insert/delete from joined queries, although Access provides some ways to do it. Also, there are other ways to structure your query so that the join can be avoided. Otherwise, use the unique table setting under the data tab.
Also, ADO has an unique table property if you need to join tables for an update under ADO.

 

[dkaplan]

Thanks, Cmmrfrds.

I actually found a way that worked. The problem seemed to be that the linked values (Main ID and foreign key ID) did not initially exist, as I was addressing the add row of the recordset. However, I discovered I could assign those values on the fly. This assignment worked only if I made changes to the main table before making changes to the linked tables. So the code looked like:

Set rs = db.OpenRecordset(strSQL, adOpenDynamic, adLockOptimistic)
With rs
.AddNew
![EFirstName] = Me!EFirstName ' (Main table)
......
![foreign ID no] = [main ID number] '(Had to use aliases for some reason. Main ID is an autonumber)
![no of widgets] = Me.NoWidgets (linked table)
.update
end with


Hope it holds up under fire.

Dennis