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