MS Access locks record after data entry in Memo field

0
votes

I have an MS Access 2010 application. When I go to a form and type data into a Memo field I then run the below query in VBA when a button is clicked. The current record is the only one being edited and no other user is in the database.

However, I get the error that the record can't be updated due to a record lock violation. I also sometimes get the error the data has been changed and I should re-edit the record.Below are the text for each error I receive.

Form DV didn't update 0 fields due to a type conversion failure, 0 records due to key violations, 1 record due to lock violations...

The data has been changed. Another user edited this record and saved the changes before you attempted to save your changes. Re-edit the record.

Does anyone know how I can get around this or why it is happening? 

Private Sub Save_Status_Complete_Button_Click()

Str_SQL_Update = "UPDATE [dbo_Tape_Capture_Local_tbl] SET header_general_comments_status = 1 WHERE [Loan Identifier] = '" & Me.Loan_ID_Combo & "';"
DoCmd.RunSQL Str_SQL_Update

End Sub
1
ms-accessvba
Is your form/memo field bound to a table? – MoondogsMaDawg
Hi Christopher, Yes. the memo field is bound to a table. – Conrad Addo
Sorry, to clarify: is it bound to the table you are performing the UPDATE on? If so, that's where the write conflict is coming from. In a bound control, you are changing the record in the bound table directly, even before you click your button. So when the button is clicked, the table is already locked because you're already editting it. – MoondogsMaDawg
Save the form's current record before executing the UPDATE. – HansUp
@HansUp seems like if it's a bound control those two things are the same thing, right? If the record is saved then there's no point in running an update, too. – Brad

1 Answers

1
votes

Add Me.Dirty = False before running your query. that will save the form's current record.