Field cannot be updated

When I am adding a new record to a table (using a form based on a query) I first use the command:

DoCmd.GoToRecord , , acNewRec

Then, when I try to type a value into a field (not a key field) I get the message: "Field cannot be updated".

The underlying table field is a "required" field with validation rule (in the table definition) of "Is Not Null".

If I respond to the message by clicking "OK" the field on the form then accepts the character that triggered the message and lets me enter the field value.

What's up?

Thanks, Lyle
FaurotAsked:
Who is Participating?
 
Jokra_the_BarbarianConnect With a Mentor Commented:
From what I can see, you have a form recordset based on the SQL you provided. My question to you:

When you create a new record, are you inserting a value in Schedule.GuestCategoryID? If there is no value for this foreign key field (Access does not know what the next value should be), then you can't create that record.
0
 
arcrossCommented:
Does the query contains the clause 'DISTINCT'?

Cuase if it does, the recordset is not updatable
Álvaro
0
 
Aland CoonsSystems EngineerCommented:
Are you modifying an existing record or creating a new record?
Since your search was based on a query you could some odd errors while adding a new record even if the resulting values matched the original query..

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
FaurotAuthor Commented:
arcross,

The query does not contain the DISTINCT clause.  Thanks for your response.

alandc,

The record is a new record.  (At least, I think it is.)
I get the record using the command:
    DoCmd.GoToRecord , , acNewRec

Thanks, Lyle
0
 
shanesuebsahakarnCommented:
Do you have anything in the form's BeforeUpdate that might be trying to save the record (such as a Me.Dirty=False or DoCmd.RunCommand acCmdSaveRecord) ?
0
 
FaurotAuthor Commented:
Shane,

No, the form's BeforeUpdate is not used.

Thanks, Lyle
0
 
JonoBBCommented:
I think that whats happening is that there is a field somewhere in the table (call it field1) that requires a value and comes before the field that you are in (Field2).

If you try put a value into Field2 *before* putting a value into Field 1, you get the error message.
0
 
FaurotAuthor Commented:
JonoBB,

I tried moving the offending field before all other required fields in the query.  This didn't work.

I then tried moving the field to the top of the field list (just after the autonumber primary key) in the table definition.  This didn't work either.  

Are you referring to something else in reference to which field comes first?  (Like tab order in the form?)

Thanks, Lyle
0
 
JonoBBCommented:
Nope, tab order in the form will not make any difference

Can I suggest that you post your file (after you have taken out any sensitive information, of course) so that we can all have a look.
0
 
JonoBBCommented:
Another thought - is this table linked to another table in your database (using referential intergrity)  and is there a foreign key in this table?

Sounds like you may be violating some referential intergrity.
0
 
Aland CoonsSystems EngineerCommented:
If you are adding a new record with a form why would it matter if it was initiated from a query unless the record set displayed was from the query. In that case since you are adding a new record it seems like your best bet would be to open a new form for data entry.
0
 
arcrossCommented:
Is that field the result of an expression?

Álvaro
0
 
Steve BinkCommented:
Please post the SQL you are using for the RecordSource on your form.  
0
 
Jokra_the_BarbarianCommented:
PRIMARY KEY

Check to make sure that the table your form is based on has a PRIMARY KEY. This is especially essential if you are linking to SQL Server tables.
0
 
FaurotAuthor Commented:
Here is the SQL:

SELECT Schedule.ReservationID, Schedule.LastName, Schedule.StartDate, Schedule.Nights, [StartDate]+[Nights] AS DepartDate, Schedule.Confirmed, Schedule.Notes, Schedule.GuestCategoryID, GuestCategory.Category, Schedule.FirstName, Schedule.Contact, Schedule.MailAddress, Schedule.ResourceID, Resource.Description, ([FirstName] & " " & [LastName]) AS FullName, Schedule.Billed, Schedule.Adults, Schedule.Children, Schedule.Phone, Schedule.EMail

FROM Resource RIGHT JOIN (Schedule LEFT JOIN GuestCategory ON Schedule.GuestCategoryID = GuestCategory.GuestCategoryID) ON Resource.ResourceID = Schedule.ResourceID;

Other info:
Schedule is the main table with guest reservations.
Primary key: ReservationID.
Foreign key: GuestCategoryID
Foreign key: ResourceID

The tables linked to are:
GuestCategory, Primary key: GuestCategoryID

Resources, Primary key: ResourceID

These two tables are used to ensure valid values are entered in the Schedule table.

I have the relationships set as follows with referential integrity turned on:

Schedule--many to one--GuestCategory
Schedule--many to one--Resource

These two tables are mainly loopup tables for validating fields in schedule but each table has a second field (description) used in reports and as items in Comboboxes on the form.  The Comboboxes have "Limit to list" turned on to prevent entering invalid values in the Schedule table. The recordsource query for one of the Comboboxes does use the "DISTINCT" clause.

Maybe I should not have these relationships set in the relationships screen, but just do the validating in the add/edit form?

Thanks, Lyle
0
 
Jokra_the_BarbarianCommented:
Which field (name) are you attempting update?
0
 
FaurotAuthor Commented:
Jokra,

Attempting to update Last Name.  

Attempting to other fields produce the same result if it is the first field entered.

Thanks, Lyle
0
 
FaurotAuthor Commented:
Jokra,

Inserting the default value into GuestCategoryID field seems to solve the problem.

This was the piece I was missing.

Thanks.

Lyle
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.