Can't update record, currently locked

[clancy76]

Hi there - I'm working with a legacy mdb that has a tab on the form. On one of of the tabs, I'm using a SQL statement that will update a field on another one of the tabs (If this particular record is updated, the field on the other tab will also get updated). When I execute the statement, I get a message stating "Could not update, currently locked". Now obviously since I'm the only user, I'm assuming the reason I'm getting this message is because the field I'm trying to update is currently open in another tab(If I close the form and run the query it updates fine). Is there a property I can set to prevent this? I've tried setting the forms Record Locks property to "No Locks", had no effect. Any help would be appreciated, thanks.

[ItsMe]

As long as they are within the same form, it should not matter how many controls are bound to a single field, at least not on an old MDB and we are talking about basic style controls.

Perhaps it is how you are executing your SQL. Can you post the code that executes the SQL?

[clancy76]

This is in the text box's AfterUpdate event (query works fine when the form is closed)

Dim strSQL as string

strSQL = "UPDATE tblSite_Update INNER JOIN (tblSite_COS INNER JOIN tblSite_CE ON tblSite_COS.COS_ID = " _
& "tblSite_CE.COS_ID) ON tblSite_Update.Site_ID = tblSite_COS.Site_ID SET tblSite_Update.Max_Payments = " _
& "[tblSite_CE].[Unit_Cost] WHERE tblSite_COS.Site_ID =" & Form_sfrmCOS.Site_ID

db.execute strSQL

[ItsMe]

Hmmm, OK. At first blush, it seems like possible duplication of data. That aside, try saving and committing your form before executing your SQL.

[clancy76]

ItsMe - I threw in the line "DoCmd.RunCommand acCmdSaveRecord" in the AfterUpdate event before the SQL is executed, is this what you meant? If so, you are a genius, it works now.

Edit: Also, Thank you!!

[ItsMe]

Cool.......