Solved

Field cannot be updated

Posted on 2004-10-07
18
1,175 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
  • 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 50

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 125 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now