Lock Error when trying to edit record

[sross81]

I have a form that is bound to a query that is bound to a table that I recently added a few new fields to. I suddenly started to get a VBA error code 7787 when a user tried to edit a record. When I try to go directly to the table I also get the error.

It turns out that only the records that have a value in the new field are giving this error. The new fields that I added are nvarchar(50). They are set to allow nulls.

In the past if I have added new fields and they were bit and I didn't give a default value I would get this error when a user tried to edit a record. I have never seen it with text.

I have tried to relink the table, as well as deleting and completely relinking. It still does not like any of the records that have values in these new fields. If there is no value in the new fields then they can edit no problem.

I will continue searching, but please share if you have ever seen this and how it was resolved.

 

[JHB]

... When I try to go directly to the table I also get the error.

In MS-Access or on the server?
What if you add a new record?

 

[sross81]

It is MS Access linked to SQL Server.

I realized last night that although I gave the bit data types a default of 0 so that nothing would end up with Null that there was some in there with a null. I ran an update to change everything null to 0 which I had done in the past and then the locking problem went away. As soon as I ran the overall update again they all were updated back to Null. So basically there is something just wrong with my code that I need to resolve...

but the problem here was just that the Bit fields had nulls instead of 1 or 0 and access does not let you edit records that are linked to a SQL table if this is the case.

 

[sathishkumarj]

It is MS Access linked to SQL Server.

I realized last night that although I gave the bit data types a default of 0 so that nothing would end up with Null that there was some in there with a null. I ran an update to change everything null to 0 which I had done in the past and then the locking problem went away. As soon as I ran the overall update again they all were updated back to Null. So basically there is something just wrong with my code that I need to resolve...

but the problem here was just that the Bit fields had nulls instead of 1 or 0 and access does not let you edit records that are linked to a SQL table if this is the case.

Thank you !!! this solution resolved my issue, today.