Unable to add a new record in a subform datasheet

[danftz]

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.

 

[John W. Vinson]

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]