Dealing with the “Field Cannot be Updated” Error

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

Access Archon Column #234

By Helen Feddema

Access versions: 2007-2013

Level:  Intermediate


Sometimes, when working on an Access subform, you will get the “Field Cannot be Updated” error (no error number), generally when typing the first character into a new record:

The Help topic for this error is of little help, attributing the problem to a Default Value setting for a control, so this article discusses the most likely cause of this error and how to fix it.

The Problem

The sample database for this article opens to version 1 of the Projects form, demonstrating this error.  The form is a main form with a subform.  There is a many-to-many relationship between Projects and Staff, as shown in the Relationships diagram (tblProjectStaff is the junction table):


The form is bound to a query based on tblProjects, and the subform is bound to a query that includes fields from both tblProjectStaff and tblStaff:

The subform is linked to the main form by the ProjectID field.  You can edit data in a subform record with no problems, but if you try to add a new Staff record, as soon as you type a character into a control, you will get the error.  After clicking OK on the dialog, the character you typed is still there, and you can continue typing, and after entering a name in the Technician control, the StaffID is automatically created – but notice that the ProjectID is blank (this control would normally be invisible; I made it visible to demonstrate the problem).  That means that the new Staff record is not linked to the current Project record; no record has been created in tblProjectStaff.

If you close the form and reopen it, you won’t see this record in the subform for Project 1.

The Solution

The solution is to create new records in both the Staff table and the linking table in code, as opposed to just going to a new record by clicking the New Record button in the navigation bar or using the GoToRecord command in code.  On version 2 of the form, Allow Additions is set to No for the subform, and there is an Add New Technician button on the main form.  Because additions are not allowed, if there are no technicians selected for a project, the subform is blank.  On clicking the Add New Technician button, the code listed below runs:

VBA Code

Private Sub cmdAddNewTechnician_Click()


On Error GoTo ErrorHandler


Dim lngProjectID As Long

Dim lngStaffID As Long

Dim rstStaff As DAO.Recordset

Dim rstProjectStaff As DAO.Recordset


‘Add a new contact record and link it to this job

Set rstStaff = CurrentDb.OpenRecordset(“tblStaff”)

Set rstProjectStaff = CurrentDb.OpenRecordset(“tblProjectStaff”)


lngProjectID = Nz(Me![ProjectID])


‘Add a new Staff record and get StaffID

With rstStaff


lngStaffID = ![StaffID]



End With


‘Add a new linking record with ProjectID and StaffID

With rstProjectStaff


![ProjectID] = lngProjectID

![StaffID] = lngStaffID



End With


‘Subform is sorted Descending on ProjectStaffID, so it

‘goes to the most recently added record when requeried

With Me![subTechnicians]

.Enabled = True


End With



Exit Sub



MsgBox “Error No: ” & Err.Number _

& ” in ” & Me.ActiveControl.Name & ” procedure; ” _

& “Description: ” & Err.Description

Resume ErrorHandlerExit


End Sub

On completion of the code, a new record appears on the subform, with both StaffID and ProjectID filled in, ready to enter the remaining data for the new technician:


The code in the sample database does not need any special references.

Supporting Files

The zip file containing this article, in Word 97-2003 format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site, as, which is the last entry in the table of Access Archon columns for Access Watch.

Field Cannot be Updated Error (AA 234).accdb

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address