CODEWITHSUNDEEP
ms-access-2010
DRVR
DRVR

Reputation: 195

Control can't be edited; it's bound to AutoNumber field [FieldName]

I have a Microsoft Access ADP-type database, linking to a SQL Server 2012 database, which was recently upgraded from Office XP (2002) to Office 2010. The upgrade was mostly successful except for an issue with a Combo Box on a form with an updateable data source - In Access XP/2002, the user could select values from the dropdown list and the value would be updated in the table. However, when the user tries to modify the record using the 'tblL.LMID' Combo Box in Access 2010, an error briefly flashes in the status bar at the bottom of the screen (and the record is not updated):

"Control can't be edited; it's bound to AutoNumber field 'LMID'"

I understand this is normal functionality if the field in question was an Identity column in SQL Server, but that's not the case here. However, the 'tblL.LMID' field does get used as a join in the SQL query behind the scenes.

The data source on the form is as follows:

SELECT dbo.tblLM.OpID, dbo.tblL.*, dbo.tblLM.DR 
FROM dbo.tblL INNER JOIN dbo.tblLM ON dbo.tblL.LMID = dbo.tblLM.LMID 
WHERE (dbo.tblLM.DR = 1) 
ORDER BY dbo.tblL.DS

The tables involved in the query are as follows:

CREATE TABLE [dbo].[tblL](
    [LID] [int] IDENTITY(1,1) NOT NULL,
    [LMID] [int] NOT NULL,
    [DS] [nvarchar](10) NOT NULL)

CREATE TABLE [dbo].[tblLM](
    [LMID] [int] IDENTITY(1,1) NOT NULL,
    [OpID] [int] NULL,
    [DR] [bit] NULL DEFAULT ((1)))

As per the table structure, tblL.LMID is a simple column (not Autonumber/Identity), and we should be able to modify it like we did in the Access XP/2002 version of the application.

I would happily accept any assistance on this issue, much appreciated! :)

Upvotes: 2

Views: 4541

Answers (2)

DRVR
DRVR

Reputation: 195

The problem was with the query itself. To resolve, we had to replace the "dbo.tblL.*" select with specific column names, as well as giving the problematic column an alias:

SELECT dbo.tblLM.OpID, dbo.tblL.LMID as l_MID, dbo.tblLM.DR 
FROM dbo.tblL INNER JOIN dbo.tblLM ON dbo.tblL.LMID = dbo.tblLM.LMID 
WHERE (dbo.tblLM.DR = 1) 
ORDER BY dbo.tblL.DS

We then updated the combo box to use the new alias ("l_MID"), and then it started working correctly.

Upvotes: 2

Cahaba Data
Cahaba Data

Reputation: 622

Put the form into design view; delete the current combobox. Create a new combobox and follow the wizard.

Upvotes: 0

Related Questions