I have three tables, Owners, Parcels, and Ownership. Owner has and Owner_ID 
and several fields of owner information. Parcel has a Parcel_ID and several 
fields of information. Ownership is a junction table that matches these two 
IDs.

I have created a form that displays the owner data in the main form and then 
all of the parcels for that owner in the subform. I would like to be able to 
add new parcels in the subform. When I try to do this, I get an error 
message, "Field cannot be updated."

I checked the control properties, and the subform and its controls are not 
locked.

Can anyone suggest what I can do to be able to add records to the subform?

Thank you. 

 

On Tue, 8 Jan 2008 13:23:01 -0800, danftz <danftz@discussions.microsoft.com>
wrote:

>I have three tables, Owners, Parcels, and Ownership. Owner has and Owner_ID 
>and several fields of owner information. Parcel has a Parcel_ID and several 
>fields of information. Ownership is a junction table that matches these two 
>IDs.
>
>I have created a form that displays the owner data in the main form and then 
>all of the parcels for that owner in the subform. I would like to be able to 
>add new parcels in the subform. When I try to do this, I get an error 
>message, "Field cannot be updated."
>
>I checked the control properties, and the subform and its controls are not 
>locked.
>
>Can anyone suggest what I can do to be able to add records to the subform?
>
>Thank you. 
>
> 

I presume that the Subform is based on the Ownership table?

You've got a couple of possibilities. One would be to have a Combo Box on the
subform, with the Parcels table as its RowSource; set its Limit to List
property to Yes, and use VBA code in its NotInList event to add a new parcel
as necessary. See the sample code at http://www.mvps.org/access (search for
NotInList) for an example.

Or you may be able to base the subform, not on the Ownership table, but on a
Query joining Ownership to Parcel:

SELECT Ownership.Owner_ID, Ownership.Parcel_ID, Parcels.Parcel_ID,
Parcels.ParcelName, <other parcels fields as needed>
FROM Parcels LEFT JOIN Ownership
ON Parcels.Parcel_ID = Ownership.Parcels_ID;

Assuming that Parcels.Parcel_ID is an Autonumber field, and that you include
both tables' instances of Parcel_ID, Access' builtin "row fixup" feature will
let you enter a new parcel in this query, and it will automatically fill in
the record in both tables.

             John W. Vinson [MVP]