Could not update; currently locked

[HomeGrowth]

I have a update button to update a record in the subform. the codes are as follow:

Dim cnn As ADODB.Connection
Dim sSQL As String
Set cnn = CurrentProject.Connection
Dim strDocNumber As String

strDocNumber = [Forms]![frm(6-1)SOPEntryForm]![txtDocNumber]

sSQL = "UPDATE [tbl(6)AllDocuments] SET [tbl(6)AllDocuments].Status = ""S"" " & _
"WHERE [tbl(6)AllDocuments].Status Like ""C"" AND " & _
"[tbl(6)AllDocuments].[Doc Number] " & _
"Like """ & strDocNumber & """;"

cnn.Execute sSQL 'Update the record

I got error message: 'Could not update; currently update'

Can't figure out why, any body can shed some lights!!

 

[PHV]

Is by chance [tbl(6)AllDocuments] the underlaying table of the form ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[HomeGrowth]

Yes, both the main form and the subform are from [tbl(6)AllDocuments]. I used Access 2000, record should only be locked on the one currently deing on edited, as I set the the lock as 'edited record' only on both forms. Even I tried 'no lock' still give me the same error.

 

[PHV]

You may consider playing with a clone of the form's recordset.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[HomeGrowth]

Is a clone recordset updatable to reflect the change in the main table? I used to use it for lookup record only. Just a question to make sure, but I'll try. Thanks!

 

[HomeGrowth]

Just for the record, I got my hint from microsoft's website

http://e18.email.excite.com/msg_rea...9e0133&ArdSI=140d0d8ed71e487dfa77d135989e0133

Basically, I can't not update another record in the subform while updating the record on the main form from the same recordset. My solution was to move this update SQL statement to the AfterUpdate event.

Thank you, All!