Re: Field locked on one SQL record

Ater a lot of digging I was able to find what was causing the problem, but I
still do not understand "Why???"

This is the SQL Table setup:

InspectionNum -- int
OrderNumber -- nvarchar
VendorID -- nvarchar
Part -- -- nvarchar
Rev -- -- nvarchar
ReceivedQty -- int
ReceivedDate -- smalldatetime
InspectionLevel -- nvarchar
AQL -- -- real
Instructions -- ntext
QtytoInspect -- int
QtytoAccept -- int
QtytoReject -- int
InspectedQty -- int
Inspector -- nvarchar
LastChangedBy -- nvarchar
Rejected -- int
RejectDate -- smalldatetime
RTV -- -- int
Rework -- -- int
UAI -- -- int
Scrap -- -- int
ECO -- -- int
Accepted -- int
ConfirmAccepted -- bit
SCAR -- -- int
Note -- -- ntext

The form is a simple entry form that gives the user an "Inspection Number"
and they fill in information like the order the part came in on, the date,
how they inspected it and some notes and etc. There is a form that has this
table as the datasource and all the fields have their datasource bound to
each of these fields. Some of the fileds have drop boxes for selections and
based on things like the Part or the Vendor, other boxes like the
OrderNumber and ACL get popluated or defaults get set.

On the form there is a drop box with a selection list for the ACL field
(datatype = real). The choices are about 20 numbers from .065 to 100. When
a user selects the "Part", the form looks up the proper ACL value and fills
in the field. The problem occurs when the user deceides for this
Inspection they need to use a different ACL number and they select one that
is not the default, but it is on the list. Once they change that value you
can only write once to the Note field (ntext). You cannot go back and edit
the Note field.

On the SQL server and in the Access project there are no relationships set
up nor are there any other criteria that the Note field depends on. I can
edit this field on the SQL server using the management tools however I
cannot even open the table in Access and change the Note once a note has
been entered. I can however, delete the note (make it blank, not null) but
cannot change it to any other text.

I don't know why this solved the problem but I sure would love to understand
why. When I changed the ACL field's datatype from "real" to "numeric" with
a scale of 3 to accomidate 3 digits after the decimal this problem went
away.




"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:unhWifzkFHA.576@xxxxxxxxxxxxxxxxxxxxxxx
> Use the profiler to see what's happening on the server. Also, make sure
> that you have used the refresh command (or F5) from the File menu for both
> the Tables and the Queries windows.
>
> If you have use the syntaxe « SELECT * FROM ... » then try by writing
> explicitely all the fields. It will also be a good idea to put all the
> NTEXT fields at the end of the list in the Select statement.
>
> Make sure that the Unique table property is set correctly and if
> necessary, create a resync command. You can also try with a dummy resync
> command (ie. any stored procedure with the wrong number of arguments; if
> the primary key has one field then use any SP with two or more parameters
> without a default value).
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
>
> "Jordan" <nojunk_allowed@xxxxxxxxxx> wrote in message
> news:O%23z2oJvkFHA.3936@xxxxxxxxxxxxxxxxxxxxxxx
>>I have a table on SQL 2000 that I am accessing with an Access 2003 form.
>>This is a basic data entry form. On 4 or 5 out of the thousands of
>>records there is a notes field that will not allow me to change or edit
>>data in the field (ntext on SQL). I can change all the other data in that
>>record and every other record besides these I can change, delete, and add
>>data to this field. The only thing I can do in this field is delete the
>>data.
>>
>> If I use SQL Manager on the server or remotely and open the table I can
>> add, delete, and modify data in that field, but Access always fails. No
>> computer that has Access on it can change the data in the field. they
>> can only delete the text in it an not be able to replace it.
>>
>> The error is usually a Write Conflict error and asks to drop changes or
>> copy to clipboard.
>>
>
>


.

Relevant Pages

  • Re: Field locked on one SQL record
    ... > InspectionNum -- int ... > The form is a simple entry form that gives the user an "Inspection Number" ... > value you can only write once to the Note field. ... > On the SQL server and in the Access project there are no relationships set ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Views vs Stored Procedures, whats the difference?
    ... I'm just trying to differentiate between two fundamentally different SQL objects. ... CREATE TABLE P(pk INT NOT NULL PRIMARY KEY, ... SELECT MAXFROM nestedview ...
    (comp.databases.ms-sqlserver)
  • Re: looping
    ... Oracle - this is a problem requiring an answer that works on SQL Server. ... id), id, salesperson_id, tran_date, clear_date, amount, ... from Trans t2 ... salesperson_id int not null, ...
    (microsoft.public.sqlserver.programming)
  • Re: Transaction Abort in a trigger SQL 2008, writing to event log
    ... catching errors in SQL. ... CREATE TABLE logtable (id int IDENTITY, ... DECLARE @errmsg nvarchar, ... EXEC error_handler_sp @@procid ...
    (microsoft.public.sqlserver.programming)
  • Re: Views vs Stored Procedures, whats the difference?
    ... In the proc I would write the MAX as you've done. ... @optional_parm1 int = NULL, ... While the above does contain logic, it will give you the best plan in MS SQL ... CREATE TABLE P(pk INT NOT NULL PRIMARY KEY, ...
    (comp.databases.ms-sqlserver)
Loading