We help IT Professionals succeed at work.

Cannot add new data to an existing Microsoft Access form.

one of our existing forms will no longer let us input new data.   It definitely has not been modified in a long time.   The only difference is we went from Access 2003 to 2010.    
The backend is SQL.

When we try to enter a new name, it displays "the field cannot be updated"
Comment
Watch Question

Anders Ebro (Microsoft MVP)Microsoft Developer
CERTIFIED EXPERT

Commented:
Start by looking at the table directly. Can you add records if you use the table directly instead of the form?

If you can't look at the table in design mode and verify that it has a primary key set.
Daniel PineaultPresident / Owner CARDA Consultants Inc.
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
The backend is SQL.
SQL Server?

Try editing the table directly, does that work?
Check the forms allow edits, allow additions, ...

Could you be impacted by https://www.devhut.net/2020/01/17/access-bug-sql-server-identity-bug-with-access/

I once chanced a case where the user could no longer add new entries and it had to do with the table definition in SQL Server (max records).
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:

Any chance the SQL table is missing PK ?

J.R. SitmanIT Director

Author

Commented:
Thanks for the posts.   I will work on this tomorrow
J.R. SitmanIT Director

Author

Commented:
There are two tables involved.   I can add to each of them directly.
J.R. SitmanIT Director

Author

Commented:
Correction.   There is one field "status" in the table, that is a nvarchar data type that I cannot enter data in.   If I remove that field from the form, the form works.  

So this is the cause, but I still need the solution.
Anders Ebro (Microsoft MVP)Microsoft Developer
CERTIFIED EXPERT

Commented:
So your form is based on a query joining the two tables?
How does your query look?
Usually forms are based on a single table and related information such as looking up status from a second table is done through a combobox.
J.R. SitmanIT Director

Author

Commented:
SELECT CONtblPeople.ContactID, CONtblPeople.LastName, CONtblPeople.Firstname, CONtblPeople.MiddleName, CONtblPeople.Suffix, CONtblPeople.Title, MedicalStaff.MedicalStaffCode, MedicalStaff.Status
FROM CONtblPeople INNER JOIN MedicalStaff ON CONtblPeople.ContactID = MedicalStaff.ContactID;
Daniel PineaultPresident / Owner CARDA Consultants Inc.
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
A form should only be based on a single table.  Then you use subforms (which use other tables) to show the other relevant information.

With your scenario, you can check the Form's Recordset Type and make sure it is set to Dynaset (Inconsistent Updates), but this is very dangerous and not recommended.
J.R. SitmanIT Director

Author

Commented:
Thanks for your posts, however, I still do not have a solution.  

Not to correct you Daniel, but we have many forms linked to more than one table and have never had an issue.
Daniel PineaultPresident / Owner CARDA Consultants Inc.
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
That may be, but it is bad practice and is to be avoided.  Proper design is a form for each table and you use subforms to be able to work with related data coming from different tables.

Did you check your form's Recordset Type?

If you are using table, then the Record Source is based off of a query, can you make entries directly in the query?
IT Director
Commented:
I got some help from another programmer that helped design it.   The form was damaged and had to be re-created.