Access Archon Column #234

By Helen Feddema

Access versions: 2007-2013

Level:  Intermediate

Introduction

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

.AddNew

Secrets and tips for the Windows 10 May 2019 update

A detailed and independent look at Windows 10, especially for Microsoft Office.

Fully up-to-date with coverage of the May 2019 major update of Windows 10.

This 918 page book shows you important features and details for all serious Windows 10 users.

lngStaffID = ![StaffID]

.Update

.Close

End With

‘Add a new linking record with ProjectID and StaffID

With rstProjectStaff

.AddNew

![ProjectID] = lngProjectID

![StaffID] = lngStaffID

.Update

.Close

End With

‘Subform is sorted Descending on ProjectStaffID, so it

‘goes to the most recently added record when requeried

With Me![subTechnicians]

.Enabled = True

.Requery

End With

ErrorHandlerExit:

Exit Sub

ErrorHandler:

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:

References

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 accarch_.zip, which is the last entry in the table of Access Archon columns for Access Watch.

Field Cannot be Updated Error (AA 234).accdb