Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1232
  • Last Modified:

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
0
Faurot
Asked:
Faurot
  • 6
  • 3
  • 3
  • +4
1 Solution
 
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
Jokra_the_BarbarianCommented:
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
 
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 6
  • 3
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now