Can't change record opened by another user 1

[Michael57]

Every time I try to add or change some fields in a record I get the message that the record is opened by another user but there definately is no one else in the database.

 

[Remou]

This can happen if the record is edited twice, say, if the record has edited by code and also manually.

 

[Michael57]

So what do I do?

 

[Remou]

Check the code to see if the record is edited in code, I guess.

 

[Michael57]

Remou there is no code using this record. I cannot even delete it in access. I have to open sql and delete it there.

 

[Remou]

Are you saying that there is no code attached to the form? What happens if you create a test form for the same table?

 

[cmmrfrds]

Is this a Form that is bound to a linked table? If so, what is the back end database?

 

[Michael57]

Yes it is bound to a linked table that is microsoft sql

 

[cmmrfrds]

What are all the different data types that are in the bound record? i.e. varchar, numeric, float, bit, etc.....

What is the primary key or unique index on the table? Is this identified in the ODBC link?

 

[Michael57]

Yes the index is linked and autonumbered. Data types are:
Autonumber,Text,Number,Yes/No,Currency

 

[cmmrfrds]

Thank you, the Yes/No field which is a bit data type in SQL Server is probably causing the problem. Normally, if this is allowed to be NULL on the sql server side it will not map cleanly through ODBC and the ODBC provider will pick this field up as being changed even though it was not. The are a number of ways to fix, one way is to add a timestamp data type to the sql server table. Sql server will send this back as a hidden field on the resultset to Access and ODBC will use this field as the primary key for update purposes and ignore the mapping of the bit data type. Another way is to make sure this field does not allow null and defaults to 0 (No) on the sql server side. You could write an update query to set these to zero in the existing data. There are other possibilities but you might want to explore this as the problem.