?
Solved

Field cannot be updated

Posted on 2004-10-07
18
Medium Priority
?
1,226 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:Faurot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
  • +4
18 Comments
 
LVL 8

Expert Comment

by:arcross
ID: 12248723
Does the query contains the clause 'DISTINCT'?

Cuase if it does, the recordset is not updatable
Álvaro
0
 
LVL 12

Expert Comment

by:alandc
ID: 12248807
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
 

Author Comment

by:Faurot
ID: 12248865
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12249024
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
 

Author Comment

by:Faurot
ID: 12249110
Shane,

No, the form's BeforeUpdate is not used.

Thanks, Lyle
0
 
LVL 8

Expert Comment

by:JonoBB
ID: 12249179
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
 

Author Comment

by:Faurot
ID: 12249310
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
 
LVL 8

Expert Comment

by:JonoBB
ID: 12249332
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
 
LVL 8

Expert Comment

by:JonoBB
ID: 12249355
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
 
LVL 12

Expert Comment

by:alandc
ID: 12249633
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
 
LVL 8

Expert Comment

by:arcross
ID: 12249908
Is that field the result of an expression?

Álvaro
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 12249994
Please post the SQL you are using for the RecordSource on your form.  
0
 
LVL 11

Expert Comment

by:Jokra_the_Barbarian
ID: 12250088
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
 

Author Comment

by:Faurot
ID: 12250937
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
 
LVL 11

Expert Comment

by:Jokra_the_Barbarian
ID: 12251301
Which field (name) are you attempting update?
0
 

Author Comment

by:Faurot
ID: 12251436
Jokra,

Attempting to update Last Name.  

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

Thanks, Lyle
0
 
LVL 11

Accepted Solution

by:
Jokra_the_Barbarian earned 500 total points
ID: 12251703
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
 

Author Comment

by:Faurot
ID: 12252112
Jokra,

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

This was the piece I was missing.

Thanks.

Lyle
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question