Field is based on an expression and can't be edited

[HJ]

Hi all,

I am building an Microsoft Access 2000 Project with SQL Server 2000. A
stored procedure displays all order lines for a given location. When I run
this stored procedure from the Access database window (and enter a
location), all order lines are returned and the data can be edited.

When I use the resulting record set from the same procedure in a continuous
form, I get the message: Field 'orlDescription' is based on an expression
and can't be edited.

I have set the Unique Table property of the form to 'tblOrderline' and
'dbo.tblOrderline', both with no result.

I have noticed that, when I use a stored procedure that selects data from
one or more tables without using an EXECUTE statement (that is using a
SELECT statement directly without any input parameters), the form is
updateable.

Where do I have to look for a solution?

Thanks in advance,

HJ
The Netherlands


ALTER PROCEDURE uspPlanning
@Location VarChar(50)

AS
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF

DECLARE @sql nVarChar(1000)

SET @sql = N'SELECT dbo.tblOrderline.* FROM dbo.tblOrderline
WHERE (ordID IN
(SELECT ordID FROM tblOrder WHERE ordLocation = ''' + @Location
+ ''')) ORDER BY orlProductionDate'

EXECUTE sp_executesql @sql
RETURN

 

[HJ]

I have not found a solution yet, but I did find a workaround. In VBA
(Form_Open) I now set the form's recordsource to the stored procedure name
and the form's Input Parameters property to @Location = 'Somewhere'. A
requery is not even necessary.

Now the form is updateable.

I am still unaware of a true solution to my initial problem (see post
before).

HJ

 

[Sophie Guo [MSFT]]

Hello HJ,

I have created two tables and I am unable to reproduce the issue on my side:

Table tblOrder:

Column ordID int
Column ordLocation varchar(50)


Table tblOrderline

Column ordID int
Column orlProductionDate datetime


To troubleshoot the issue, please send me a sample database (both SQL
server and Access database file) and post here the detailed steps to
reproduce the issue.

I will check it and let you know the results. I look forward to hearing
from you.


Sophie Guo
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[HJ]

Thank you for the offer, but momentarily I cannot spare the time to post
these data and details. And since it is working with the workaround, it is
no longer urgent for us.

HJ